Again something i struggled with myself and found no help with online.

We will need to use a clever little function in oracles bag of tricks, the hierarchical query.

Hierarchical queries allow you to create a query on a table where the query will link back to the same table any number of times. I wont go too in depth about these themselves as their are some quite good webpages explaining them that are easily found on google.

How do these queries allow you to change a delimited column into multiple rows? Well we are miss using the function really, it is not truly intended for this use but the function lends it self very nicely to this situation.

There are plenty of examples on oracles forums (if you can pick through them) of using this type of query to great effect on character delimited strings. On implementing against actual data and adapting to a fixed length rather than delimited i found a lot of problems. Effectively once we took the examples beyond one concatenated string. The results became geometric, growing by a factor of the original row number with every iteration. So an initial sample of 3 rows with delimited columns would give 9 rows after just 3 iterations.

The solution was quite simple, as we have fixed length data (in my case 366 sets of 2 characters,) instead of using the actual data in the hierarchical query we can simply use the hierarchical query to create a table to which we link our data. this table will hold sufficient information to allow a simple select to filter the data to what we need.

For example.

select
     x.id,
       x.startDate,
       x.startDate + (p.position - 1) as att_date,
       substr(x.delimedString, (p.position * 2) - 1, 2) as val,
from   table1 x,
       (select rownum as position
        from   dual
        connect by level < 366) p
where p.position <= (length(x.delimedString) / 2)

As you can see we have made every row in table1 into 365 rows. These rows will contain all of the information plus a rownum value. We use this rownum value in the select clause, to make each val value different. So the row with position 1 will take the first 2 charecters. The row with position as 3 will take the 5th and 6th numbers.

I've been working on a particularly poorly designed third party system. In this system child records have a start date and an end date.

The system has a flaw in which while allowing one to many links from parent to child records (needed for history), the business logic does not impose the required behaviour. For instance there can be more than one open (null end date) child record when only one should be permitted and all child records can be closed (non null end date) when a child record should only be closed on the creation of a new open record.

The reports that I have been tasked with creating therefore required a new filter type on these child records.

The filter was needed to return only the open child record with the latest start date or if there is no open child records the record with the latest end date.

To do this I split the situation. Firstly we want only the most recent open records. This is fairly simple.

(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate

Next we want the most recent end date again fairly simple using the method above.

 (Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate

We can than make a union of these that will almost solve our problem.

Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x

union
select y.* from (

(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y

I say almost as their will still be 2 records where open and ended child records are present. So we must apply the conditional filter I detailed in my previous post. The script becoming.

Select  Cond.* from

((Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate

We can than make a union of these that will almost solve our problem.

Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x

union
select y.* from (

(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y) as cond
left outer join
( select id from child where EndDATE is null group by id) AS condFilter
on cond.id = condFilter.id where ((cond.EndDate is null and condFilter.id is not null) or (cond.EndDate is not null and condFilter.id is null))

Sometimes you may wish to filter an sql only if that would give you a record. For example if you have a table that holds several telephone numbers for a customer. The table has a flag "preferred” (True or false) that specifies a users preferred contact number. Now in the SQL we wish to create a list of users complete with telephone numbers. Here is where we hit a snag, due to input errors/business logic bugs. A number of users have been allowed to enter several contact numbers without selecting a preferred one. Therefore we can’t simply filter preferred numbers as this would lead to some customers not having a number were a number does actually exist.

The solution I propose is two fold.

Firstly, We need to know are preferred numbers available for a user. To do this we first make an sql statement that provides this info. In my example lets select all entries grouped by customer id where preferred.
(i.e.

Select CustomerID as CustomerID2 from TelephoneDetails where preferred group by CustomerID

. )

Secondly, To use a xnor logical clause. To create this we need a clause in our sql that states ( (A and B) or ( !A and !B)). Clause A will be simply is the telephone number preferred, clause b will be are preferred numbers available for this customer from above. To apply clause b we must first join the table above to the standard table. To allow non-preferred rows to be returned we need to use an outer join.

Our Sql becomes:

Select * from TelephoneDetails as a left outer join (
Select CustomerID as CustomerID2,  from TelephoneDetails where preferred group by CustomerID
) as b on a.CustomerID = b.CustomerID2
where (( a.Preffered = 1 and b.CustomerID2 is not null) or (a.Preffered = 0 and b.CustomerID2 is null))

This will now return a customers preffered telephone if set, and other telephone if not.

Given Data

CustomerID	Number	Prefered
1	1251	true
1	1255	false
2	1235	false

Standard Filter would give

CustomerID	Number	Prefered
1	1251	true

but our sql returns

CustomerID	Number	Prefered
1	1251	true
2	1235	false