Oracle SQL : Live Example : LISTAGG,DECODE,CASE,UNION,GROUP, HAVING

 Oracle SQL : Live Example

----Converting multiple columns into single using  LISTAGG Function--

select distinct aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

group by aia.invoice_num;

---------------- Group by having clause-----------------------------------------------

select 'A' BLOCK ,aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

group by aia.invoice_num

having count(aila.line_number) < 3

---------------------Union ALL -------------

select 'A' BLOCK ,

aia.invoice_num--,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

and aia.invoice_num ='ERS-36577'

--and rownum <5

--group by aia.invoice_num;

--having count(aila.line_number) < 3

Union all

select 'B' BLOCK ,

aia.invoice_num--LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

and aia.invoice_num ='ERS-36577'

--group by aia.invoice_num

--having count(aila.line_number) > 10;

-----------------------Decode , Case, ----------------------------------------

select gcck.concatenated_segments,

--gcck.SEGMENT1||'-'||gcck.SEGMENT2||'-'||gcck.SEGMENT3||'-'||gcck.SEGMENT4|| '-'||gcck.SEGMENT5||'-'||gcck.SEGMENT6||'-'||gcck.SEGMENT7||'-'||gcck.SEGMENT8||'-'||gcck.SEGMENT9 "ACCOUNT",

 gcck.SEGMENT3,

 case  when gcck.SEGMENT3 in  ( 5250,1110,1590,4150)then

 'ABC'

 ELSE

 NULL

 end SOB_ITP,

 decode(gcck.SEGMENT3,5250,'abc',1110,'xyz',null)

 from gl_code_combinations_kfv gcck;

 where gcck.SEGMENT3 != '5250';

 ---------Nested Case Statement---------

 select gcck.concatenated_segments, gcck.SEGMENT3,

 case  when gcck.SEGMENT2 in  ( '520','170','510')then

   case when gcck.SEGMENT3 in  ( '5250','1590')then

   'NESTED CASE'

   else

   'NESTED ELSE'

   end

 ELSE

 NULL

 end SOB_ITP

 from gl_code_combinations_kfv gcck;

 where gcck.SEGMENT3 != '5250';