MS SQL Error Q2

[AJBJ]

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.

[Steve Sommers]

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.

[AJBJ]

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>

[Steve Sommers]

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?

[BKBK]

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?

[Dan_Bracuk]

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.