Using Decode
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Using Decode

  1. #1
    Join Date
    May 2003
    Posts
    49

    Question Using Decode

    I am trying to write a query that uses if... then... else (DECODE), but I'm having trouble getting it to work.

    Here is my current query -

    Code:
    SELECT t.Start_Dat, t.End_Dat 
    FROM Tariff t 
    WHERE t.Start_Dat >= (SELECT max(t.start_dat) 
    	FROM Tariff t 
    	WHERE t.Start_dat <= to_date('21/11/2003', 'dd/mm/yyyy'))
    Sometimes the sub query returns empty if this happens I want to use an alternative date. The theory is something like this -

    Code:
    SELECT t.Start_Dat, t.End_Dat 
    FROM Tariff t 
    WHERE t.Start_Dat >= (If select max = '' Then '20/11/2003')
    Here is my query so far but I can't get it to work

    Code:
    SELECT t.Start_Dat, t.End_Dat 
    FROM Tariff t 
    WHERE t.Start_Dat >= (DECODE(SELECT Max(t.start_dat) 
    	FROM Tariff t 
    	WHERE t.Start_Dat <= to_date('21/11/2003', 'dd/mm/yyyy'), '', to_date('20/11/2003', 'dd/mm/yyyy')))
    It doesn't seem to like the select max within the decode? Does anyone know how to make this work?

    Any help will be greatly appreciated

    Cheers Al

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Put the whole of select expression within brackets in decode function. Syntax will then be right.
    -- Dilip

  3. #3
    Join Date
    May 2003
    Posts
    49
    Patel_dil,

    Thanks for your reply, I've done as you suggested, it's now stopped complaining about syntax, but is ignoring the decode completely? Do you have any ideas as to why? By that I mean no matter what I change the dates to it returns the same result set???

    Al

  4. #4
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    With no error message, no idea about your database objects and data, it is difficult to comment as to what the problem might be. Try running that sql inside decode and see if that returns what you want. Debug the sqls individually before you put decode with them. Paste here what you are actually trying to do. Pasting sample of your data and what you expect might help a lot.

    Thanks
    -- Dilip

  5. #5
    Join Date
    May 2003
    Posts
    49
    Dilip,

    Thanks for bearing with me on this -

    I think I see where the problem is
    I've told it to check the max date then if it's '' use 20/11/2003. I now need to give it the else part, which is use the returned date from the max query.

    What's the best way to do this? I've put a comma in and rewriten out the max query which appears to work, but looks messy. Is there a better way of structuring it?

    Code:
    SELECT t.Start_Dat, t.End_Dat 
    FROM Tariff t 
    WHERE t.Start_Dat >= (decode((SELECT max(t.start_dat) 
    	FROM Tariff t 
    	WHERE t.Start_dat <= to_date('19/11/2003', 'dd/mm/yyyy')), Null, to_date('10/11/2003', 'dd/mm/yyyy'), (SELECT max(t.start_dat) 
    		FROM Tariff t 
    		WHERE t.Start_dat <= to_date('19/11/2003', 'dd/mm/yyyy'))))

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    If you are checking for null as in above sql, you can use NVL instead of decode function. and your query might be something like following.

    SELECT t.Start_Dat, t.End_Dat
    FROM Tariff t
    WHERE t.Start_Dat >=
    (NVL((SELECT max(t.start_dat) FROM Tariff t WHERE t.Start_dat <= to_date('19/11/2003', 'dd/mm/yyyy')),
    to_date('10/11/2003', 'dd/mm/yyyy')))


    HTH.
    -- Dilip

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For readability (ie. maintainability) purposes I prefer to place the Nvl() -- or decode -- inside the subquery. Just a preference
    Code:
    SELECT
       t.Start_Dat,
       t.End_Dat 
    FROM
       Tariff t 
    WHERE
       t.Start_Dat >= 
          (SELECT
             Nvl(max(t.start_dat),
                to_date('10/11/2003', 'dd/mm/yyyy'))
          FROM  Tariff t
          WHERE t.Start_dat <= 
             to_date('19/11/2003', 'dd/mm/yyyy'));
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2003
    Posts
    49
    Dilip and SlimDave,

    Brilliant thanks for all your help, I've not tried the NVL function yet will do first thing tomorrow morning.

    Thanks again

    Al

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    NVL and DECODE are all old methods.

    Another method is using COALESCE function.

    SELECT
    t.Start_Dat,
    t.End_Dat
    FROM
    Tariff t
    WHERE
    t.Start_Dat >=
    COALESCE((SELECT
    max(t.start_dat)
    FROM Tariff t
    WHERE t.Start_dat <=
    to_date('19/11/2003', 'dd/mm/yyyy'),
    to_date('10/11/2003', 'dd/mm/yyyy')
    );

    Tamil

  10. #10
    Join Date
    May 2003
    Posts
    49
    Tamil,

    Thanks for your response.

    I've got Dilip and SlimDave's NVL function to work, but will give the COALESCE function a whirl. Are there any benefits to using the new function? i.e speed??
    Cheers Al
    Last edited by Running Bear; 12-12-2003 at 12:50 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width