Solved

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

Posted on 2012-04-12
2
261 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now