[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL question

Posted on 2014-08-12
6
Medium Priority
?
285 Views
Last Modified: 2014-08-12
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
0
Comment
Question by:angel7170
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40256330
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40256342
it would be easier to see what you're looking for with sample data and expected output
0
 

Author Comment

by:angel7170
ID: 40256356
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 40256444
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
 

Author Comment

by:angel7170
ID: 40256531
Thank you very much! I am getting an error ORA-00979: not a GROUP BY expression (VT_TEXT)
0
 

Author Comment

by:angel7170
ID: 40256791
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question