Selection rows based on hierarchial preference 1

[etprash]

Hi,
I have a table with columns:status, gl_date and others.
Status can take one of three values: CONFIRMED, REMITTED, CLEARED.
My problem is that User wants to select record for a particular gl_date with preference.
The order of preference is:
1.CONFIRMED 2.REMITTED 3.CLEARED
If only REMITTED AND CLEARED records are present then only row with REMITTED should be selected. Likewise if all three records are present for a gl_date then only CONFIRMED record should be selected.
How can i achieve this with a single query? I tried DECODE but could not come up with anything concrete.
Any help will be greatly appreciated.
Thanks,
Prashant

 

[hoinz]

In your where condition, you will have to compare status to the result of a subquery.
For the subquery, you may use a decode. Or you may rely on preference being the same as alphabetically ordering the third letter of your status in reverse order.

select * from my_table
where <condition for gl_date here>
and (substr(status,3,1)) = (select(max(substr(status,3,1))) from my_table)

hope this helps

 

[hoinz]

Just noticed that you will have to add <condition for gl_date> in the subquery as well.

 

[etprash]

Thanks Hoinz. This might solve my problem for now although in a subtle way.

 

[taupirho]

Looking at it froma slightly different angle, something like this might work.

1) create a little driver table t2
status wgt
CONFIRMED 1
REMITTED 3
CLEARED 5

2) Now the following query (t1 is your orginal table)

SELECT gl_date,status
(
SELECT t1.gl_date gl_date,t1.status status,t2.wgt wgt,
SUM(t2.wgt) OVER (PARTITION BY t1.gl_date) sum_wgt,
MAX(t2.wgt) OVER (PARTITION BY t1.gl_date) max_wgt
WHERE t1.status = t2.status
)
WHERE sum_wgt in (4,9)
AND wgt = max_wgt

Haven't tested this but it might just work



In order to understand recursion, you must first understand recursion.

 

[taupirho]

Oops,

need this line after the first SELECT

FROM

And need this line before the first WHERE

FROM t1,t2




In order to understand recursion, you must first understand recursion.