VERY WEIRD T-SQL issue - query works but not if I encapsulate it as a sub-query to just select * from ()

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.D开发者_C百科BO.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

---

I think you need to add an alias to the subquery so you'd have

SELECT * FROM ( MY QUERY HERE) as MyQuery