By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Fix my SQL "WHERE" Statement!!

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.
Jul 20 '05 #2

P: n/a
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.
Jul 20 '05 #3

P: n/a
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)
Jul 20 '05 #4

P: n/a
"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
Jul 20 '05 #5

P: n/a
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

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.