Support of iif statement?

AS400Admin · #1 (**permalink**) 10-27-03, 11:22

We are using DB2400 for AS400 and are trying to convert some of our reports from MS Access to directly to DB2.

We have been using Access as a go between for some reports because we use the "iif" function/statement but we would like to get away from that.

Does anyone know of a why to pass an "iif" or something simiar to DB2400?

TIA

Ryan

r937 · #2 (**permalink**) 10-27-03, 13:11

select IIF(foo='bar',qux,937) as zorp

becomes

select CASE when foo='bar' then qux else 937 END as zorp

AS400Admin · #3 (**permalink**) 10-29-03, 09:57

Quote:

Originally posted by r937
select IIF(foo='bar',qux,937) as zorp

becomes

select CASE when foo='bar' then qux else 937 END as zorp

Thanks for the help! Unfortunately, we haven't been able to use the case statement in a WHERE clause, only SELECT (as your example depicted).

Currently, MS Access supports IIF in the WHERE clause so we have our reporting software connecting to the AS400 through linked tables in MS Access. We are trying to get away from that.

Let me know if a look at the SQL would help.

Thanks in advance.

Ryan

r937 · #4 (**permalink**) 10-29-03, 12:24

yes, let's have a look at your query

according to the DB2 Universal Database for iSeries SQL Reference manual Case expressions section, you should be able to do it

the example given in that section is

Code:

SELECT EMPNO, WORKDEPT, SALARY+COMM
  FROM EMPLOYEE
  WHERE (CASE WHEN SALARY=0 THEN NULL
              ELSE COMM/SALARY
         END) > 0.25

rudy

AS400Admin · #5 (**permalink**) 10-31-03, 11:00

Rudy, your are right. We just tried it (and I book marked that reference site). Maybe it didn't work prior to V5R2. We originally wrote the reports in V4R5 and we really thought it wouldn't work.

Thanks for your help.

Ryan

Internet Services

Web Development

Digital Marketing

Consumer Tech