Home » SQL & PL/SQL » SQL & PL/SQL » narration (9i)
narration [message #294945] Sun, 20 January 2008 23:58 Go to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear all,

i have table sale as :-
  BILL_NO   ITEM_CD
--------- ---------
        1      3004
        2      3004
        3      3004
        4      3005
        5      3004
        6      3005
        7      3005
        8      3005

may i have narration for it like :-

Item 3004 sold through bill no/no.s 1,2,3 and 5.
Item 3005 sold through bill no/no.s 4,6,7 and 8.

thanks,
mshrkshl
Re: narration [message #294946 is a reply to message #294945] Mon, 21 January 2008 00:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Please refer pivoting technique

Thumbs Up
Rajuvan
Re: narration [message #295276 is a reply to message #294945] Mon, 21 January 2008 23:48 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SELECT ('Item  ' || item_cd ||  '  sold through bill no/no.s  ' || MAX(SUBSTR(bill_no,2))) narration
FROM ( 
       SELECT item_cd
     , SYS_CONNECT_BY_PATH(bill_no,',') bill_no
FROM ( 
       SELECT row_number() OVER 
( PARTITION BY item_cd ORDER BY bill_no) seqno
            ,bill_no
            , item_cd
       FROM   sale
       ORDER BY bill_no
    )
CONNECT BY item_cd = PRIOR item_cd
       AND seqno   = PRIOR seqno + 1
START WITH seqno = 1
     )
GROUP BY item_cd
/


NARRATION
-----------------------------------------------
Item  3004  sold through bill no/no.s  1,2,3,5
Item  3005  sold through bill no/no.s  4,6,7,8


Now I want to put 'and' before last bill_no 5 and 8 respectively to match it 100% with my requirement.Any help please.
Re: narration [message #295283 is a reply to message #295276] Tue, 22 January 2008 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSTR can give the index of last ','
REPLACE can replace any string

Regards
Michel
Re: narration [message #295367 is a reply to message #294945] Tue, 22 January 2008 03:45 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
code
select (substr(nar,0,instr(nar,',',-1)-1) || replace(nar,substr(nar,0,instr(nar,',',-1)),'and')||'.') narration
from (
SELECT ('Item  ' || item_cd ||  '  sold through bill no/no.s  ' || MAX(SUBSTR(bill_no,2))) nar
FROM ( 
       SELECT item_cd
     , SYS_CONNECT_BY_PATH(bill_no,',') bill_no
FROM ( 
       SELECT row_number() OVER 
( PARTITION BY item_cd ORDER BY bill_no) seqno
            ,bill_no
            , item_cd
       FROM   sale
       ORDER BY bill_no
    )
CONNECT BY item_cd = PRIOR item_cd
       AND seqno   = PRIOR seqno + 1
START WITH seqno = 1
     )
GROUP BY item_cd)
/

output
NARRATION
-------------------------------------------------
Item  3004  sold through bill no/no.s  1,2,3and5.
Item  3005  sold through bill no/no.s  4,6,7and8.

thanks!
Previous Topic: differentiate item units (merged)
Next Topic: select query
Goto Forum:
  


Current Time: Sat Dec 10 22:45:53 CST 2016

Total time taken to generate the page: 0.04888 seconds