Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Support of iif statement?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select IIF(foo='bar',qux,937) as zorp

    becomes

    select CASE when foo='bar' then qux else 937 END as zorp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Posts
    39
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Aug 2003
    Posts
    39
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •