SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 PL/SQL nested select with decode		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

apitts0217
Starting Member

USA
1 Posts
Posted - 03/14/2013 :  18:33:18  Show Profile  Reply with Quote
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

Australia
52 Posts
Posted - 03/14/2013 :  22:11:16  Show Profile  Reply with Quote
-- 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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1429 Posts
Posted - 03/15/2013 :  18:51:08  Show Profile  Reply with Quote
I may be completely off course from where you want to get to but...
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
Does this get you there?

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000