Skip to main content
A
AJBJ
Inspiring
May 7, 2012
Question

MS SQL Error Q2

  • May 7, 2012
  • 2 replies
  • 2611 views

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.

    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    May 7, 2012

    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.

    S
    Steve Sommers
    Legend
    May 7, 2012

    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.

    A
    AJBJAuthor
    Inspiring
    May 7, 2012

    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>

    S
    Steve Sommers
    Legend
    May 7, 2012

    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?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices