May 29, 2009 at 2:37 pm
Hi Allister,
I took a look at it and there were a couple syntax errors that I fixed. But I'm still getting this error:
(15333 row(s) affected)
(15333 row(s) affected)
SELECT [Service Order ID],
[Part No], [Part Desc], [Date Consumed], [Part Status], [Quantity], MAX(CASE WHEN NVPRole = 'Sold-To Party' THEN NVPValue END) AS [Sold-To Party],
MAX(C
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FROM'.
I can't seem to find this one though. Ichecked all the brackets, quotes, ect. adn it isn't jumping out at me.....
Here's the new one...
DECLARE @SQLSelect VARCHAR(100)
DECLARE @SQLSelectList VARCHAR(100)
DECLARE @SQLFrom VARCHAR(100)
select
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Orders.PROCESS_TYPE
, CRM_Partners.DESCRIPTION as NVPRole
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
-- Here is the contents of the table
select * from #NormalNVP
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT [Service Order ID],
[Part No], [Part Desc], [Date Consumed], [Part Status], [Quantity], [Service Order Last Change], [Serial No] , [PROCESS_TYPE], ' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
Thanks, Michelle
May 30, 2009 at 3:32 am
change DECLARE @SQLSelectList VARCHAR(100) to DECLARE @SQLSelectList VARCHAR(max).
///edit
If you need more help with this you'll have to post a sample of data from temporary table #NormalNVP.
Check out Jeff's artice how to do that: http://www.sqlservercentral.com/articles/Best+Practices/61537/ if needs be. Look under Create Some Data heading.
Hope this helps.
June 1, 2009 at 1:48 pm
Hi Allister,
Here's the resolution with a slightly different approach, if you're interested.
SELECT
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Orders.PROCESS_TYPE
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, MAX(CASE WHEN CRM_Partners.DESCRIPTION ='Exec. Service Employee' THEN CRM_Partners.ADDRESS ELSE NULL END) as [Exec. Service Employee],
MAX(CASE WHEN CRM_Partners.DESCRIPTION ='Service Employee Group' THEN CRM_Partners.ADDRESS ELSE NULL END) as [Service Employee Group],
MAX(CASE WHEN CRM_Partners.DESCRIPTION ='Sold-To Party' THEN CRM_Partners.ADDRESS ELSE NULL END) as [Sold-To Party]
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
GROUP BY
CRM_PartsLabor.TRANSACTION_ID
, CRM_PartsLabor.DESCRIPTION
, CRM_Confirmations.POSTING_DATE
, CRM_StatusCodes.USER_STATUS
, CRM_PartsLabor.QUANTITY
, CRM_StatusCodes.END_DATE
, CRM_Orders.SERIAL
, CRM_Orders.PROCESS_TYPE
, CRM_PartsLabor.ORDERED_PROD
Thank again for all your help! I learned a alot from you! 😀
~Michelle
June 2, 2009 at 1:09 am
Nice one Michelle,
glad you got there, and no need for dynamic SQL!
Take care
Allister
June 4, 2009 at 7:48 am
I use this Windows help file frequently when referencing SQL commands: tsqlref.chm
You should be able to find it amongst your SQL installs.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply