Oracle Communities

Oracle Analytics and AI

Child Item

Can i use MIN and Max Function together?

I am trying to use the a min and max functions on this case statements. What I want is that when the first case statement is true, I would like to return the min date and when the second case statement is true return the max date then group everything BY "Details"."PNumb"). I have tried it as shown below but getting syntax errors. any suggestions on where I might be going wrong?

(MIN(CASE WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

ELSE (CASE WHEN "Details"."PNumb" = "Details"."PI" AND "JOB"." Name" ="JOB"."Name"

THEN MAX("XX"."Date") END) END) BY "Details"."PNumb")

Find more posts tagged with

Accepted answers

All comments

Jerry Casey

Hi ForSly,

For any given record, "JOB"." Name" will always equal "JOB"."Name", so your WHEN and ELSE conditions are identical. (Or is that leading blank space intentional? If so, read on.)

The first condition will always catch anything that the ELSE statement is intended to catch, since both contain "Details"."PNumb" = "Details"."PI".

Your ELSE condition is more restrictive, so put that first, and reverse the MIN/MAX operations.

ForSly

What do you mean by reversing my MIN/MAX?- I have made some corrections- changed the job name definitions. Would you be kind enough to show me how I can write it without getting an error?

(MIN(CASE WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

ELSE (CASE WHEN "Details"."PNumb" = "Details"."PI" AND "JOB"." Name" LIKE '%-DIE' OR "JOB"." Name" LIKE '%-DEW'

THEN MAX("XX"."Date") END) END) BY "Details"."PNumb")

Jerry Casey

I don't know the details of your data, but I was talking about something like this:

CASE WHEN "Details"."PNumb" = "Details"."PI" AND ("JOB"." Name" LIKE '%-DIE' OR "JOB"." Name" LIKE '%-DEW') THEN MAX("XX"."Date" by "Details"."PNumb")

WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

ELSE cast(NULL as DATE) END

Your original statement has no condition for when "Details"."PNumb" <> "Details"."PI", so I added the ELSE NULL. If "Details"."PNumb" is always equal to "Details"."PI", you don't need it in the conditions.

I don't see why you would need to nest the whole thing with a MIN statement, unless I'm missing something. I moved the By "Details"."PNumb" clause into your MAX fnx.

ForSly

I wanted to be able group both min and max results set by "Details"."PNumb"). Does it make it any difference if I start with max ?

Jerry Casey

Your statement is testing a set of "PNumb"s against a set of conditions and extracting a specific date based on those conditions. That looks like the output is one date per "PNumb".

There must be more to it if you want to then group by PNumb. Not enough information for me to understand your requirement.

ForSly

my requirement is to calculate the offers we extended to employees , basically if someone applied for a job in January and we extended an offer to them in February, my requirement is that when I create metric to show offers extended in January, I should show all offers even those extended in February

ForSly