angel7170
asked on
SQL question
Hello,
I have an existing query as below for concatenating "Owner name" from different table (PY and VT) and columns based on a logic. I need to add more columns to this query for Owner_Seq_No, Street_Address_Line_1, Street_ Address_Line_2, City and Zip
I am attaching the logic behind each columns in the excel spreadsheet. Can someone please assist? Thank you
Select PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE, MAX(PY.PY_TEXT)|| MAX(VT.VT_text) as OWNER_NM from
(
Select
PY_SER_NUM
,PY_NAM_1
,PY_NAM_2
,PY_NAM_3
,PY_ENT_NUM
,PY_PARTY_TYPE
,PY_ENTITY_TYPE
,PY_NAM_1 || PY_NAM_2 || PY_NAM_3 PY_TEXT
from pY
) PY
left outer join
(Select
VT_SER_NUM
,VT_TEXT_TYPE
,Listagg(VT_TEXT) within group (order by vt_ent_num) over (partition by vt_ser_num) VT_text
from vt
where VT_TEXT_TYPE like 'PN%'
)VT
ON PY.PY_SER_NUM = VT.VT_SER_NUM
AND PY.PY_PARTY_TYPE = trim(substr(VT.VT_TEXT_TYP E,3,2))
AND PY.PY_ENT_NUM = trim(substr(VT_TEXT_TYPE,6 ,6))
GROUP BY PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE
ORDER BY PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE
Logic.xlsx
I have an existing query as below for concatenating "Owner name" from different table (PY and VT) and columns based on a logic. I need to add more columns to this query for Owner_Seq_No, Street_Address_Line_1, Street_ Address_Line_2, City and Zip
I am attaching the logic behind each columns in the excel spreadsheet. Can someone please assist? Thank you
Select PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE, MAX(PY.PY_TEXT)|| MAX(VT.VT_text) as OWNER_NM from
(
Select
PY_SER_NUM
,PY_NAM_1
,PY_NAM_2
,PY_NAM_3
,PY_ENT_NUM
,PY_PARTY_TYPE
,PY_ENTITY_TYPE
,PY_NAM_1 || PY_NAM_2 || PY_NAM_3 PY_TEXT
from pY
) PY
left outer join
(Select
VT_SER_NUM
,VT_TEXT_TYPE
,Listagg(VT_TEXT) within group (order by vt_ent_num) over (partition by vt_ser_num) VT_text
from vt
where VT_TEXT_TYPE like 'PN%'
)VT
ON PY.PY_SER_NUM = VT.VT_SER_NUM
AND PY.PY_PARTY_TYPE = trim(substr(VT.VT_TEXT_TYP
AND PY.PY_ENT_NUM = trim(substr(VT_TEXT_TYPE,6
GROUP BY PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE
ORDER BY PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE
Logic.xlsx
it would be easier to see what you're looking for with sample data and expected output
ASKER
thank you!
My left outer join query is basically filtered to VT_TEXT_TYPE like 'PN% for listagg function. I am not sure how to apply the CASE statement in this case.
Please assist. Thank you
My left outer join query is basically filtered to VT_TEXT_TYPE like 'PN% for listagg function. I am not sure how to apply the CASE statement in this case.
Please assist. Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much! I am getting an error ORA-00979: not a GROUP BY expression (VT_TEXT)
ASKER
ok, I figured what that issue was. Not all 'AS%' are numeric and I had to exclude them from the VT table.
Thank you very much for your assistance!
ELECT Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
, MAX ( Py.Py_Text || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'PN', Vt.Vt_Text, '' ) ) AS Owner_Nm
, MAX ( Py.Py_Addr_1 || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'AI', Vt.Vt_Text, '' ) ) AS Py_Addr_1
, MAX ( Py.Py_Addr_2 || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'AS', Vt.Vt_Text, '' ) ) AS Py_Addr_2
, MAX ( Py_City ) Py_City
, MAX ( Py_Zip_Cd ) Py_Zip_Cd
FROM (SELECT Py_Ser_Num
, Py_Nam_1
, Py_Nam_2
, Py_Nam_3
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
, Py_Nam_1 || Py_Nam_2 || Py_Nam_3 Py_Text
, Py_Addr_1
, Py_Addr_2
, Py_City
, Py_Zip_Cd
FROM pY@DQADMIN_DBRD5
---where py_ser_num = 71420278
) Py
LEFT OUTER JOIN
( SELECT Vt_Ser_Num
, Vt_Text_Type
, LISTAGG ( Vt_Text ) WITHIN GROUP (ORDER BY Vt_Ent_Num) OVER (PARTITION BY Vt_Ser_Num, VT_TEXT_TYPE) Vt_Text
FROM Vt@DQADMIN_DBRD5 VT
WHERE REGEXP_LIKE(SUBSTR ( Vt.Vt_Text_Type, 3, 2 ), '^[[:digit:]]+$')
and ( Vt_Text_Type LIKE 'PN%'
OR Vt_Text_Type LIKE 'AI%'
OR Vt_Text_Type LIKE 'AS%'
)
) Vt
ON Py.Py_Ser_Num = Vt.Vt_Ser_Num
AND Py.Py_Party_Type = TRIM ( SUBSTR ( Vt.Vt_Text_Type, 3, 2 ) )
AND Py.Py_Ent_Num = TRIM ( SUBSTR ( vt.Vt_Text_Type, 6, 6 ) )
GROUP BY Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
ORDER BY Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
Thank you very much for your assistance!
ELECT Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
, MAX ( Py.Py_Text || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'PN', Vt.Vt_Text, '' ) ) AS Owner_Nm
, MAX ( Py.Py_Addr_1 || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'AI', Vt.Vt_Text, '' ) ) AS Py_Addr_1
, MAX ( Py.Py_Addr_2 || DECODE ( SUBSTR ( Vt.Vt_Text_Type, 1, 2 ), 'AS', Vt.Vt_Text, '' ) ) AS Py_Addr_2
, MAX ( Py_City ) Py_City
, MAX ( Py_Zip_Cd ) Py_Zip_Cd
FROM (SELECT Py_Ser_Num
, Py_Nam_1
, Py_Nam_2
, Py_Nam_3
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
, Py_Nam_1 || Py_Nam_2 || Py_Nam_3 Py_Text
, Py_Addr_1
, Py_Addr_2
, Py_City
, Py_Zip_Cd
FROM pY@DQADMIN_DBRD5
---where py_ser_num = 71420278
) Py
LEFT OUTER JOIN
( SELECT Vt_Ser_Num
, Vt_Text_Type
, LISTAGG ( Vt_Text ) WITHIN GROUP (ORDER BY Vt_Ent_Num) OVER (PARTITION BY Vt_Ser_Num, VT_TEXT_TYPE) Vt_Text
FROM Vt@DQADMIN_DBRD5 VT
WHERE REGEXP_LIKE(SUBSTR ( Vt.Vt_Text_Type, 3, 2 ), '^[[:digit:]]+$')
and ( Vt_Text_Type LIKE 'PN%'
OR Vt_Text_Type LIKE 'AI%'
OR Vt_Text_Type LIKE 'AS%'
)
) Vt
ON Py.Py_Ser_Num = Vt.Vt_Ser_Num
AND Py.Py_Party_Type = TRIM ( SUBSTR ( Vt.Vt_Text_Type, 3, 2 ) )
AND Py.Py_Ent_Num = TRIM ( SUBSTR ( vt.Vt_Text_Type, 6, 6 ) )
GROUP BY Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
ORDER BY Py_Ser_Num
, Py_Ent_Num
, Py_Party_Type
, Py_Entity_Type
Example:
Open in new window
:p