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.Defen dant_ID = ARREST.Defendan t_ID) ON
PrisonRelease.P ID = Defendant.PID_C ode "

varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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]

Re: Fix my SQL "WHERE&quo t; Statement!!

RachelWeeden@ho tmail.com (Rachel Weeden) wrote in message news:<f5066a28. 0408230526.3f88 1906@posting.go ogle.com>...[color=blue]
> 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.Defen dant_ID = ARREST.Defendan t_ID) ON
> PrisonRelease.P ID = Defendant.PID_C ode "
>
> varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "
>
> varSQL = varSQL & "AND
> (IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
> Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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[/color]

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

[Philip Mette]

Re: Fix my SQL &quot;WHERE&quo t; Statement!!

RachelWeeden@ho tmail.com (Rachel Weeden) wrote in message news:<f5066a28. 0408230526.3f88 1906@posting.go ogle.com>...[color=blue]
> 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.Defen dant_ID = ARREST.Defendan t_ID) ON
> PrisonRelease.P ID = Defendant.PID_C ode "
>
> varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "
>
> varSQL = varSQL & "AND
> (IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
> Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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[/color]

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

[Gert-Jan Strik]

Re: Fix my SQL &quot;WHERE&quo t; Statement!!

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:[color=blue]
>
> 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.Defen dant_ID = ARREST.Defendan t_ID) ON
> PrisonRelease.P ID = Defendant.PID_C ode "
>
> varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "
>
> varSQL = varSQL & "AND
> (IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
> Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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[/color]

--
(Please reply only to the newsgroup)

[Craig Kelly]

Re: Fix my SQL &quot;WHERE&quo t; Statement!!

"Rachel Weeden" wrote:
[color=blue]
> 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.Defen dant_ID = ARREST.Defendan t_ID) ON
> PrisonRelease.P ID = Defendant.PID_C ode "
>
> varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "
>
> varSQL = varSQL & "AND
> (IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
> Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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[/color]

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(Defendan t.[CRIME_CLASSIFIC ATION_CODE], '') = ''
THEN 9999

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

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

However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFIC ATION_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]

Re: Fix my SQL &quot;WHERE&quo t; Statement!!

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" <cnkelly.nospam @nospam.net> wrote in message news:<v5tWc.504 132$Gx4.393231@ bgtnsc04-news.ops.worldn et.att.net>...[color=blue]
> "Rachel Weeden" wrote:
>[color=green]
> > 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.Defen dant_ID = ARREST.Defendan t_ID) ON
> > PrisonRelease.P ID = Defendant.PID_C ode "
> >
> > varSQL = varSQL & "WHERE DEFENDANT.Race_ Type_Code_L in (" &
> > varRaceList & ")AND DEFENDANT.Gende r In (" & varGenderList & ") "
> >
> > varSQL = varSQL & "AND
> > (IIf(IsNull(Def endant.[CRIME_CLASSIFIC ATION_CODE]) Or
> > Defendant.[CRIME_CLASSIFIC ATION_CODE]="" Or
> > (Defendant.[CRIME_CLASSIFIC ATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> > Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like
> > "[0-9][0-9][0-9][0-9]"),9999,CInt(De fendant.[CRIME_CLASSIFIC ATION_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[/color]
>
> 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(Defendan t.[CRIME_CLASSIFIC ATION_CODE], '') = ''
> THEN 9999
>
> WHEN Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like '[0-9][0-9][0-9]' AND
> Defendant.[CRIME_CLASSIFIC ATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
> THEN 9999
>
> ELSE
> CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFIC ATION_CODE]) BETWEEN
> 1800 AND 1899
> THEN 1
> ELSE 0
> END
> END
> )
>
> However, it appears you want something akin to "WHERE
> Defendant.[CRIME_CLASSIFIC ATION_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[/color]