Hello All,
I have the following code which works FANTASTIC. Except - It is a sub-query to another (final) outer query and when I attempt to encapsulate this as a sub-query it refuses to run. To make it simple, I just did a "SELECT * FROM ( MY QUERY HERE)" and that even fails to run. I can only figure I am putting the right ")" in the wrong spot, but I just don't know....
Anyway - this is the query that runs fine till it is a simple sub-query:
SELECT FK_Campaign, FK_Geography, FK_InvoiceDate, FK_SalesType, FK_CustGroup, FK_Customer_Detail, FK_Parent_Detail, FK_Department, FK_Item_Detail,
FK_ItemClass_Detail, FK_ItemGroup_Detail, FK_SalesRep, FK_SalesOrigin, FK_QualityCode, FK_QualityDetailCode, FK_ResponsibilityCode,
FK_NewItemFlag, FK_TransDate, FK_CreatedDate, FK_VMReceivedDate, FK_ReturnAction, FK_StatusCode, ABC.InvoiceId, SalesID, VMRaNumber,
RetDays, ReturnQty, LineAmountmst, Qty, ReturnDays, ReturnLineAmount, ListPrice, ABC.InventTransId, ABC.ItemId,
ZZZ.FK_InventSerialProdDate AS FK_InventSerialProdDate
FROM
(SELECT
ISNULL((SELECT campaignkey --PK_Campaign
FROM VMDWAnalytical.DBO.DIM_Campaign
WHERE CampaignID = ST.smmCampaignID), -99) FK_Campaign,
ISNULL((SELECT PK_Geography
FROM VMDWAnalytical.DBO.DIM_Geography G
WHERE G.Country = ISNULL(CIJ.DLVCountryRegionID, 'zUnknown')
AND G.State = ISNULL(CIJ.DLVState, 'zUnknown')
AND G.Zip = ISNULL(CIJ.DLVZipCode, 'zUnknown')), -99) FK_Geography,
ISNULL((SELECT T.TIMEKEY
FROM VMDWAnalytical.DBO.DIM_Time T
WHERE T.FullDate = CIJ.InvoiceDate), 1) FK_InvoiceDate,
ISNULL((SELECT PK_SalesType
FROM VMDWAnalytical.DBO.DIM_SalesType
WHERE SalesType = SL.SalesType), -99) FK_SalesType,
ISNULL((SELECT PK_CustGroup
FROM VMDWAnalytical.DBO.DIM_CustGroup
WHERE CustGroup = CT.CustGroup), -99) FK_CustGroup,
ISNULL((SELECT PK_Customer_Detail
FROM VMDWAnalytical.DBO.DIM_Customer_Detail
WHERE AccountNum = CIJ.OrderAccount), -99) FK_Customer_Detail,
ISNULL((SELECT PK_Parent_Detail
FROM VMDWAnalytical.DBO.DIM_Parent_Detail
WHERE AccountNum = CIJ.OrderAccount), -99) FK_Parent_Detail,
ISNULL((SELECT PK_Department
FROM VMDWAnalytical.DBO.DIM_Department D
WHERE D.NUM = CIT.Dimension), -99) FK_Department,
ISNULL((SELECT PK_Item_Detail
FROM VMDWAnalytical.dbo.DIM_Item_Detail
WHERE Item = IT.ItemID
AND (ConfigID = ID.ConfigID
OR ConfigID = 'NONE')), -99) FK_Item_Detail,
ISNULL((SELECT PK_ItemClass_Detail
FROM VMDWAnalytical.dbo.DIM_ItemClass_Detail
WHERE ItemID = IT.ItemID), -99) FK_ItemClass_Detail,
ISNULL((SELECT PK_ItemGroup_Detail
FROM VMDWAnalytical.dbo.DIM_ItemGroup_Detail
WHERE ItemID = IT.ItemID), -99) FK_ItemGroup_Detail,
ISNULL((SELECT PK_SalesRep
FROM VMDWAnalytical.DBO.DIM_SalesRep
WHERE SalesRep = ST.SalesResponsible), -99) FK_SalesRep,
ISNULL((SELECT PK_SalesOrigin
FROM VMDWAnalytical.DBO.DIM_SalesOrigin
WHERE OriginID = ST.SalesOriginID), -99) FK_SalesOrigin,
CASE
WHEN CIT.Qty < 0 AND RR.TransDate IS NULL THEN
-97
WHEN CIT.QTY >=0 AND RR.TransDate IS NULL THEN
-98
ELSE
ISNULL((SELECT PK_QualityCode
FROM VMDWAnalytical.dbo.DIM_QualityCode
WHERE QualityCode = RR.VMQualityCode), -99)
END FK_QualityCode,
CASE
WHEN CIT.Qty < 0 AND RR.TransDate IS NULL THEN
-97
WHEN CIT.QTY >=0 AND RR.TransDate IS NULL THEN
-98
ELSE
ISNULL((SELECT PK_QualityDetailCode
FROM VMDWAnalytical.dbo.DIM_QualityDetailCode
WHERE QualityDetailCode = SL.VMQualityDetailCode
AND QualityCode = RR.VMQualityCode), -99)
END FK_QualityDetailCode,
CASE
WHEN CIT.Qty < 0 AND RR.TransDate IS NULL THEN
-97
WHEN CIT.QTY >=0 AND RR.TransDate IS NULL THEN
-98
ELSE
ISNULL((SELECT PK_ResponsibilityCode
FROM VMDWAnalytical.dbo.DIM_ResponsibilityCode
WHERE ResponsibilityCode = RR.VMResponsibilityCode), -99)
END FK_ResponsibilityCode,
CASE
WHEN ST.CREATEDDATE <= DATEADD(YEAR, 2, (SELECT KPISTARTDATE FROM RDS_KPIInventTableRel WHERE ITEMID = IT.ITEMID)) THEN
(SELECT PK_NEWITEMFLAG FROM VMDWAnalytical.DBO.DIM_NewItemFlag WHERE NewItemFlag = 'NEW')
ELSE
(SELECT PK_NEWITEMFLAG FROM VMDWAnalytical.DBO.DIM_NewItemFlag WHERE NewItemFlag = 'EXISTING')
END FK_NewItemFlag,
ISNULL((SELECT T.TIMEKEY
FROM VMDWAnalytical.DBO.DIM_Time T
WHERE T.FullDate = RR.TransDate), 1) FK_TransDate,
ISNULL((SELECT T.TIMEKEY
FROM VMDWAnalytical.DBO.DIM_Time T
WHERE T.FullDate = RR.CreatedDate), 1) FK_CreatedDate,
ISNULL((SELECT T.TIMEKEY
FROM VMDWAnalytical.DBO.DIM_Time T
WHERE T.FullDate = RR.VMReceivedDate), 1) FK_VMReceivedDate,
CASE
WHEN CIT.Qty < 0 AND RR.TransDate IS NULL THEN
-97
WHEN CIT.QTY >=0 AND RR.TransDate IS NULL THEN
-98
ELSE
ISNULL((SELECT PK_ReturnAction
FROM VMDWAnalytical.dbo.DIM_ReturnAction
WHERE ReturnActionID = RR.ReturnActionID), -99)
END FK_ReturnAction,
CASE
WHEN CIT.Qty < 0 AND RR.TransDate IS NULL THEN
-97
WHEN CIT.QTY >=0 AND RR.TransDate IS NULL THEN
-98
ELSE
ISNULL((SELECT PK_StatusCode
FROM VMDWAnalytical.dbo.DIM_StatusCode
WHERE StatusCode = RR.VMStatusCode), -99)
END FK_StatusCode,
ISNULL(CIT.InvoiceID, 'zUnknown') InvoiceID,
ISNULL(CIT.InventTransId, 'zUnknown') InventTransId,
ISNULL(CIT.ItemId, 'zUnknown') ItemId,
ISNULL(ST.SalesID, 'zUnknown') SalesID,
ISNULL(RR.VMRaNumber, 'zUnknown') VMRaNumber,
CASE
--A, Take out the Service item, flag with -98
WHEN IT.ITEMGROUPID = 'SERVICE' THEN
-98
--B, There is an original SO and the difference between the dates is non-negative
WHEN (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM) IS NOT NULL
AND DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM), ST.CREATEDDATE) >= 0 THEN
DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM), ST.CREATEDDATE)
--C, The last 10 characters of the refnum field start with SOR, and the orig SO date is not null and the difference between the dates is non-negative
WHEN RIGHT(ST.PURCHORDERFORMNUM, 10) LIKE 'SOR%' AND (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = RIGHT(ST.PURCHORDERFORMNUM, 10)) IS NOT NULL
AND DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = RIGHT(ST.PURCHORDERFORMNUM, 10)), ST.CREATEDDATE) >= 0 THEN
DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = RIGHT(ST.PURCHORDERFORMNUM, 10)), ST.CREATEDDATE)
--D, There is an original SO and the difference between the dates is negative
WHEN (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM) IS NOT NULL
AND DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM), ST.CREATEDDATE) < 0 THEN
DATEDIFF(DAY, (SELECT SCT.CAMPAIGNDATE FROM RDS_SMMCAMPAIGNTABLE SCT WHERE SCT.CAMPAIGNID = ST.SMMCAMPAIGNID), ST.CREATEDDATE)
--E, The last 10 characters of the refnum field start with SOR, and the orig SO date is not null, and the difference between the dates is negative
WHEN RIGHT(ST.PURCHORDERFORMNUM, 10) LIKE 'SOR%' AND (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = RIGHT(ST.PURCHORDERFORMNUM, 10)) IS NOT NULL
AND DATEDIFF(DAY, (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = RIGHT(ST.PURCHORDERFORMNUM, 10)), ST.CREATEDDATE) < 0 THEN
DATEDIFF(DAY, (SELECT SCT.CAMPAIGNDATE FROM RDS_SMMCAMPAIGNTABLE SCT WHERE SCT.CAMPAIGNID = ST.SMMCAMPAIGNID), ST.CREATEDDATE)
--F, If the campaign date <> 1/1/1900 and the datediff is positive
WHEN (SELECT SCT.CAMPAIGNDATE FROM RDS_SMMCAMPAIGNTABLE SCT WHERE SCT.CAMPAIGNID = ST.SMMCAMPAIGNID) <> '1/1/1900'
AND DATEDIFF(DAY, (SELECT SCT.CAMPAIGNDATE FROM RDS_SMMCAMPAIGNTABLE SCT WHERE SCT.CAMPAIGNID = ST.SMMCAMPAIGNID), ST.CREATEDDATE) >= 0 THEN
DATEDIFF(DAY, (SELECT SCT.CAMPAIGNDATE FROM RDS_SMMCAMPAIGNTABLE SCT WHERE SCT.CAMPAIGNID = ST.SMMCAMPAIGNID), ST.CREATEDDATE)
--G, If the difference between the Return SO and the Return and Repair transdate is positive
WHEN DATEDIFF(DAY, ST.CREATEDDATE, RR.TRANSDATE) > 0 THEN
DATEDIFF(DAY, ST.CREATEDDATE, RR.TRANSDATE)
--H
ELSE
-97
END RETDAYS,
--RETURNS
-1 * (CASE
WHEN SL.SALESTYPE = 4 THEN
CIT.LINEAMOUNTMST
ELSE
0
END) AS ReturnLineAmount,
-1 * (CASE
WHEN SL.SALESTYPE = 4 THEN
CIT.QTY
ELSE
0
END) AS ReturnQty,
--/RETURNS
(CIT.LINEAMOUNTMST) AS LineAmountMst, --Measure
(CIT.QTY) AS Qty, --Measure
ISNULL(DATEDIFF(DAY, RR.CreatedDate, RR.TransDate), 0) ReturnDays,
ITMS.PRICE AS ListPrice --Measure
FROM RDS_CUSTINVOICETRANS CIT
LEFT JOIN RDS_CUSTINVOICEJOUR CIJ
ON CIT.INVOICEID = CIJ.INVOICEID
JOIN RDS_CUSTTABLE CT
ON CIJ.ORDERACCOUNT = CT.ACCOUNTNUM
LEFT JOIN RDS_ADDRESSCOUNTRYREGION ACR
ON CIJ.DLVCOUNTRYREGIONID = ACR.COUNTRYREGIONID
LEFT JOIN RDS_SALESTABLE ST
ON CIJ.SALESID = ST.SALESID
LEFT JOIN RDS_SALESLINE SL
ON CIT.INVENTTRANSID = SL.INVENTTRANSID
LEFT JOIN RDS_INVENTTABLE IT
ON CIT.ITEMID = IT.ITEMID
LEFT JOIN RDS_INVENTTABLEMODULE ITMS
ON IT.ITEMID = ITMS.ITEMID
AND ITMS.MODULETYPE = 2
LEFT JOIN RDS_VMRETURNANDREPAIRTABLE RR
ON SL.SALESID = RR.SALESID
AND SL.VMRANUMBER = RR.VMRANUMBER
LEFT JOIN RDS_INVENTDIM ID
ON SL.INVENTDIMID = ID.INVENTDIMID
) ABC
--WHERE SL.SALESTYPE = 4 --and (SELECT ST1.CREATEDDATE FROM RDS_SALESTABLE ST1 WHERE ST1.SALESID = ST.PURCHORDERFORMNUM) IS NULL
LEFT JOIN
(SELECT CIT.InventTransId AS InventTransId, CIT.ItemId AS ItemId, CIT.InvoiceId AS InvoiceId,
ISNULL((SELECT T.TIMEKEY
FROM VMDWANAlytical.DBO.DIM_Time T
WHERE T.FullDate = MAX(SR.ProdDate)),1) AS FK_InventSerialProdDate
FROM RDS_CustInvoiceTrans CIT
LEFT JOIN RDS_InventTrans IVT
ON CIT.InventTransId = IVT.InventTransId AND
CIT.InvoiceId = IVT.InvoiceId AND
CIT.ItemId = IVT.ItemId
LEFT JOIN RDS_INVENTDIM ID
ON IVT.InventDimId = ID.InventDimId
LEFT JOIN RDS_InventSerial SR
ON ID.InventSerialId = SR.InventSerialId AND
IVT.ItemId = SR.ItemId
GROUP BY CIT.InventTransId, CIT.ItemId, CIT.InvoiceId) ZZZ
ON ABC.InventTransId = ZZZ.InventTransId AND
ABC.ItemId = ZZZ.ItemId AND
ABC.InvoiceId = ZZZ.InvoiceId