Oracle Database Discussions

GREATEST

i am getting ora-00936 error

its saying missing expression at the TABLE(DATE(date1,date2,date3,date4)) closeddate

i could not group the columns

select id,greatest(date1,date2,date3....)  closed_date
from YOUR_TABLE                                                                                                                                                                                    

You don't need any of that. Check the documentation jeneesh has directed you to.

SORRY i could not retrieve the values for closed date column

in the output

values in ID column are displayed but closed_date column is empty

the query is
select ID,GREATEST(DATE1,DATE2,DATE3,DATE4) closed_date
from t1
the output i got is
ID closed_date
100
101
102
103


etc

values are not displayed under closed date cloumn
(once again FYI DATE1,DATE2,DATE3,DATE4 are column names)

help me out

no values are retrieved for closed_date

the column is empty

please help me out.

this is an urgent requirement for me

That means one of the columns included inside the greatest function is null.

Nicolas.

yaa sum of the column values are also null in each row

so use the NVL on each column. and set it to something ridiculous, so it will always be less than the other values. and then, if the ridiculous value shows up, it means all the columns were null for that row.

hi i cannot change anything on the database as it is in the dev environment

i am java programmer has to retrieve the values and display them in a jsp page

so i need write a query in such a way that i get the max of dates of all columns of each record in to one column .

i can only query the database.

so please help me out

because of this query i am held

i need to submit this my tommorow

thanks for the help in advance

regards

Try this..

select id,greatest(nvl(date1,sysdate-36500),
                         nvl(date2,sysdate-36500),
                         nvl(date3,sysdate-36500)....)  closed_date
from YOUR_TABLE                                                                                                                                                                                                                                                                                                                                                                                                                    

so use the NVL on each column. and set it to
something ridiculous, so it will always be less than
the other values. and then, if the ridiculous value
shows up, it means all the columns were null for that
row.

Rather than relying on some rediculously small date use coalecse on each date column with the date column of intereste listed first. The order of the remaining terms in the coalesce statement is unimportant. e.g. on a table with three date columsn:

with x as (
  select 1 id, sysdate dte1, sysdate-1 dte2, sysdate+1 dte3 from dual union
  select 2, sysdate, null, null from dual union
  select 3, null, sysdate-1, null from dual union
  select 4, null, sysdate-1, sysdate+1 from dual union
  select 5, null, null, null from dual
)
select id, 
       greatest( coalesce(dte1,dte2,dte3), 
                 coalesce(dte2,dte3,dte1), 
                 coalesce(dte3,dte1,dte2)) Max_Date
from x
 
ID                     MAX_DATE                  
---------------------- ------------------------- 
1                      09-OCT-2007 12.15.44      
2                      08-OCT-2007 12.15.44      
3                      07-OCT-2007 12.15.44      
4                      09-OCT-2007 12.15.44      
5                      (null)
 
5 rows selecte

This way if they are all null, you get a null result, and don't need to handle your null value date as a special case. It also avoids the situation where the unlikely event occurs where your special null value date appears as actual data.

thanks jeenesh and shobloc

ur code worked

thanks a lot

regards

hi friend,
select id,

greatest( coalesce(dte1,dte2,dte3),

coalesce(dte2,dte3,dte1),

coalesce(dte3,dte1,dte2)) Max_Date

from x

this mysql work for 3 columns and if i have 30 columns then.I have to write all columns in coalesce method are is there any another mysql. please suggest me .

Edited by: user12922016 on Apr 2, 2010 11:55 PM