QUERY REPORT

[nguyenb9]

How do I write a query report on a table that provide the output as follow:

Mytable:

Account_id LSDM
43730748 T1FAC
43730748 T1FAC
43730748 UNEP
43730748 T1FAC
43730748 UNEP
43730748 UENP
43730748 RS
43730748 RS
43730748 UNEP
43730748 T1FAC

Output should look like this:
43730748 UNEP/ T1FAC/ RS

THANK YOU

 

[lava1178]

Hi nguyenb9 ,
Please find the Query for the output desired:

SELECT distinct m1.account_id ,lsdm1||'/'||lsdm2||'/'||lsdm3 lsdm
from (select distinct lsdm lsdm1,account_id a1
from mytable where lsdm='UNEP')
, (SELECT distinct lsdm lsdm2,account_id a2
from mytable where lsdm='RS')
, (SELECT distinct lsdm lsdm3,account_id a3
from mytable where lsdm='T1FAC')
,mytable m1
where m1.account_id=a1
and m1.account_id=a2
and m1.account_id=a3

Hope this helps.

-----
Rgds,
Lavanya

 

[nguyenb9]

It giving me an error!

This is the script:

SELECT distinct m1.account_id ,lsdm1||'/'||lsdm2||'/'||lsdm3 lsdm
from (select distinct lsdm lsdm1, account_id a1
from tblbillxtract where lsdm='UNEP')
,(SELECT distinct lsdm lsdm2, account_id a2
from tblbillxtract where lsdm='RS')
,(SELECT distinct lsdm lsdm3, account_id a3
from tblbillxtract where lsdm='T1FAC')
,tblbillxtract m1
where m1.account_id = 'a1'
and m1.account_id = 'a2'
and m1.account_id = 'a3'
/

This is an error is giving me:

,(SELECT distinct lsdm lsdm3, account_id a3
*
ERROR at line 6:
ORA-00904: invalid column name

Thanks, for the help

 

[lava1178]

Hi ,
Please check the Where condition :
where m1.account_id = 'a1'
and m1.account_id = 'a2'
and m1.account_id = 'a3

a1,a2,a3 are account id and are being used as an alias name to the account_id from the table tblbillxtract . By mentioning 'a1' it becomes a character string.
i.e where condition m1.account_id ='a1' i s similar to saying m1.account_id ='HELLO'.

Following is the output that I receive when I run the query mentioned byme above :
ACCOUNT_ID LSDM
--------------- -----------------------
43730748 UNEP/RS/T1FAC


In case this does not help.
Though i donot see any problem in the either the name of the column or the table name, can you please recheck the script written. do a desc of the table and check.

Hope this helps.

 

[maheshtr03]

SELECT DISTINCT A.ABC, A.XYZ||'/'||B.XYZ||'/'||C.XYZ BABU FROM HELLO A,HELLO B, HELLO C WHERE
A.ABC = B.ABC AND B.ABC= C.ABC AND C.ABC = A.ABC AND A.XYZ = 'RS' AND B.XYZ = 'UNEP' AND C.XYZ = 'T1FAC'

UNION

SELECT DISTINCT A.ABC, A.XYZ||'/'||B.XYZ BABU FROM HELLO A,HELLO B WHERE
A.ABC = B.ABC AND B.XYZ = 'UNEP' AND a.XYZ = 'T1FAC' AND A.ABC NOT IN (SELECT DISTINCT A.ABC FROM HELLO A,HELLO B, HELLO C WHERE
A.ABC = B.ABC AND B.ABC= C.ABC AND C.ABC = A.ABC AND A.XYZ = 'RS' AND B.XYZ = 'UNEP' AND C.XYZ = 'T1FAC')
UNION
SELECT DISTINCT A.ABC, A.XYZ BABU FROM HELLO A,HELLO B WHERE
a.XYZ = 'T1FAC' AND A.ABC NOT IN (SELECT DISTINCT A.ABc FROM HELLO A,HELLO B WHERE
A.ABC = B.ABC AND B.XYZ = 'UNEP' AND a.XYZ = 'T1FAC')

IN THIS MANNER TRY ALL THE COMBINATIONS

 

[maheshtr03]

SELECT DISTINCT A.ABC, A.XYZ||'/'||B.XYZ||'/'||C.XYZ BABU FROM HELLO A,HELLO B, HELLO C WHERE
A.ABC = B.ABC AND B.ABC= C.ABC AND C.ABC = A.ABC AND A.XYZ = 'RS' AND B.XYZ = 'UNEP' AND C.XYZ = 'T1FAC'

UNION

SELECT DISTINCT A.ABC, A.XYZ||'/'||B.XYZ BABU FROM HELLO A,HELLO B WHERE
A.ABC = B.ABC AND B.XYZ = 'UNEP' AND a.XYZ = 'T1FAC' AND A.ABC NOT IN (SELECT DISTINCT A.ABC FROM HELLO A,HELLO B, HELLO C WHERE
A.ABC = B.ABC AND B.ABC= C.ABC AND C.ABC = A.ABC AND A.XYZ = 'RS' AND B.XYZ = 'UNEP' AND C.XYZ = 'T1FAC')
UNION
SELECT DISTINCT A.ABC, A.XYZ BABU FROM HELLO A,HELLO B WHERE
a.XYZ = 'T1FAC' AND A.ABC NOT IN (SELECT DISTINCT A.ABc FROM HELLO A,HELLO B WHERE
A.ABC = B.ABC AND B.XYZ = 'UNEP' AND a.XYZ = 'T1FAC')

IN THIS MANNER TRY ALL THE COMBINATIONS
and even a first select statement works