Skip navigation
AJBJ
AJBJ
Currently Being Moderated

MS SQL Error Q2

May 7, 2012 8:28 AM

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.

1674 Views   5 Replies   Latest reply: BKBK, May 8, 2012 1:12 AM
 
Replies
  • Steve Sommers
    Currently Being Moderated
    1. Steve Sommers,
    May 7, 2012 8:41 AM   in reply to AJBJ
    Report

    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.

     
    |
    Mark as:
  • AJBJ
    Currently Being Moderated
    2. AJBJ,
    May 7, 2012 9:01 AM   in reply to Steve Sommers
    Report

    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>

     
    |
    Mark as:
  • Steve Sommers
    Currently Being Moderated
    3. Steve Sommers,
    May 7, 2012 9:11 AM   in reply to AJBJ
    Report

    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?

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    4. Dan Bracuk,
    May 7, 2012 2:13 PM   in reply to AJBJ
    Report

    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.

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    5. BKBK,
    May 8, 2012 1:12 AM   in reply to AJBJ
    Report

    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?

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points