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 WeedenRachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.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.|||RachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.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.|||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)|||"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|||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.504132$Gx4.393231@.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
没有评论:
发表评论