Link to home
Start Free TrialLog in
Avatar of angel7170
angel7170Flag for United States of America

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_TYPE,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
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

And what is preventing you from using CASE statement(s)?
Example:
SELECT . . .
     , CASE WHEN VT_TEXT_TYPE = 'AI'
            THEN PY_ADDR_1||VT_TEXT
       END AS STREET_ADDRESS_LINE_1_TX
     , CASE WHEN VT_TEXT_TYPE = 'AS'
            THEN PY_ADDR_2||VT_TEXT
       END AS STREET_ADDRESS_LINE_2_TX
     , . . .  Etc

Open in new window

:p
Avatar of Sean Stuber
Sean Stuber

it would be easier to see what you're looking for with sample data and expected output
Avatar of angel7170

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
ASKER CERTIFIED SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much! I am getting an error ORA-00979: not a GROUP BY expression (VT_TEXT)
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