Link to home
Start Free TrialLog in
Avatar of pm-archtect
pm-archtectFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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


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".
Avatar of Sean Stuber
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?
Avatar of Sean Stuber
Sean Stuber

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pm-archtect


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.
that's not exactly what I was asking,
but try the query posted above and see if I guessed correctly anyway

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.
both that spreadsheet and your first one only show 6 rows returned.

what's missing?
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.
>>> 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.
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?
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.

Now your earlier question makes sense :-)
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
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.
This is really cool stuff - thank you for sticking with me on this.