Home » SQL & PL/SQL » SQL & PL/SQL » CASE statement
CASE statement [message #207129] Mon, 04 December 2006 04:19 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I want to append some leading zero's to the rownum. For ex, if the rownum is < 1000, then the rownum should be displayed as '001','002','003',...'999' , similarly if the rownum < 9999 then the rownum should be displayed as '01000','01001','01002', and so on..

I have written a query using DECODE statement but i would like to know how to use the case statement instead of decode.

select ai.invoice_num,
decode(length(rownum),1, lpad(rownum,3,0),2,lpad(rownum,3,0),3,lpad(rownum,4,0),4,lpad(rownum,5,0)) "RowNum"
from ap_invoices ai
where rownum <=1010

The Sample output would look like,

Invoice_Num Rownum
100-980 001
100-765 002
101-987 003
...
...
102-012 999
104-987 01000
109-766 01001
...
...
107-765 09999

Thanks
Safeeq
Re: CASE statement [message #207140 is a reply to message #207129] Mon, 04 December 2006 04:59 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi, found the solution..

select ai.invoice_num,
case length(rownum)
When 1 then lpad(rownum,3,0)
When 2 then Lpad(rownum,3,0)
When 3 then Lpad(rownum,4,0)
When 4 then Lpad(rownum,5,0)
When 5 then Lpad(rownum,6,0)
end "Rownum"
from ap_invoices ai
where rownum <=1010
Previous Topic: Materialized view refreshed through DBlink
Next Topic: outer join 2
Goto Forum:
  


Current Time: Sat Dec 03 05:45:35 CST 2016

Total time taken to generate the page: 0.08179 seconds