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
angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
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
0
sdstuberCommented:
it would be easier to see what you're looking for with sample data and expected output
0
angel7170Author Commented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MikeOM_DBACommented:
Check this out:
  SELECT 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) 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%'
                   OR Vt_Text_Type LIKE 'AI%'
                   OR Vt_Text_Type LIKE 'AS%' )
           GROUP BY Vt_Ser_Num, Vt_Text_Type ) 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
/

Open in new window

PS: Learn to use the "code" tags to format your queries.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
Thank you very much! I am getting an error ORA-00979: not a GROUP BY expression (VT_TEXT)
0
angel7170Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.