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

x
?
Solved

SQL question

Posted on 2014-08-11
10
Medium Priority
?
482 Views
Last Modified: 2014-08-12
Hello,

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
(
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
Left outer join
(Select
        VT_SER_NUM
        ,VT_TEXT_TYPE
        ,Listagg(VT_TEXT) within group (order by vt_ent_num) VT_Nametext
        from vt
 where VT_TEXT_TYPE like 'PN%'
  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,PY_NAM_1,PY_NAM_2,PY_NAM_3
) s
Logic.docx
0
Comment
Question by:angel7170
  • 5
  • 3
  • 2
10 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40254557
You need to clarify the requirements.

The code you posted has:
PY.PY_PARTY_TYPE = trim(substr(VT.VT_TEXT_TYPE,3,2)

For word doc has:
trim(SUBSTR(VT_TEXT_TYPE,3,4)) = PY_PARTY_TYPE

Based on what the SQL you posted has, it returns the correct result  vt_text_type from char 3 for 2 characters is 30 and text5 is 30.

Please clarify.
0
 

Author Comment

by:angel7170
ID: 40255535
Sorry for the confusion!

The SQL code I posted is correct. I need to modify the word doc.

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))
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40255578
OK, then what isn't working on your select?

Based on what I understood, text5 is what should be returned not 'text1text2text3' as desired.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 40255742
Agreeing with slightwv, the only row from py that matches the criteria of py_party_type = substr(vt_text_type,3,2) is the one with test5 for py_nam_1. You would have to use <> to arrive at your expected results. Is that what you meant? Also, I'm not clear on the purpose of having py_ent_num = substr(vt_text_type, 6,6).  [ I suspect you mean substr(vt_text_type, 6,1) ]
The following should produce your expected results:
select py_ser_num, max(py_text)||max(vt_text) text from
(select vt_ser_num,
 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%') x
left outer join
(select py_ser_num, py_nam_1||py_nam_2||py_nam_3 py_text
 from py where not exists
 (select 1 from vt
  where py_party_type = substr(vt_text_type,3,2)
  and vt_text_type like 'PN%')) y
on x.vt_ser_num = y.py_ser_num
group by py_ser_num
order by py_ser_num;

If you truly meant to match the py_party_type with the third and fourth characters from the vt_text_type, change the not exists to exists.
0
 

Author Comment

by:angel7170
ID: 40255784
Sorry! Expected results as per Logic it should be text5 (When I wrote the document I missed it)

 I meant to match the PY_party_type with the third and fourth characters from the vt_text_type. Also, it should match PY.PY_ENT_NUM = trim(substr(VT_TEXT_TYPE,6,6)).

Thank you both. I am running the script now and let you know how it turns out to be.

thanks again!!
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 40255831
Modified to meet your conditions. I still don't understand why trim(vt_text_type,6,6)).

select py_ser_num, max(py_text)||max(vt_text) text from
(select vt_ser_num,
 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%') x
left outer join
(select py_ent_num, py_ser_num, py_nam_1||py_nam_2||py_nam_3 py_text
 from py where exists
 (select 1 from vt
  where py_party_type = substr(vt_text_type,3,2)
  and py_ent_num = trim(substr(vt_text_type,6,6))
  and vt_text_type like 'PN%')) y
on x.vt_ser_num = y.py_ser_num
group by py_ser_num
order by py_ser_num;
0
 

Author Comment

by:angel7170
ID: 40256247
Thank you very much!

I was able to take your SQL and modified it little bit to group by for a given PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE as per logic.


Select PY_SER_NUM,PY_ENT_NUM, PY_PARTY_TYPE, PY_ENTITY_TYPE, MAX(PY.PY_TEXT), MAX(VT.VT_text)  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
0
 

Author Comment

by:angel7170
ID: 40256254
Question: I have additional columns I need to add like Address 1 , Address 2 , Street etc with it's own logic. Should I open a new discussion? Please let me know
Thank you
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40256260
I would think it should be a new question since it is new requirements.
0
 

Author Comment

by:angel7170
ID: 40256280
ok, I will open a new one.

Thank you for all your help!
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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 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