You can only reference the column alias in the outer element, so if you don’t recount all the previous values for each column that you need to nest each level, which is a little ugly:
select bt, cno, amt, ths, fivhun, hund, fif, twenty, tens, fives, twos, trunc((amt-(ths*1000)-(fivhun*500)-(hund*100)-(fif*50)-(twenty*20) -(tens*10)-(fives*5)-(twos*2))/1) as ones from ( select bt, cno, amt, ths, fivhun, hund, fif, twenty, tens, fives, trunc((amt-(ths*1000)-(fivhun*500)-(hund*100)-(fif*50)-(twenty*20) -(tens*10)-(fives*5))/2) as twos from ( select bt, cno, amt, ths, fivhun, hund, fif, twenty, tens, trunc((amt-(ths*1000)-(fivhun*500)-(hund*100)-(fif*50)-(twenty*20) -(tens*10))/5) as fives from ( select bt, cno, amt, ths, fivhun, hund, fif, twenty, trunc((amt-(ths*1000)-(fivhun*500)-(hund*100)-(fif*50) -(twenty*20))/10) as tens from ( select bt, cno, amt, ths, fivhun, hund, fif, trunc((amt-(ths*1000)-(fivhun*500)-(hund*100) -(fif*50))/20) as twenty from ( select bt, cno, amt, ths, fivhun, hund, trunc((amt-(ths*1000)-(fivhun*500) -(hund*100))/50) as fif from ( select bt, cno, amt, ths, fivhun, trunc((amt-(ths*1000)-(fivhun*500))/100) as hund from ( select bt, cno, amt, ths, trunc((amt-trunc(ths*1000))/500) as fivhun from ( select bt, cno, amt, trunc(amt/1000) as ths from employer ) ) ) ) ) ) ) );
... which gives something like:
BT CNO AMT THS FIVHUN HUND FIF TWENTY TENS FIVES TWOS ONES --- --- ---------------- ------- ------ ---- --- ------ ---- ----- ---- ---- 1 2 123,456,789 123456 1 2 1 1 1 1 2 0 3 4 87,654,321 87654 0 3 0 1 0 0 0 1 5 6 1,234,567 1234 1 0 1 0 1 1 1 0
Not much better, but a recursive version, mainly for my own entertainment:
with t as ( select bt, cno, amt, x, case x when 1 then 1000 when 2 then 500 when 3 then 100 when 4 then 50 when 5 then 20 when 6 then 10 when 7 then 5 when 8 then 2 when 9 then 1 end as bill from employer cross join (select level as x from dual connect by level < 10) ), r (bt, cno, amt, x, y, running) as ( select t.bt, t.cno, t.amt, 0 as x, 0 as y, 0 as running from t where tx = 1 -- could be any x, just want one row per bt/cno union all select t.bt, t.cno, t.amt, tx, trunc((t.amt - r.running)/t.bill) as y, r.running + (t.bill * trunc((t.amt - r.running)/t.bill)) as running from t join r on r.bt = t.bt and r.cno = t.cno and rx = tx - 1 ) select bt, cno, amt, max(case when x = 1 then y else 0 end) as ths, max(case when x = 2 then y else 0 end) as fivhun, max(case when x = 3 then y else 0 end) as hund, max(case when x = 4 then y else 0 end) as fif, max(case when x = 5 then y else 0 end) as twenty, max(case when x = 6 then y else 0 end) as tens, max(case when x = 7 then y else 0 end) as fives, max(case when x = 8 then y else 0 end) as twos, max(case when x = 9 then y else 0 end) as ones from r group by bt, cno, amt order by bt, cno;
The generic table expression t (CTE) simply cross-combines the real data with a dummy table that generates numbers 1-9 and assigns vector denomination values (assuming Robert Co is correct) for each level for later use.
r CTE is recursive, which, I think, only works with 11gR2. The first part of the union sets the "total" of what is still being formed, which is zero, since this is the first step in recursion. The remaining columns are not used except for a dummy zero for x , which is used for a recursive join. The second part of the union subtracts the current total from the previous level with amt at that level, finds the number of whole bills of this denomination — what we want to actually report — and recounts the current amount to include this figure. Each time around the cycle, the size of the banknote decreases, and the total amount increases.
Thus, it ends with many lines, the amount of each account as another line; which actually have to be rotated to show the values under the corresponding columns. This is what the max() bit and group by at the end.
For my dummy data, it gives the same result:
BT CNO AMT THS FIVHUN HUND FIF TWENTY TENS FIVES TWOS ONES --- --- ---------------- ------- ------ ---- --- ------ ---- ----- ---- ---- 1 2 123,456,789 123456 1 2 1 1 1 1 2 0 3 4 87,654,321 87654 0 3 0 1 0 0 0 1 5 6 1,234,567 1234 1 0 1 0 1 1 1 0
By the way, I initially tried to simplify this with mod() (as AndriyM suggests), but you cannot calculate each value independently:
select bt, cno, amt, floor( amt/1000) as ths, floor(mod(amt, 1000)/ 500) as fivhun, floor(mod(amt, 500)/ 100) as hund, floor(mod(amt, 100)/ 50) as fif, floor(mod(amt, 50)/ 20) as twenty, floor(mod(amt, 20)/ 10) as tens, floor(mod(amt, 10)/ 5) as fives, floor(mod(amt, 5)/ 2) as twos, floor(mod(amt, 2)/ 1) as ones from employer order by bt, cno; BT CNO AMT THS FIVHUN HUND FIF TWENTY TENS FIVES TWOS ONES
Most values are the same, but tens are all 0 , and ones are all 1 . The latter is easily explained, although it is rather a question of why they should not be 1 . If the value of fives is 1 , then the remaining amount for the separation becomes even, so ones should be 0 . Similarly, tens does not account for fif . Thus, there are dependencies between values that such a simple request does not process. You can customize the problem columns to take this into account, of course, with the risk of introducing subtle errors.