Using Decode

[Running Bear]

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 -

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 -

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

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

[patel_dil]

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

[Running Bear]

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

[patel_dil]

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

[Running Bear]

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?

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

[patel_dil]

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.

[slimdave]

For readability (ie. maintainability) purposes I prefer to place the Nvl() -- or decode -- inside the subquery. Just a preference

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

[Running Bear]

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

[tamilselvan]

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

[Running Bear]

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