An example of a Nested SQL statment

Seven of Nine, from the Star Trek Series, Voyager

 Article by Kirk Harrington

In followup to the recent SQL discussion I posted, here's a Nested SQL example that actually works but thats sure to confound you!  Can you guess what its doing?

select distinct j.index_nbr_b, j.index_b_stardt, j.index_b_amt, j.index_nbr_h as cube_nbr_h, j.index_h_stardt as cube_h_stardt, j.index_h_code as cube_trcd, j.index_h_amt as ic_tran_amt, j.hflag_stardt, j.Attrib, j.index_nbr_h as seven_of_nine_nbr, j.seven_of_nine_stardt, SUM(latinum_bars) as sum_latinum_bars, SUM(closeout dollars) as sum_credit_chips, SUM(borg_dollars) as sum_borg_dollars from

 (

select g.*,'latinum_bars'=case when seven_trcd='LB' and seven_attrib=1 then rif_amt else 0 end,

'credit_chips'=case when seven_trcd='CC' and seven_attrib=1 then rif_amt else 0 end,

'borg_dollars'=case when seven_trcd='BD' and seven_attrib=1 then rif_amt else 0 end

from

(

(select *, 'seven_attrib'=case when pmt_dt between index_h_stardt and seven_of_nine_stardt then 1 else 0 end from

(

((select v.*, index_b_stardt+7 as seven_of_nine_stardt from

(select z.*, 'Attrib'=case when z.index_h_stardt between z.hflag_stardt and z.index_b_stardt and index_b_amt=index_h_amt then 1 else 0 end

from

(select *, index_b_stardt - 30 as hflag_stardt from

((select index_nbr_b,index_b_stardt,index_b_amt

from watb.borg_queen where index_nbr_b between 7777777 and 9999999 and index_b_stardt between 7411.4 and 9521.6) a

FULL OUTER JOIN

(select index_nbr_h, index_h_stardt, index_h_code, index_h_amt from watb.seven_of_nine where index_nbr_h between 7777777 and 9999999 and substring(index_h_code,1,3)='IC@' and index_h_stardt between 6411.4 and 9521.6) b

on a.index_nbr_b=b.index_nbr_h))x) v where v.Attrib=1)) o

LEFT OUTER JOIN

(select index_nbr_h as index_nbr_h, SUBSTRING(index_h_code,1,2) as seven_trcd, index_h_amt as rif_amt, index_h_stardt as pmt_dt from watb.seven_of_nine where substring(index_h_code,1,2) in ('LB','CC', 'BD') and

index_h_stardt between 6411.4 and 9521.6 and index_h_amt>0 and index_nbr_h between 7777777 and 9999999) m

on 0.index_nbr_b=m.index_nbr_h)

)) g

) j

GROUP BY index_nbr_b, index_b_stardt, index_b_amt. index_nbr_h, index_h_stardt, index_h_code, index_h_amt, hflag_stardt, Attrib, index_nbr_h, seven_of_nine_stardt

ORDER BY index_nbr_b, index_b_stardt

-----------------------------------------------

An Effective Analyst Tip
When a business line asks you for A,B, and C, also provide them D,E, and F.  This means that you provide them more than what they're asking for so they see you are engaged in wanting to help them succeed.