If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

SubQueries

Started by Kory, 30 Aug 2006 09:47:36 AM

Previous topic - Next topic

Kory

How can I include the subquery in this code in Report Studio. The join works fine and I get the correct results only if I leave out the subquery, but that would make sense because I do not know how to include the subquery correctly. This is what I have done so far that works. I drag a query subject to the work area,
then the join, then two new query subjects. I add some fields to query2, some to query3, I select an outer join, then I add the fields I want to display to query1. Where and how to I incorporate the subquery? Here is the SQL code:

SELECT   IC.LocationId       AS ICLoc, 
   IC.SkuID       AS ICSku,
   S.[Description 1]    AS SDesc,
   S.Size          AS SSIze,
   SUM(IC.Cases)       AS ICCases,
   SUM(IC.Bottles)    AS ICBottles,
    ???ISNULL((SELECT TOP 1 LocationID FROM Locations
      WHERE SKU = IC.SKUID AND LocationTypeID IN (1,2)),'')
            AS BtlLoc,
    ???ISNULL((SELECT TOP 1 LocationID FROM Locations
      WHERE SKU = IC.SKUID AND LocationTypeID IN (3,4,5,6,7)),'')
             AS CasLoc
FROM   InventoryCatalog IC
   LEFT OUTER JOIN [WCS-UNITED].dbo.Sku S ON IC.SkuId = S.Sku
GROUP BY IC.Locationid, IC.SkuId, S.[Description 1], S.Size
ORDER BY ic.locationid, ic.skuid