Are you sure that SQL statement is good and returning the proper rows? Somehow, I doubt it.
This is what it looks like when you properly format the SQL and capitalize all the keywords:
SELECT U.SearchKey
, pj.LOCATION_CODE
, pj.PRODUCT_LINE
, pj.PAINT_CODE
, pj.DATE_PROMISED
, (SELECT AlphaValue
FROM JobscopeMeyer.dbo.UserDefinedFields
WHERE SearchKey IN
('" + strRelease + "'+ ' 001')
AND DataItem ='WORK ORDER'
GROUP BY AlphaValue
) AS Work_Order
, sj.BILL_CODE AS 'Bill Code'
, sj.WIP_CODE AS 'WIP Code'
, MAX(
CASE WHEN U.dataitem = '12INTISSUE'
THEN U.AlphaValue
ELSE ''
END
) AS '12INTISSUE'
, MAX(
CASE WHEN U.dataitem = '13PRT STAT'
THEN U.AlphaValue
ELSE ''
END
) AS '13PRT_STAT'
, MAX(
CASE WHEN U.dataitem = '14E/D/C/C'
THEN U.AlphaValue
ELSE ''
END
) AS '14E_D_C_C'
, MAX(
CASE WHEN U.DataItem = '15RCP CMP'
THEN U.AlphaValue
ELSE ''
END
) AS '15RCP_CMP'
, MAX(
CASE WHEN U.DataItem = '16EST COMP'
THEN U.AlphaValue
ELSE ''
END
) AS '16EST_COMP'
, MAX(
CASE WHEN U.DataItem = '17AP CK SK'
THEN U.AlphaValue
ELSE ''
END
) AS '17AP CK SK'
, MAX(
CASE WHEN U.DataItem = '18AP CK CM'
THEN U.AlphaValue
ELSE ''
END
) AS '18AP CK CM'
, MAX(
CASE WHEN U.DataItem = '19AP OUTSK'
THEN U.AlphaValue
ELSE ''
END
) AS '19AP OUTSK'
, MAX(
CASE WHEN U.DataItem = '20AP OUTCM'
THEN U.AlphaValue
ELSE ''
END
) AS '20AP OUTCM'
, MAX(
CASE WHEN U.DataItem = '21AP RTNSK'
THEN U.AlphaValue
ELSE ''
END
) AS '21AP RTNSK'
, MAX(
CASE WHEN U.DataItem = '22AP RTNCM'
THEN U.AlphaValue
ELSE ''
END
) AS '22AP RTNCM'
, MAX(
CASE WHEN U.DataItem = '23FB CHKSK'
THEN U.AlphaValue
ELSE ''
END
) AS '23FB CHKSK'
, MAX(
CASE WHEN U.DataItem = '24FB CHKCM'
THEN U.AlphaValue
ELSE ''
END
) AS '24FB CHKCM'
, MAX(
CASE WHEN U.DataItem = '25FB SCH'
THEN U.AlphaValue
ELSE ''
END
) AS '25FB SCH'
,MAX(
CASE WHEN U.DataItem = '26FB CMP'
THEN U.AlphaValue
ELSE ''
END
) AS '26FB CMP'
, MAX(
CASE WHEN U.DataItem = '59FOO CMP'
THEN U.AlphaValue
ELSE ''
END
) AS '59FOO CMP'
FROM UserDefinedFields U
LEFT JOIN (
SELECT LOCATION_CODE
, paint_code
, PRODUCT_LINE
, DATE_PROMISED
, RELEASE AS RELEASE_PJ
, MAX(DATE_REQUESTED) AS DATE_REQ
FROM JobscopeMeyer.dbo.PPJOBD
WHERE DATE_REQUESTED NOT IN (0, 99999999)
GROUP BY RELEASE, LOCATION_CODE, paint_code, PRODUCT_LINE, DATE_PROMISED
) PJ
ON RELEASE_PJ = SearchKey
LEFT JOIN (
SELECT BILL_CODE
, WIP_CODE
, JOB_NUMBER
FROM JobscopeMeyer.dbo.IPJOBM
WHERE JOB_NUMBER IN
('" + strRelease + "')
) sj
ON U.SearchKey = sj.JOB_NUMBER
WHERE U.SearchKey IN
('" + strRelease + "')
AND U.dataitem IN
('12INTISSUE','13PRT STAT','14E/D/C/C','15RCP CMP','16EST COMP','17AP CK SK','18AP CK CM','19AP OUTSK','20AP OUTCM','21AP RTNSK','22AP RTNCM','23FB CHKSK','24FB CHKCM','25FB SCH','26FB CMP','27 PRODWK','28 SHIPMON','30 %COMPL','31 TGTSHIP','34GI CMP','59FOO CMP','Work Order')
GROUP BY U.SearchKey, PJ.DATE_REQ, PJ.LOCATION_CODE, pj.paint_code, pj.PRODUCT_LINE, pj.DATE_PROMISED, sj.BILL_CODE, sj.WIP_CODE"
The SQL looks written to compensate for bad table design and bad data.
But, I'll clue you in where I
think the problem is going to be:
SELECT U.SearchKey
, pj.LOCATION_CODE
, pj.PRODUCT_LINE
, pj.PAINT_CODE
, pj.DATE_PROMISED
, (SELECT AlphaValue
FROM JobscopeMeyer.dbo.UserDefinedFields
WHERE SearchKey IN
('" + strRelease + "'+ ' 001') <
AND DataItem ='WORK ORDER'
GROUP BY AlphaValue
) AS Work_Order
, sj.BILL_CODE AS 'Bill Code'
...