You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Need help with If-Then Expression

3 replies [Last post]
Ed-D's picture
User offline. Last seen 9 years 19 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello everyone,

I am using PeopleSoft Query 8.8x online, and need to write an expression that looks between two dates (provided by prompts) and does the following:

First, if the Hire Date is between the two prompt dates, it will return "New Hire"

Second, if the Termination Date is between the two dates, it will return "New Term"

Third, if those two conditions are not met, it will simply need to return "Existing"

I have been wrestling with this and cannot quite figure it out. I must use an expression as we do not have access to edit SQL.

Can anyone help?

Thanks in advance,

Ed

Lepa's picture
User offline. Last seen 2 years 1 week ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Need help with If-Then Expression

Ed-D you need to use "Case" to accomplish that. on the expression tab, click add expression and then Character on the Expression type drop down. The length box should be large enough to hold the text you want, example "new hire". Now, in the expression Text field, add something like the below:

case when Hire-Date between date1 and date2 then 'New Hire'
when Termination-Date between date1 and date2 then 'New Term'
else 'Existing'
end

Click ok when you are done. Again on the Expression page, you need to click on "Use as field" link. This way, the expression will be added to your "Select" fields.

if you look at the "View SQL" page, you should see the expression with your selected fields. Make sure you select hire-date and termination-date fields back as well for the Case to work. Your select should look like:

SELECT hire-DATE, termination-DATE,
       CASE
          WHEN hire - DATE BETWEEN date1 AND date2
             THEN 'New Hire'
          WHEN termination - DATE BETWEEN date1 AND date2
             THEN 'New Term'
          ELSE 'Existing'
       END
  FROM table1;

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 9 years 19 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Need help with If-Then Expression

Awesome!

Thanks very much, Lepa!!!!! You totally ROCK!!!!!

Best Regards,

Ed

Guest's picture
Guest (not verified)
Re: Need help with If-Then Expression

(CASE WHEN B.HIRE_DT BETWEEN (:1) AND (:2) THEN ('New Hire') WHEN B.REHIRE_DT BETWEEN (:1) AND (:2) THEN ('New Hire') WHEN B.TERMINATION_DT BETWEEN (:1) AND (:2) THEN ('Termination') ELSE ('Existing') End)

You will have to create prompts for the between dates (:1) and (:2) - make certain to format the prompts as dates.