Nested SELECT statements

Toad Data Point
1

Hi there, I’m a newbie when it comes to SQL so I hop you guys don’t mind a simple question.

I have 3 quearies and a calculation that I’d like to run all at once, instead of having to do them all individually. The problem is, the queries all go against the same dataset with different parameters for each SELECT.

Here’s the SQL I wrote:

–Estimated Sales calculation is
–(QA Beginning Balance + Shipment $'s) - QA Ending Balance
–Based on 4-4-5 calendar and making sure to go back
–at least 5 days to get the actual begin and end dates by location

–region QA Beginning Quantity
SELECT CR.IDENTITY, TR.REL_NAME, SUM(KPIF.PA_QA_VALUE) AS QA_BEG
FROM KPI_Facts KPIF, TRADING_RELATIONSHIPS TR, CREDENTIALS CR, ITEMS IT
WHERE KPIF.ITEM_KEY = IT.ITEM_ID
AND KPIF.RELATIONSHIP_KEY = TR.RELATIONSHIP_ID
AND TR.FROM_PARTNER = CR.PARTNER_ID
AND IDENTITY IN (‘0031’, ‘0045’, ‘0052’, ‘0059’, ‘0087’, ‘0270’)
AND KPIF.ITEM_KEY=‘1221107’
AND kpif.TIME_KEY=‘30-DEC-2007’
GROUP BY CR.IDENTITY, TR.REL_NAME
–endregion

–region QA Ending Quantity
SELECT CR.IDENTITY, TR.REL_NAME, SUM(KPIF.PA_QA_VALUE) AS QA_END
FROM KPI_Facts KPIF, TRADING_RELATIONSHIPS TR, CREDENTIALS CR, ITEMS IT
WHERE KPIF.ITEM_KEY = IT.ITEM_ID
AND KPIF.RELATIONSHIP_KEY = TR.RELATIONSHIP_ID
AND TR.FROM_PARTNER = CR.PARTNER_ID
AND IDENTITY IN (‘0031’, ‘0045’, ‘0052’, ‘0059’, ‘0087’, ‘0270’)
AND KPIF.ITEM_KEY=‘1221107’
AND kpif.TIME_KEY=‘27-JAN-2008’
GROUP BY CR.IDENTITY, TR.REL_NAME
–endregion

–region Shipment Dollars
SELECT CR.IDENTITY, TR.REL_NAME, SUM(KPIF.I_ORDER_VALUE) AS _SHPT
FROM KPI_Facts KPIF, TRADING_RELATIONSHIPS TR, CREDENTIALS CR, ITEMS IT
WHERE KPIF.ITEM_KEY = IT.ITEM_ID
AND KPIF.RELATIONSHIP_KEY = TR.RELATIONSHIP_ID
AND TR.FROM_PARTNER = CR.PARTNER_ID
AND IDENTITY IN (‘0031’, ‘0045’, ‘0052’, ‘0059’, ‘0087’, ‘0270’)
AND KPIF.ITEM_KEY=‘1221107’
AND kpif.TIME_KEY BETWEEN ‘30-DEC-2007’ AND ‘27-JAN-2008’
GROUP BY CR.IDENTITY, TR.REL_NAME
–endregion

–Then I need to calculate: (QA_BEG + SHPT) - QA_END as ESTIMATED_SALES

Is there a way to do this in one step? I’ve heard of NESTED SELECT statements, but am not sure if that’s what I need.

Any help would be greatly appreciated.

I’m also attaching the SQL if that would help at all.

Thanks!
Mike Smith

Estimated Sales Queries.sql (1.72 KB)

2

You could do it with nested selects (or sub-selects), but I personally would use a CASE statement (see your database SQL reference manual for more details.) Something like this:

SELECT CR.IDENTITY, TR.REL_NAME,
sum(case kpif.time_key when ‘30-DEC-2007’ then kpif.pa_qa_value else 0 end)
as qa_beg,
sum(case kpif.time_key when ‘27-JAN-2008’ then kpif.pa_qa_value else 0 end)
as qa_end,
sum(kpif.i_order_value) as shpt,
sum(case kpif.time_key when ‘30-DEC-2007’ then kpif.pa_qa_value else 0 end)
+
sum(kpif.i_order_value)
-
sum(case kpif.time_key when ‘27-JAN-2008’ then kpif.pa_qa_value else 0 end)
as estimated_sales
FROM KPI_Facts KPIF, TRADING_RELATIONSHIPS TR, CREDENTIALS CR, ITEMS IT
WHERE KPIF.ITEM_KEY = IT.ITEM_ID
AND KPIF.RE LAT IONSHIP_KEY = TR.RELATIONSHIP_ID
AND TR.FROM_PARTNER = CR.PARTNER_ID
AND IDENTITY IN (‘0031’, ‘0045’, ‘0052’, ‘0059’, ‘0087’, ‘0270’)
AND KPIF.ITEM_KEY=‘1221107’
AND kpif.TIME_KEY BETWEEN ‘30-DEC-2007’ AND ‘27-JAN-2008’
GROUP BY CR.IDENTITY, TR.REL_NAME ;

3

Thank you so much.

That worked like a charm!

Mike

4

Thanks!

5

I’m glad that worked for you. What database system are you using?

6

I’m using Oracle 9i.

7

Thank you for the information, we always like to know what RDBMS our customers are using.