Copy link to clipboard
Copied
Here is my query:
<CFQUERY NAME="GetProd"datasource="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#" BLOCKFACTOR="100">
SELECT Products.Cat, Products.Brief, Products.Title, Min(Products.Image) AS Image, Min(Products.Cfm) AS Cfm,
Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty )) AS Total, Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty)) / #dateFormat(Now())# - Min(ItemsOrdered.Date) / 30) AS AvgMon,
#dateformat(Now())# - Min(ItemsOrdered.Date) / 30 AS Mons
FROM Products LEFT JOIN ItemsOrdered ON Products.UOM = ItemsOrdered.UOM AND Products.ID = ItemsOrdered.ID
WHERE Products.Price > 0
<CFIF URL.Cfm is "HD"> AND Products.CFM Like '%HD%' </CFIF>
<CFIF URL.Cfm is "Sale">
AND Products.Special = 'Y' AND Products.SaleCode <> NULL AND Products.SaleAmt > 0 AND #dateFormat(Now(),"yyyymmdd hhnnss")# <= #dateFormat(SaleExp,"yyyymmdd")# & "140000"
</CFIF>
GROUP BY Products.Cat, Products.Brief, Products.Title
HAVING Products.Brief) = '#URL.Brief#'
<CFIF URL.Cfm is not "Sale">
AND Min(Products.Cfm Like '%#URL.cfm#%'
</CFIF>
ORDER BY Products.Cat, Products.Brief, Products.Title
</CFQUERY>
I get an error: Incorrect syntax near 'gt'.
Again, any advice would be helpful.
Copy link to clipboard
Copied
I see a few problems with your query, and most would be solved (and it is best practice) to use the cfqueryparam tag for all your variables. This by itself will probably pinpoint the exact error you are receiving.
Copy link to clipboard
Copied
I added in the cfqueryparam on 2 variables, I still get the same exact error with no additional info.
Incorrect syntax near 'gt'.
<CFQUERY NAME="GetProd" datasource="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" BLOCKFACTOR="100">
SELECT Products.Cat, Products.Brief, Products.Title, Min(Products.Image) AS Image, Min(Products.Cfm) AS Cfm,
Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty )) AS Total, Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty)) / #dateFormat(Now())# - Min(ItemsOrdered.Date) / 30 AS AvgMon,
#dateformat(Now())# - Min(ItemsOrdered.Date) / 30 AS Mons
FROM Products LEFT JOIN ItemsOrdered ON Products.UOM = ItemsOrdered.UOM AND Products.ID = ItemsOrdered.ID
WHERE Products.Price > 0
<CFIF URL.Cfm is "HD"> AND Products.CFM Like '%HD%' </CFIF>
<CFIF URL.Cfm is "Sale">
AND Products.Special = 'Y' AND Products.SaleCode <> NULL AND Products.SaleAmt > 0 AND #dateFormat(Now(),"yyyymmdd hhnnss")# <= #dateFormat(SaleExp,"yyyymmdd")# & "140000"
</CFIF>
GROUP BY Products.Cat, Products.Brief, Products.Title
HAVING Products.Brief)=<CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="#URL.Brief#">
<CFIF URL.Cfm is not "Sale">
AND Min(Products.Cfm Like <CFQUERYPARAM CFSQLTYPE="CF_SQL_CHAR" VALUE="%#URL.cfm#%">
</CFIF>
ORDER BY Products.Cat, Products.Brief, Products.Title
</CFQUERY>
Copy link to clipboard
Copied
You missed a couple, and this is where your issue is:
...Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty )) AS Total, Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty)) / #dateFormat(Now())# - Min(ItemsOrdered.Date) / 30 AS AvgMon
At a minimum you'll need hash's around the iif tags. Myself, I would change the logic to use an cfif block instead of the iif tags, especially considering that the "if" portion are the same for both so you are executing the logic twice. Supportability wise, I find the iif tags are prone to errors; not because the tag is buggy, but instead because programmers tend to misuse them.
A second but related problem is that you are dividing a sum by a date -- not number of days, but a date?
Copy link to clipboard
Copied
AJBJ wrote:
I added in the cfqueryparam on 2 variables, I still get the same exact error with no additional info.
Incorrect syntax near 'gt'.
... Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty )) ...
'gt' is ColdFusion syntax. Here, you need the equivalent SQL syntax, '>'.
Sum(IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty)) / #dateFormat(Now())# - Min(ItemsOrdered.Date) / 30) AS AvgMon
There are a number of things not quite right here. Dateformat(now()) is ColdFusion syntax, and so the dateformat function should have a mask parameter(which it doesn't0. Even then, there is no need to do all that when you could just use MS SQL's own function, getDate().
Even ignoring 'gt', shouldn't the statement 'IIf(ItemsOrdered.ReturnedAmt gt 0, 0, Qty)' actually be 'IIf(ItemsOrdered.ReturnedAmt gt 0, Qty, 0)' .
The above corrections result in:
Sum(IIf(ItemsOrdered.ReturnedAmt > 0, Qty, 0)) / getDate() - Min(ItemsOrdered.Date) / 30 AS AvgMon
It is still unclear what you wish to achieve. You divide a sum, hence a number, by a date: not good. You divide a date by the number 30: not good either. I can imagine that you had wished to evaluate some total, and to then divide it by 30 to get an average. Could you explain in words what you want to achieve here?
Copy link to clipboard
Copied
You are doing several things wrong.
Your specific error is sending ColdFusion code to sql server and expecting it to work. It won't.
You also appear to be attempting to divide by a string. That's not likely to work.
Later you appear to be trying to subtract a number from a string. Equally bad.
Your Having clause looks like it should be in your where clause.
This should get you started.