Hi Guys,
I have a GREATEST function in Oracle that I need to translate to SQL, what the best way ?
the NVL function is like ISNULL, but cant figure out how to rewrite below query?
Thanks!
Pasi
select GREATEST(NVL((SELECT MAX(JOBX.FDT)
FROM JOB JOBX
explain GREATEST and the difference between MAX and GREATEST
The Oracle GREATEST function returns the “greatest” or largest value in a set of values that you provide to it. The both may be the same in SQL? not sure
and I think, SQL Max is a group function/aggregate function/summary function
Greatest is a single row function.- operates on every row, if I am not mistaking?
You can use case or iif
how would I do that?
Can you show the whole greatest clause you're trying to convert?
Here you go: this is all I can show for now, not sure if I even need the GREATEST here?
-/*Adjusted hire date is most recent of hire date, rehire date, or transfer date into current company. */
SELECT
,PERS.EMPLID
,GREATEST(NVL((SELECT MAX(JOBX.FDT)
FROM JOB JOBX
WHERE JOBX.EMPLID = PERS.EMPLID
AND JOBX.ACTION = 'XFR'
AND JOBX.ACTION_REASON = 'TAF'),'0001-01-01')
--- ,NVL(EMPL.HIRE_DT,'0001-01-01')
,ISNULL(EMPL.HIRE_DT,'0001-01-01') --SQL
-- ,NVL(EMPL.REHIRE_DT,'0001-01-01'))
,ISNULL(EMPL.REHIRE_DT,'0001-01-01')) AS ADJ_HIRE_DATE ---SQL
FROM PERSON_DATA PERS
I think this might work:
select pers.emplid
,case
when job.fdt is not null
and job.fdt>isnull(empl.hire_dt,cast('0001-01-01' as date))
and job.fdt>isnull(empl.rehire_dt,cast('0001-01-01' as date))
then job.fdt
when empl.hire_dt is not null
and empl.hire_dt>isnull(job.fdt,cast('0001-01-01' as date))
and empl.hire_dt>isnull(empl.rehire_dt,cast('0001-01-01' as date))
then empl.hire_dt
when empl.rehire_dt is not null
and empl.rehire_dt>isnull(job.fdt,cast('0001-01-01' as date))
and empl.rehire_dt>isnull(empl.hire_dt,cast('0001-01-01' as date))
then empl.rehire_dt
else cast('0001-01-01' as date)
end as adj_hire_date
from person_data as pers
left outer join (select max(job.ftd) as ftd
from job
where job.emplid=pers.emplid
and job.action='XFR'
and job.action_reason='TAF'
) as job
;
Thanks Bitsmed, not sure I will try this, this query I am working very complex and has about 15 sub queries, and this was one of them 
.
you are asking me questions on my questions? 
That was a typo sorry, I did answer you question. 
Here's an approach I've used for in-row MAX:
assume a simple table:
create table foo ( id int identity, a int, b int, c int)
insert into foo (a, b, c) values
(1,2,3),
(6,4,2),
(10,9,8)
For each row, select the id and the maximum (greatest) value of a, b, c)
select id, greatest
from foo
cross apply (
select max(v) from (values (a), (b), (c)) _(v)
) _(greatest)
Of course, you can substitute the column names a, b, c for a result from another sub query, if you like.
Thanks gbritton, that's another way to look at it, THE SQL does not recognize 'GREATEST" built in function but I guess there is no single function in SQL as is in Oracle to just use to get the 'greatest' , it has to be a CASE.
Pasi.
It doesn't have to be case. My example doesn't use CASE
Sometimes - reformatting the query to see what it is actually doing will help when trying to understand what it is actually doing.
The GREATEST function here is getting the highest (max) value from either JOBX.FDT or HIRE_DT or REHIRE_DT. The NVL function forces any null values to be returned with the lowest date as '0001-01-01'.
Using @gbritton method with CROSS APPLY:
Select
, PERS.EMPLID
--, GREATEST(NVL(( Select max(JOBX.FDT)
-- From Job JOBX
-- Where JOBX.EMPLID = PERS.EMPLID
-- And JOBX.ACTION = 'XFR'
-- And JOBX.ACTION_REASON = 'TAF'), '0001-01-01')
-- , NVL(EMPL.HIRE_DT,'0001-01-01')
-- , NVL(EMPL.REHIRE_DT,'0001-01-01'))
-- ) As ADJ_HIRE_DATE ---SQL
, adj.adj_hire_date
From PERSON_DATA PERS
Cross Apply (Select max(v.max_date) As adj_hire_date
From (
Values (isnull(Select max(jobx.FDT)
From Job jobx
Where jobx.empid = pers.emplid
And jobx.action = 'XFR'
And jobx.action_reason = 'TAF'), 0) -- default to 1900-01-01
, (isnull(empl.hire_dt, 0))
, (isnull(empl.rehire_dt, 0))
) As v(max_date)) As adj
And defaulting to the zero date in SQL Server (1900-01-01) we can do something like the above. To simplify the values statement, we can use another cross apply:
Select
, PERS.EMPLID
--, GREATEST(NVL(( Select max(JOBX.FDT)
-- From Job JOBX
-- Where JOBX.EMPLID = PERS.EMPLID
-- And JOBX.ACTION = 'XFR'
-- And JOBX.ACTION_REASON = 'TAF'), '0001-01-01')
-- , NVL(EMPL.HIRE_DT,'0001-01-01')
-- , NVL(EMPL.REHIRE_DT,'0001-01-01'))
-- ) As ADJ_HIRE_DATE ---SQL
, adj.adj_hire_date
From PERSON_DATA PERS
Cross Apply (Select max(jobx.FDT) As FDT
From Job jobx
Where jobx.empid = pers.emplid
And jobx.action = 'XFR'
And jobx.action_reason = 'TAF'
) As jobx
Cross Apply (Select max(v.max_date) As adj_hire_date
From (
Values (isnull(jobx.FDT), 0) -- default to 1900-01-01
, (isnull(empl.hire_dt, 0))
, (isnull(empl.rehire_dt, 0))
) As v(max_date)) As adj
Thanks Jeff,gBritton for help on this. have not used cross apply before, have to see how it works in my queries 
using below ---> getting error saying using ISNULL requires 2 arguments?
last part of query:
Values (isnull(jobx.FDT), 0) -- default to 1900-01-01
, (isnull(empl.hire_dt, 0))
, (isnull(empl.rehire_dt, 0))
) As v(max_date)) As adj
There is a typo...since I did not write this with available test data to validate and I don't have your tables to actually test.
(isnull(jobx.FDT), 0) should be (isnull(jobx.FDT, 0))
Not a prob, I am still working on this long query and some of data and tbls not ready yet for me to test.
Thanks.
I am almost getting one part to work but getting error:
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "pers.emplid" could not be bound.
Any Idea?
Select
PERS.EMPLID
--, GREATEST(NVL(( Select max(JOBX.FDT)
-- From Job JOBX
-- Where JOBX.EMPLID = PERS.EMPLID
-- And JOBX.ACTION = 'XFR'
-- And JOBX.ACTION_REASON = 'TAF'), '0001-01-01')
-- , NVL(EMPL.HIRE_DT,'0001-01-01')
-- , NVL(EMPL.REHIRE_DT,'0001-01-01'))
-- ) As ADJ_HIRE_DATE ---SQL
, adj.adj_hire_date
From PERSON_DATA PERS, EMPLOYMENT EMPL
Cross Apply (Select max(jobx.FDT) As FDT
From Job jobx
Where jobx.EMPLID = pers.emplid
And jobx.action = 'XFR'
And jobx.action_reason = 'TAF'
) As jobx
Cross Apply (Select max(v.max_date) As adj_hire_date
From (
Values (isnull(jobx.FDT, 0)) -- default to 1900-01-01
, (isnull(empl.hire_dt, 0))
, (isnull(empl.rehire_dt, 0))
) As v(max_date)) As adj