Avatar of pm-archtect
pm-archtect
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Oracle 11g - Join two tables based on complex query that splits values

Hi

I have two queries, one quite complex (courtesy of expert exchange user sdstuber) that splits a value in a column in to its constituent parts and then finds all the matching rows and pivots the data and another that is a simple select, from where.

Each of these queries and their data are in the attached example spreadsheet under tabs "Part One" and "Part Two".  You will in Part One how the simple column data is transfomed into a new set of rows.

What I need to do is join the resulting sets of data together and the example is in the third tab "Example Join".
Split-Example.xlsx
Oracle Database

Avatar of undefined
Last Comment
pm-archtect

8/22/2022 - Mon
Sean Stuber

Can a row in your event_parameters_view match more than unitno in the consistid?

what would you expect to see if that happened?
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pm-archtect

ASKER
Not sure if I understand your question correctly - are you asking:

Can the values in other columns of the event_parameters_view match the extracted element from the consistid column e.g:

If you have a consistid of 101-90-40-202 and you were referencing element number 2 which is the value "90", could that value match another column?  The answer would be yes, that can happen.  The fault code column holds numbers that are the same as the unitids.  This would result in mismatched rows.

I  hope that I've answered your question correctly.
Sean Stuber

that's not exactly what I was asking,
but try the query posted above and see if I guessed correctly anyway
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
pm-archtect

ASKER
Hi

A fantastic 1st try.  The join is the wrong way round so I only saw 6 records (one for each consistid).  

The event_parameters_view has 159 rows so I would have expected to see all those rows along with the consistid row where the unitid in the consistid string matches the event_parameter_view.unitid value.

Attached are the results for you to see.
Results.xls
Sean Stuber

both that spreadsheet and your first one only show 6 rows returned.

what's missing?
pm-archtect

ASKER
A decent example me-thinks - sorry.

I've updated the attached and the third tab now correctly reflects the join showing all the records from the event_parameters_view connected to the results from the consistid result set.

You will see that the rows are joined where the extracted element from the consistid matches the event_parameter_view.unit. for example:

In the very last row, the consistid 225-777-84-297 matches the unitid of 777.

We have to match the extracted string from the consistid to event_parameter_view.unitid (and not the other way around because if you have a unitid of 12, that could compare and match any part of a consistid for example: 112-212-312-412).

Thank you for your help so far and apologies once again for the poor example.
Split-Example.xlsx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

>>> because if you have a unitid of 12, that could compare and match any part

no it won't, that's what the '-'  are for in the INSTR call.   they ensure that every element of consistid is enclosed with dashes (including the first and last)  and that we search for those with the number itself.


I'm still not seeing the problem with the results though,  even when I add more data, the query picks it up.
Sean Stuber

the ordering of the results is different, I'm still ordering by consistid from the original query.

What are you ordering by in your results?
pm-archtect

ASKER
The ordering is was not of importance to me at this stage as the view will be used by another person to create a report in Crystal.  They will filter the view further by only returning records to a user for a specific unitid or, a specific consistid.  

If the ordering is important at this stage, then it would be by UNITID, EVENTDATE.

Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
pm-archtect

ASKER
Now your earlier question makes sense :-)
Sean Stuber

so, is it working for you now?

if not,  pick one row that should appear in the results but doesn't, or does but has the wrong results.

narrowing down the problem to a specific test case should help
pm-archtect

ASKER
Yes it work now - thank you.  I had some duff data that I needed to tidy up.

This is really cool stuff.  Will take me some time to wander through and figure out what it does.  THANK YOU.

If you have time, would you take a peek at my other open question please, no one appears interested so I'm wondering if what I'm asking is not possible.

https://www.experts-exchange.com/Database/Oracle/Q_27730373.html
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pm-archtect

ASKER
This is really cool stuff - thank you for sticking with me on this.