July 7, 2004 at 5:12 pm
I am trying to pull selected records using a where clause as follows (the pound signs denote variables from Cold Fusion application):
WHERE #form.pStreetNumber# BETWEEN Cast(AddrPrimaryLowNo As Integer) AND Cast(AddrPrimaryHighNo As Integer) And .....
I get an error stating:
Encountered "33 Between ( Cast" at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition
Can someone set me straight here... in ACCESS I use:
WHERE IsNumeric(AddrPrimaryLowNo) AND IsNumeric(AddrPrimaryHighNo) AND #form.pStreetNumber# Between Int(AddrPrimaryLowNo) AND Int(AddrPrimaryHighNo) AND......
Thanks!!!!!
July 7, 2004 at 8:11 pm
What is the full code lines source code? How are you applying the variable to the string and submitting to sql?
July 7, 2004 at 8:17 pm
If I am reading this correctly, you are using MS ACCESS and passing in a parameter from a form.
Try this:
WHERE #form.pStreetNumber# BETWEEN CInt(AddrPrimaryLowNo) AND CInt(AddrPrimaryHighNo) And .....
Access uses different conventions than SQL.
Hope that helps.
I wasn't born stupid - I had to study.
July 7, 2004 at 10:54 pm
In SQL the conversion from a character to an integer is implicit. You can drop the 'CAST' in the select.
July 7, 2004 at 11:01 pm
Let me clarify. I developed in Access but need to migrate to SQLServer. Access code works well with Access db(note that I am using Cold Fusion thus the pundsigns and CFIF tags - errors aren't affected by presence or absence of CFIF tags). Heres the code that works in Access:
Select ZipAddOnLowSectorNo, ZipAddOnLowSegmentNo
From zipcodestable
Where IsNumeric(AddrPrimaryLowNo) AND IsNumeric(AddrPrimaryHighNo) And #form.pStreetNumber# Between Int(AddrPrimaryLowNo) And Int(AddrPrimaryHighNo) AND streetname='#form.pStreetName#' AND StreetSuffixAbbr='#form.pStreetSuffix#' And AddrPrimaryOddEvenCode LIKE '#oddeven#' And zipCode LIKE '#form.pZip#'<CFIF IsDefined("pStreetDirection") AND pStreetDirection IS NOT ""> AND StreetPreDirAbbr LIKE <CFIF pStreetDirection IS "North">'N'<CFELSEIF pStreetDirection IS "South">'S'<CFELSEIF pStreetDirection IS "East">'E'<CFELSEIF pStreetDirection IS "West">'W'</CFIF></CFIF>
I am using Cold Fusion MX on a windows server. I was trying to convert the code to work with SQLServer syntax... The db doesnt want to convert the AddrPrimaryHighNo and AddrPrimaryLowNo to intergers even tho I am filtering for only those records that have numeric values in this field. The db is from our illustrious Post Office and the AddrPrimaryHighNo and AddrPrimaryLowNo fields occasionally contain nonnumeric values (about 30K of 1.3 million records).
I have been trying:
Select ZipAddOnLowSectorNo, ZipAddOnLowSegmentNo
From zipcodestable
Where IsNumeric(AddrPrimaryLowNo)=1 AND IsNumeric(AddrPrimaryHighNo)=1 AND zipcode=#form.pZip# And #form.pStreetNumber# Between Cast(AddrPrimaryLowNo As Integer) And Cast(AddrPrimaryHighNo As Integer) And AddrPrimaryOddEvenCode LIKE '#oddeven#' AND streetname = '#form.pStreetName#' AND StreetSuffixAbbr='#form.pStreetSuffix#'<CFIF IsDefined("pStreetDirection") AND pStreetDirection IS NOT ""> AND StreetPreDirAbbr LIKE <CFIF pStreetDirection IS "North">'N'<CFELSEIF pStreetDirection IS "South">'S'<CFELSEIF pStreetDirection IS "East">'E'<CFELSEIF pStreetDirection IS "West">'W'</CFIF></CFIF>
July 8, 2004 at 7:20 pm
Unforutnately I have not had the need to mess with Cold Fusion yet so I am in know way truely knowledgeable. I suggest trying the user forms at the macromedia site as there is a section dedicated to Database Access.
July 8, 2004 at 7:35 pm
So are u saying that the SQL protion of the code I posted should work. I am trying to get the SQL protion correct. The Cold Fusion just send the SWL to the SQL Server to run. I dont think the Cold Fusion is causing the problem here.
Thanks!!
July 9, 2004 at 9:52 am
There should be no problem using CAST with BETWEEN. I just tried it successfully in QA.
However, your query looks a little odd:
WHERE #form.pStreetNumber# BETWEEN Cast(AddrPrimaryLowNo As Integer) AND Cast(AddrPrimaryHighNo As Integer) And
It looks like you're querying the form, rather than a table. I assume that '33' was entered into form.pStreetNumber?
Sorry if I'm betraying my ignorance of your environment.
July 9, 2004 at 11:44 am
Joe,
I have two fields in the table, a high value and a low value, while some values are alpha (thus the varchar type). I need to create a Where statement that pulls only records where the high and low values are numeric, and then only if the form entered value "33" falls between the two field values in the table. Does that muddy the problem any better for you?
So yes, the between is constructed in reverse to normal usage.
-Jon
July 9, 2004 at 12:08 pm
Jon,
It should still work (just tried it). Don't know what to tell you.
In your first post, your error message has a '(' after the Between and before the Cast. Was this put in by the interpreter, or is it in your code? I don't think it's allowed.
July 10, 2004 at 4:50 pm
I'm almost sure that ColdFusion is using ODBC to communicate with SQL Server, and ODBC mangles the SQL you might pass into it into its own version of SQL before passing the translated SQL to the database server, unless you're using an ODBC Pass-through query.
I don't know how to do a pass-through query with CF.
Otherwise, code the SQL you want to call into a view or stored procedure on the server and use that instead.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply