I have inherited a project. The orginal code was written when MX was around and the client was using 17 access db's to run the site.
I have converted the db's to MS SQL.
The following was the original query:
<CFQUERY NAME="GetPremium" datasource="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#" BLOCKFACTOR="100">
SELECT IIf(InStr([City],',') > 0, Left([City],InStr([City],',')-1), IIf(InStr([City],'&') > 0, Left([City], InStr([City],'&')-1), [City])) AS xCity, Count(Featured.ID) AS CountOfID
FROM Features INNER JOIN Featured ON Features.ID = Featured.ID
WHERE (((Featured.Type)='Facility') and ((Features.Cat)=<CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="#URL.Cat#">) and ((Featured.State)=<CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="#URL.ST#">) )
GROUP BY IIf(InStr([City],',') > 0, Left([City], InStr([City],',')-1) ,IIf(InStr([City],'&') > 0, Left([City],InStr([City],'&')-1), [City]))
</CFQUERY>
I changed it to:
<CFQUERY NAME="GetPremium" datasource="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#" BLOCKFACTOR="100">
SELECT IIf(CHARINDEX([City],',') > 0, Left([City],CHARINDEX([City],',')-1), IIf(CHARINDEX([City],'&') > 0, Left([City], CHARINDEX([City],'&')-1), [City])) AS xCity, Count(Featured.ID) AS CountOfID
FROM Features INNER JOIN Featured ON Features.ID = Featured.ID
WHERE (((Featured.Type)='Facility') and ((Features.Cat)=<CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="#URL.Cat#">) and ((Featured.State)=<CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="#URL.ST#">) )
GROUP BY IIf(CHARINDEX([City],',') > 0, Left([City], CHARINDEX([City],',')-1) ,IIf(CHARINDEX([City],'&') > 0, Left([City],CHARINDEX([City],'&')-1), [City]))
</CFQUERY>
I know the InStr function does not work with MS SQL so I changed that to CHARINDEX. Not sure if the formatting of the query needs to change or not or if I am any where close.
I am getting an MS SQL error stating: [SQLServer]Incorrect syntax near '>'.
Any suggestions would be helpful.
AJ
2
Replies
AdChoices