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

Put the whole of select expression within brackets in decode function. Syntax will then be right.

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

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,

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'))))

---

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.

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'));

---

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 :D

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

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

COALESCE fn will return the first NOT-NULL value from the list starting from left to right. Here order of checking is important.
This will solve many if-then-else conditions in a single function.
For example:
In a query where a column value is checked against 3 different subqueries, then coalesce fn can be used.

Select * from table_A
where A.tdate = COALESCE (
( select somedate from Table_B where ....),
( Select somedate from Table_C where ...) ,
( select somedate from Table_D where ...)
) ;

Here, if the first sub query ( from Table_B) returns NULL, then the 2nd Query will be executed. If the 2nd query ( From Table_C) returns null , then the 3rd Query ( from Table_D) will be executed.

Hope this help you.

Tamil

Quote:

---

Originally posted by tamilselvan
NVL and DECODE are all old methods.

---

That's good -- Oracle's probably got most of the bugs out of them then.

COALESCE and CASE are the SQL/92 versions - so will be understandable by non-Oracle people. v.Bad for job security. ( . . . and it takes me a lot of thought to spell colaecse correctly.)