Coldfusion Dynamic Variable (?) parsed to SQL Query

Question

This is the first time I am attempting to parse a variable to a CF query, but I have run into a few little issues.

In summary, I am creating a pivot table of sales by operators by week. Manually, no hassle, but I only want a subset of weeks, not all. Again, no real problem if I want hardcoded weeks, but the problem comes in when I try and parse a week number to the SQL query to create the subset of dynamic weeks.

The CFDUMP shows me that the query is executing based on what I am sending to it, but when it comes to outputting the value of the field (the week), it takes the variable name value, and not the field value if that makes sense?

I know that I shouldn't be having field names as values, but I still tryingh to test right now. With the manual query, I prefix the week number with a 'W' e.g. W9, but when I try and do that I get

MANUAL QUERY

SELECT UserName,
       ISNULL([W6], 0) AS [W6],
       ISNULL([W7], 0) AS [W7],
       ISNULL([W8], 0) AS [W8],
       ISNULL([W9], 0) AS [W9],
       ISNULL([W10], 0) AS [W10]
  FROM (   SELECT CASE
                       WHEN SUBSTRING(Username, 1, 3) = 'd.S' THEN 'DS'
                       WHEN SUBSTRING(Username, 1, 3) = 'p.R' THEN 'PR'
                       WHEN SUBSTRING(Username, 1, 3) = 'j.G' THEN 'JG'
                       WHEN SUBSTRING(Username, 1, 3) = 'b.c' THEN 'BC'
                       ELSE 'Other' END AS Username,
                  CONCAT('W', DATEPART(isowk, ERCFullAuditDate)) as XWeek,
                  COUNT(1) [SalesCount]
             FROM [ERC-Transactions].[dbo].[ERC-Audit]
            WHERE (   ERCModule = 'Carriage Return on Account'
                AND   ERCFullAuditDate >= DATEADD(week, -4, GETDATE())
                 OR   (   ERCFullAuditDate <= DATEADD(week, -52, convert(datetime, GETDATE()))
                    and   ERCFullAuditDate >= DATEADD(week, -56, convert(datetime, GETDATE()))))
            GROUP BY DATEPART(isowk, ERCFullAuditDate),
                     UserName) ST
  PIVOT (   SUM(SalesCount)
            for XWeek in ([W6], [W7], [W8], [W9], [W10])) as StorePivot

The above yields this result.

THE COLDFUSION DYNAMIC QUERY

Now when I try and do the same, but by parsing variables to the query, the CFDUMP yields the correct values, but as I said, when I try and output this, I get the field name and not the value.

To be honest, I have two issues here in that I need to address. The variable field name, but also when I come to adding the concatenation of 'W' to the week number, I am seeing an 'Error converting data type nvarchar to int.' I think I may need a cfqueryparam, but I am not sure.

<cfset WEEK_2 = DATETImeFormat(DateAdd("ww",-2,now()),"w")>
           
<cfoutput>Week: #WEEK_2#</cfoutput> (This is the value of the WEEK_2 variable)<br>       

<cfset XX = "">
<cfset XX = XX & "SELECT UserName, ">
<cfset XX = XX & "ISNULL([#WEEK_2#], 0) AS [#WEEK_2#] ">
<cfset XX = XX & "FROM (   SELECT CASE ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'd.S' THEN 'DS' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'p.R' THEN 'PR' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'j.G' THEN 'JG' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'b.c' THEN 'BC' ">
<cfset XX = XX & "ELSE 'Other' END AS Username, ">
<cfset XX = XX & "DATEPART(isowk, ERCFullAuditDate) as XWeek, ">
<cfset XX = XX & "COUNT(1) [SalesCount] ">
<cfset XX = XX & "FROM [EBS-ERC-Transactions].[dbo].[ERC-Audit] ">
<cfset XX = XX & "WHERE (   ERCModule = 'Carriage Return on Account' ">
<cfset XX = XX & "AND   ERCFullAuditDate >= DATEADD(week, -4, GETDATE()) ">
<cfset XX = XX & "OR   (   ERCFullAuditDate <= DATEADD(week, -52, convert(datetime, GETDATE())) ">
<cfset XX = XX & "and   ERCFullAuditDate >= DATEADD(week, -56, convert(datetime, GETDATE())))) ">
<cfset XX = XX & "GROUP BY DATEPART(isowk, ERCFullAuditDate), ">
<cfset XX = XX & "UserName) ST ">
<cfset XX = XX & "PIVOT (   SUM(SalesCount) ">
<cfset XX = XX & "for XWeek in ([#WEEK_2#])) as StorePivot ">

<cfquery name = "QueryTest" dataSource = "EBSERC"> 
    #PreserveSingleQuotes(XX)# 
</cfquery>

<br>

<cfoutput Query="QueryTest">
    #UserName#, #WEEK_2#<br>
</cfoutput>

<br>
<cfdump var="#QueryTest#" />

And this is the result ...

Ultimately, as mentioned before, I want to concatenate 'W' to the week number field.

Any guidance or a steer in the right direction will be VERY appreciated and thanks for your time.

Thank you very much for reading.

Ignoring the question for a second ... 1) The `WHERE` clause isn't doing what you think. It currently mixes AND/OR which may produce unexpected results. The current clause essentially does this *where Col1 = X **AND** Col2 = Y **OR** Col2 = Z* ... but should be grouping the conditions like this: *where Col1 = X **AND** ( Col2 = Y **OR** Col2 = Z )* 2) There shouldn't be a need for all the concatenation. Strings can usually span multiple lines, and there's always cfsavecontent if needed. Removing all the "&" will greatly improve readability — SOS, Mar 09 '22 at 17:55
Small code shortcut that you might like; for concat strings, you can use the &= operator: . — Scott Jibben, Mar 11 '22 at 19:33
Hello Scott - thanks a ton for the feedback and tip. That will make a massive difference in the old way I do things. Cheers for that, Sir. — Mr Jackson, Mar 15 '22 at 07:02

score 2 · Answer 1 · answered Mar 09 '22 at 11:42

2

If you're trying to copy the SQL exactly I think you missed the 'W' in the cfset at the top:

Outputs:

Week: W9 (This is the value of the WEEK_2 variable)

answered Mar 09 '22 at 11:42

Nichoru

41
1

Thanks so much for the feedback. I think I did try that at one point, but got a 'Error converting data type nvarchar to int.' I get the error message, just not sure if the w9 is meant to be parsed as a date possible (with out the 'w') if that makes sense. – Mr Jackson Mar 09 '22 at 13:52
@MrJackson - It should work fine as long as you add a leading "W" to the "XWeek" column as well: `CONCAT('W', DATEPART(isowk, ERCFullAuditDate)) as XWeek,`. If you forgot to add it, that may be the problem you encountered. – SOS Mar 09 '22 at 19:39

score 2 · Answer 2 · answered Mar 09 '22 at 14:15

You have this command:

<cfset XX = XX & "ISNULL([#WEEK_2#], 0) AS [#WEEK_2#] ">

Since the week_2 variable has a value of 9, when that command gets executed, it becomes this:

<cfset XX = XX & "ISNULL([9], 0) AS [9] ">

and your query becomes

select username
, 9 as 9
from etc

You are going to have to put some more thought into generating your week numbers.

SOS · Accepted Answer · 2022-03-10T02:13:23.713

This is too long for comments.

Since the column labels are dynamic and closely bound to the database, I'd probably do this all on the database side. Preferably inside a stored procedure.

If you're using SQL Server 2017+, a CTE could be used to generate the weeks within the desired date range. Then STRING_AGG() used to convert the results into comma separated lists.

; WITH dates AS (
   -- Generate range between -56 and -52 weeks ago
   SELECT DateAdd(wk, -56, GETDATE()) AS WeekDate
   UNION ALL
   SELECT DateAdd(wk, 1, WeekDate)
   FROM   dates
   WHERE  DateAdd(wk, 1, WeekDate) <= DateAdd(wk, -52, GETDATE())
)
, dateLabels AS (
  -- extract week number and construct label "W1","W2",etc..
  SELECT  DATEPART(isowk, WeekDate) AS WeekNum
          , QUOTENAME( CONCAT('W', DATEPART(isowk, WeekDate) )) AS WeekLabel
  FROM   dates
)
-- convert to comma separated lists
SELECT STRING_AGG( WeekLabel, ',') 
            WITHIN GROUP (ORDER BY WeekNum) AS PivotColumns
      , STRING_AGG( CONCAT('ISNULL(', WeekLabel, ',0) AS ', WeekLabel ), ',') 
            WITHIN GROUP (ORDER BY WeekNum) AS SelectColumns
FROM  dateLabels
;

The results would look like this (minus any line wrapping)

PivotColumns SelectColumns

[W6],[W7],[W8],[W9],[W10] ISNULL([W6],0) AS [W6],ISNULL([W7],0) AS [W7],ISNULL([W8],0) AS [W8],ISNULL([W9],0) AS [W9],ISNULL([W10],0) AS [W10]

Then simply plug the two lists into the SQL query. In CF:

<cfscript>
   // ...
   sqlString = "
        SELECT UserName 
               , #SelectColumns#
        FROM (   

                ... 

             ) ST
             PIVOT 
             (   
                SUM(SalesCount)
                FOR XWeek IN ( #PivotColumns# )

             ) AS StorePivot
    ";

    qPivot = queryExecute( sqlString );
    // ...
</cfscript>

Results:

SELECT UserName
      , ISNULL([W6],0) AS [W6]
      , ISNULL([W7],0) AS [W7]
      , ISNULL([W8],0) AS [W8]
      , ISNULL([W9],0) AS [W9]
      , ISNULL([W10],0) AS [W10]
FROM (   

      ... 
      
   ) ST
   PIVOT (   
          SUM(SalesCount)
          FOR XWeek IN ( 
              [W6],[W7],[W8],[W9],[W10]
          )
  ) AS StorePivot

WHERE oh where are the parentheses?

A few observations about the original query:

The current WHERE clause is incorrect. When mixing AND/OR operators, parentheses must be used to ensure the order of operations. Conceptually, the current query is doing this:

   WHERE Condition1 AND Condition2 OR Condition3

It should be using parentheses here:

   WHERE Condition1 AND ( Condition2 OR Condition3 )

GETDATE() already returns a datetime value, so there's no need to convert it to a datetime again here:

DATEADD(week, -52, convert(datetime, GETDATE()))

Lastly, there shouldn't be a need for all the concatenation. Strings can usually span multiple lines, and there's always cfsavecontent if needed. Removing all the & will greatly improve readability.

SOS, as always, thank you! I see where you are coming from and it all makes sense. I just need to spend some time now and get familiar with stored procedures and injecting it into the script. I will come back to you on the outcome. — Mr Jackson, Mar 10 '22 at 08:07
You're welcome. Technically, you could use two cfquery's, like in the above code, instead of a procedure as well. A procedure makes more sense, IMO, but either works. — SOS, Mar 10 '22 at 16:48

Coldfusion Dynamic Variable (?) parsed to SQL Query

3 Answers3