Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SYNTAX ERROR WITH IN CLAUSE IN PIVOT QUERY Msg 102, Level 15, State 1, Line 24

Posted on 2012-04-12
2
Medium Priority
?
277 Views
Last Modified: 2012-06-21
declare @MyCol as varchar(250)

 set @MyCol = (SELECT     R2.MYMONTH + ', '
                            FROM          SALES_TABLE_Col R2
                             FOR XML PATH(''))

SELECT
SALE_LINE, SALES_PART, SALES_PART_DESC, 'MYSORT' AS SORT_TYPE, OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING
FROM
( SELECT SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH, SUM(SALES_AMT) AS SALES_AMT
FROM
(SELECT SALE_LINE, SALES_PART, SALES_PART_DESC,
CASE WHEN SALES_DATE < GETDATE() AND STATUS <> 'H' THEN 'OVERDUE'
       WHEN SALES_DATE > [ENDING_DATE] AND STATUS <> 'H' THEN 'FUTUREDUE'
       WHEN STATUS = 'H' OR SALES_DATE IS NULL THEN 'PENDING'
       ELSE MYMONTH END AS MYMONTH,
 SALES_AMT
FROM SALES_TABLE
) MYSALES
GROUP BY SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH
) AS SourceTable
PIVOT
(SUM(SALES_AMT)
    FOR [MYMONTH] IN ( OVERDUE,  ' + @MyCol + '  FUTUREDUE, PENDING)
     ) AS PivotTable
0
Comment
Question by:NettieEckenrode
2 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 37840308
declare @MyCol as varchar(250)

 set @MyCol = (SELECT R2.MYMONTH + ', ' FROM SALES_TABLE_Col R2 FOR XML PATH(''))


declare @sql varchar(8000) = '
SELECT
SALE_LINE, SALES_PART, SALES_PART_DESC, ''MYSORT'' AS SORT_TYPE, OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING
FROM
( SELECT SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH, SUM(SALES_AMT) AS SALES_AMT
FROM
(SELECT SALE_LINE, SALES_PART, SALES_PART_DESC,
CASE WHEN SALES_DATE < GETDATE() AND STATUS <> ''H'' THEN ''OVERDUE''
       WHEN SALES_DATE > [ENDING_DATE] AND STATUS <> ''H'' THEN ''FUTUREDUE''
       WHEN STATUS = ''H'' OR SALES_DATE IS NULL THEN ''PENDING''
       ELSE MYMONTH END AS MYMONTH,
 SALES_AMT
FROM SALES_TABLE
) MYSALES
GROUP BY SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH
) AS SourceTable
PIVOT
(SUM(SALES_AMT)
    FOR [MYMONTH] IN ( OVERDUE,  ' + @MyCol + ' FUTUREDUE, PENDING)
     ) AS PivotTable
'    

-- print @sql

exec(@sql)
0
 

Author Closing Comment

by:NettieEckenrode
ID: 37840460
I had to change the following:
 
 declare @sql varchar(8000) = '
SELECT

to

  declare @sql varchar(8000)

set @sql = '  select

Thanks so much.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question