-1

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.

Phlegon_of_Tralles
  • 181
  • 1
  • 14
  • 1
    `coalesce(g.email, q.email_Addr,'OTHR')` if g.email is null it will use q.email_addr. if q.email_addr is blank it will use 'OTHR' but that's straight SQL not a GUI editor... so I don't know if you can do that in peoplesoft-query coalesce() returns the first non-null value in a series seperated by comma – xQbert Jun 02 '22 at 14:52
  • 1
    Yeah that doesn't work - I think it's because the Q.EMAIL_ADDR is a field in a table and the table has another field - Q.E_ADDR_TYPE - which is where the filter 'OTHR' is. If that makes sense. Thank you though. – Phlegon_of_Tralles Jun 02 '22 at 14:58
  • This works, but it doesn't isolate the 'OTHR' records: %coalesce(G.EMAILID, q.email_Addr) – Phlegon_of_Tralles Jun 02 '22 at 15:22
  • Hey xQbert - the solution worked; I just had to add the criteria on the table (in the query tab). So this %coalesce(G.EMAILID, q.email_Addr) works - can you put it as an answer and I'll mark it as solved? – Phlegon_of_Tralles Jun 02 '22 at 15:35

1 Answers1

1

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
xQbert
  • 34,733
  • 2
  • 41
  • 62