Search code examples
sqlpeoplesoftpeoplesoft-query

Case Statement with multiple criteria for the then

Okay I'm writing an expression - it's a case statement. What I want is this, if an email address isn't in the G.EMAIL field, then I want the email address in the Q.EMAIL_ADDR - but only the 'OTHR' ones in that table.

Right now my case statement looks like this:

(CASE WHEN G.EMAILID is NULL THEN Q.EMAIL_ADDR AND 'OTHR' ELSE G.EMAILID END)

Which is probably messed up. I'm using PeopleSoft Query manager - as it's the only reporting tool I have access to. On another report, the criteria tab, this is how the Q.EMAIL_ADDR table criteria is set up.

Criteria Tab

I'm adding the query record tab:

Query Tab

In the above, the field/column is EMAIL_ADDR, and the criteria ('OTHR') is in the E_ADDR_TYPE field.

Solution

  • Maybe something like: coalesce(g.email, q.email_Addr) but I'm not ure peoplesoft-query supports direct SQL statements like this.

    Keep in mind coalesce will return the first non-null value encountered in a series

    coalesce(NULL,NULL,NULL,'BOB','Tim') = BOB
coalesce(NULL,'Tim','BOB','Kate') = Tim