SQL question

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
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.

slightwv (䄆 Netminder) Commented:
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
angel7170Author Commented:
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
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

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 now.

awking00Commented:
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
angel7170Author Commented:
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
awking00Commented:
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

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 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
angel7170Author Commented:
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
slightwv (䄆 Netminder) Commented:
I would think it should be a new question since it is new requirements.
0
angel7170Author Commented:
ok, I will open a new one.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.