SQLTeam.com | Weblogs | Forums

Greatest Function in SQL


#1

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


#2

explain GREATEST and the difference between MAX and GREATEST


#3

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?


#4

You can use case or iif


#5

how would I do that?


#6

Can you show the whole greatest clause you're trying to convert?


#7

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


#8

I think this might work:

Query
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
;

#9

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 :frowning: :slight_smile: .


#10

you are asking me questions on my questions? :rofl::joy:


#11

That was a typo sorry, I did answer you question. :grin:


#12

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.


#13

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.


#14

It doesn't have to be case. My example doesn't use CASE


#15

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

#16

Thanks Jeff,gBritton for help on this. have not used cross apply before, have to see how it works in my queries :fist_left:


#17

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


#18

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


#19

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.


#20

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