I am trying to write a SQL query using Toad which can do some data transformation for a column as per logic.
I am have a attached a document that shows the logic, sample data and the expected results.
I have two tables PY and VT. The name of a owner is saved in 3 columns in PY table (PY_NAM_1, PY_NAM_2 and PY_NAM_3) and the overflow of the text is saved in VY table (VT_TEXT) but in different rows with a number sequence (VT_ENT_NUM). Using these columns from these two tables I need to get a concatenated value named "Text" that uses the logic mentioned in the document.
The query below doesn't do what I expected it to do. Can someone please assist?
Select PY_SER_NUM,PY.PY_TEXT, VT.VT_Nametext from
,PY_NAM_1 || PY_NAM_2 || PY_NAM_3 PY_TEXT
Left outer join
,Listagg(VT_TEXT) within group (order by vt_ent_num) VT_Nametext
where VT_TEXT_TYPE like 'PN%'
Group by VT_SER_NUM, VT_TEXT_TYPE
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,PY_NAM_1,PY