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

pm-archtect
pm-archtect used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

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

what would you expect to see if that happened?
Most Valuable Expert 2011
Top Expert 2012
Commented:
how about this?
ee.txt

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
that's not exactly what I was asking,
but try the query posted above and see if I guessed correctly anyway

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2012

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

what's missing?

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> 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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

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

Author

Commented:
Now your earlier question makes sense :-)
Most Valuable Expert 2011
Top Expert 2012

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

Author

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

http://www.experts-exchange.com/Database/Oracle/Q_27730373.html

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial