Fix my SQL "WHERE" Statement!!

[Rachel Weeden]

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

[Philip Mette]

Ra**********@hotmail.com (Rachel Weeden) wrote in message news:<f5**************************@posting.google. com>...

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.

[Philip Mette]

Ra**********@hotmail.com (Rachel Weeden) wrote in message news:<f5**************************@posting.google. com>...

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.

[Gert-Jan Strik]

CInt is not supported in SQL-Server. You can use CAST or CONVERT
instead.

IIf it not supported in SQL-Server. You can use the CASE expression,
although it works slightly different, so you will need to rewrite that
part.

Have a look at SQL-Server Books Online for more information and
examples.

Hope this helps,
Gert-Jan
Rachel Weeden wrote:

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

--
(Please reply only to the newsgroup)

[Craig Kelly]

"Rachel Weeden" wrote:

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

Rachel,

[Note: I typed some of the T-SQL code in my newsreader, so formatting and
syntax may be a little goofy, but it should get you started in the right
direction.]

The square brackets are OK in T-SQL. The problem you're having is that your
WHERE clause is using VBA functions. While this is a cool feature in the
JET database engine, you can't use it in T-SQL (or any other DB environment
that I'm aware of). As others have mentioned:

- Use CAST or CONVERT instead of CInt (or any of the VB casting functions
e.g. CStr, CDbl, etc)

- Use CASE instead of IIf

Also,

- In VB, IsNull is a boolean function that returns true if the single
argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
parameters; if the first argument is NULL it returns the second else it
returns the first. For example:

ISNULL(NULL, 1) returns 1
....and....
ISNULL(2, 1) returns 2

A rough translation of your code would go something like (watch out for word
wrap and funny formatting)...

AND (
CASE
WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
THEN 9999

WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
THEN 9999

ELSE
CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
1800 AND 1899
THEN 1
ELSE 0
END
END
)

However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
representation or it is numeric and is inclusively in the range 1800-1899".
If I'm correct, you could use something like this (tested in Query Analyzer
with SQL Server 2000)...

DECLARE @tab TABLE (
d varchar(32),
ccc varchar(20)
)

INSERT @tab VALUES ('Num outside range', '1750')
INSERT @tab VALUES ('Num in range', '1800')
INSERT @tab VALUES ('Not a num', 'aaa')
INSERT @tab VALUES ('NULL', NULL)
INSERT @tab VALUES ('Empty string', '')

SELECT *
FROM @tab
WHERE CASE WHEN ISNUMERIC(ccc) = 1
THEN
CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
THEN 1
ELSE 0
END
ELSE 1
END = 1

This returns everything in the test table except the 'Num outside range'
row.

Craig

[Rachel Weeden]

Thanks for all the input, Craig - I have taken some time to look over
your code, and I understand the basics about replacing some of my VB
functions with T-SQL ones. Problem is, I am very inexperienced with
SQL (this page is my first project, really!), so the details are a
little confusing.

For example, I've never heard of T-SQL before. I assumed I was writing
a SQL statement in a VB script on an ASP page...but that's a new
acronym for me! Also, the code you included looks totally different
than anything else on my page, so I am having trouble figuring out
where it all fits in, etc.

But I will look into this a bit more, and I'm sure your suggestions
about CAST, CONVERT, CASE, etc. will come in handy.

Thanks again,
Rachel

"Craig Kelly" <cn************@nospam.net> wrote in message news:<v5*********************@bgtnsc04-news.ops.worldnet.att.net>...

"Rachel Weeden" wrote:

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel Weeden

Rachel,

[Note: I typed some of the T-SQL code in my newsreader, so formatting and
syntax may be a little goofy, but it should get you started in the right
direction.]

The square brackets are OK in T-SQL. The problem you're having is that your
WHERE clause is using VBA functions. While this is a cool feature in the
JET database engine, you can't use it in T-SQL (or any other DB environment
that I'm aware of). As others have mentioned:

- Use CAST or CONVERT instead of CInt (or any of the VB casting functions
e.g. CStr, CDbl, etc)

- Use CASE instead of IIf

Also,

- In VB, IsNull is a boolean function that returns true if the single
argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
parameters; if the first argument is NULL it returns the second else it
returns the first. For example:

ISNULL(NULL, 1) returns 1
...and....
ISNULL(2, 1) returns 2

A rough translation of your code would go something like (watch out for word
wrap and funny formatting)...

AND (
CASE
WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
THEN 9999

WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
THEN 9999

ELSE
CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
1800 AND 1899
THEN 1
ELSE 0
END
END
)

However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
representation or it is numeric and is inclusively in the range 1800-1899".
If I'm correct, you could use something like this (tested in Query Analyzer
with SQL Server 2000)...

DECLARE @tab TABLE (
d varchar(32),
ccc varchar(20)
)

INSERT @tab VALUES ('Num outside range', '1750')
INSERT @tab VALUES ('Num in range', '1800')
INSERT @tab VALUES ('Not a num', 'aaa')
INSERT @tab VALUES ('NULL', NULL)
INSERT @tab VALUES ('Empty string', '')

SELECT *
FROM @tab
WHERE CASE WHEN ISNUMERIC(ccc) = 1
THEN
CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
THEN 1
ELSE 0
END
ELSE 1
END = 1

This returns everything in the test table except the 'Num outside range'
row.

Craig