[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

How to get values from the main query down 2 levels into a subquery

This is my query

SELECT SUB1.COMP_ID,
       DSH_DATE,
       CPRF_NBR,
       DSH_TICKETS,
       (SELECT TOP 1 FBATRD_RATE
          FROM (SELECT FBATRD_RATE, 1 AS XLEVEL
                  FROM FB_COMPLEX_AVGTAKEUPRATE_DETAIL
                 WHERE COMP_ID = SUB1.COMP_ID
                   AND FBATRS_ID = COMP.FBATRS_ID
                   AND FBATRD_DW = DATEPART(DW, SUB1.DSH_DATE)
                   AND FBATRD_PERF = SUB1.CPRF_NBR
                UNION ALL
                SELECT FBATRD_RATE, 2 AS XLEVEL
                  FROM FB_COMPLEX_AVGTAKEUPRATE_DETAIL
                 WHERE COMP_ID = SUB1.COMP_ID
                   AND FBATRS_ID = 'TEST'
                   AND FBATRD_DW = DATEPART(DW, SUB1.DSH_DATE)
                   AND FBATRD_PERF = SUB1.CPRF_NBR) AS SUB2
         ORDER BY XLEVEL) AS XRATE
  FROM (SELECT COMP_ID,
               DSH_DATE,
               CPRF_NBR,
               SUM(DSH_TICKETS) AS DSH_TICKETS
          FROM TS_DAYSALHEAD
         WHERE COMP_ID = 'METRO'
           AND DSH_DATE >= '01/01/2005'
         GROUP BY COMP_ID,
                  DSH_DATE,
                  CPRF_NBR) AS SUB1
       INNER JOIN COMPLEX COMP ON SUB1.COMP_ID = COMP.COMP_ID
 ORDER BY SUB1.COMP_ID,
          DSH_DATE,
          CPRF_NBR

Problem is that SqlServer doesn't know the field SUB1.COMP_ID (and others) at the second subquery.
In this example this top1 only contains a union of 2 queries, in the real thing it would be of more than 5.
Doesn't matter for the problem.
The reason i'm using this technique is to avoid a user defined function to get my value (wich would be easy) in order to gain speed.
0
HLEBOEUF
Asked:
HLEBOEUF
1 Solution
 
ispalenyCommented:

SELECT SUB1.COMP_ID,
       DSH_DATE,
       CPRF_NBR,
       DSH_TICKETS, COALESCE(SUBX1.FBATRD_RATE,SUBX2.FBATRD_RATE) FBATRD_RATE
  FROM
(
      SELECT COMP_ID,
               DSH_DATE,
               CPRF_NBR,
               SUM(DSH_TICKETS) AS DSH_TICKETS
          FROM TS_DAYSALHEAD
         WHERE COMP_ID = 'METRO'
           AND DSH_DATE >= '01/01/2005'
         GROUP BY COMP_ID,
                  DSH_DATE,
                  CPRF_NBR
) AS SUB1

       INNER JOIN COMPLEX COMP ON SUB1.COMP_ID = COMP.COMP_ID

LEFT JOIN FB_COMPLEX_AVGTAKEUPRATE_DETAIL SUBX1
                    ON SUBX1.COMP_ID = SUB1.COMP_ID
                   AND SUBX1.CFBATRS_ID = COMP.FBATRS_ID
                   AND SUBX1.CFBATRD_DW = DATEPART(DW, SUB1.DSH_DATE)
                   AND SUBX1.CFBATRD_PERF = SUB1.CPRF_NBR

LEFT JOIN FB_COMPLEX_AVGTAKEUPRATE_DETAIL SUBX2
                    ON SUBX2.COMP_ID = SUB1.COMP_ID
                   AND SUBX2.FBATRS_ID = 'TEST'
                   AND SUBX2.FBATRD_DW = DATEPART(DW, SUB1.DSH_DATE)
                   AND SUBX2.FBATRD_PERF = SUB1.CPRF_NBR
 ORDER BY SUB1.COMP_ID,
          DSH_DATE,
          CPRF_NBR
0
 
HLEBOEUFAuthor Commented:
I just had to drop a few C's on the subx1 but .... PERFECT, Thx.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now