PL/SQL nested select with decode

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

PL/SQL nested select with decode

Author

Topic

apitts0217
Starting Member

1 Post

Posted - 2013-03-14 : 18:33:18

Good afternoon SQ Server Forumt

Have a list of dates (called TAU_LAST_UPDATE_DATE) associated with TAU_UIDs would like to
1. Pick the TAU_UID which has a date field which is null
unless
2. There are multiple date fields with nulls in which case just pick one,
say order by rownum and pick the lowest one
unless
3. There are no nulls in which case pick the oldest date

Have started with

Select *
from
(select *
from
(select TAU_LAST_UPDATE_DATE, TAU_UID,
DECODE(TAU_LAST_UPDATE_DATE, NULL, 01/01/1900) result
from TAU_TREATMENT_AUTHORIZATION)
where result IS NOT NULL
Order by rownum)
where rownum = 1

This works ok.

This gives a single TAU_UID whether there is one or multiple nulls
but how do I get it to, if there is no result select the TAU-UID with the oldest date?

I have the kernel as

Select MIN(TAU_LAST_UPDATE_DATE)
FROM TAU_TREATMENT_AUTHORIZATION

But this doesn't pull the TAU_UID
AND
I need some kind of if then else to connect the oldest date selector
to work if there is no result from the null value statement.

Thanks

Allen in Dallas

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-14 : 22:11:16

-- Reading up on the Decode in Oracle. Would this not work
Select *
from
(select *
from
(select TAU_LAST_UPDATE_DATE, TAU_UID,
DECODE(TAU_LAST_UPDATE_DATE, NULL, 01/01/2100,TAU_LAST_UPDATE_DATE) result
from TAU_TREATMENT_AUTHORIZATION)
Order by result desc)
where rownum = 1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-03-15 : 18:51:08

I may be completely off course from where you want to get to but...[CODE]select
t1.TAU_UID,
min(t1.TAU_LAST_UPDATE_DATE) TAU_LAST_UPDATE_DATE
from
TAU_TREATMENT_AUTHORIZATION t1
where
exists (select *
from TAU_TREATMENT_AUTHORIZATION t2
where t1.TAU_UID = t2.TAU_UID
and t2.TAU_LAST_UPDATE_DATE is null
)
group by t1.TAU_UID[/CODE]Does this get you there?

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources