Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-03-01T09:17:44Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic No EXISTS predicate allowed in a searched WHEN clause of a CASE control statement?

‏2006-02-08T22:58:48Z |
The syntax diagram in the manual appears to allow this but when I try to
execute this interactively I get an error message that token EXISTS was not
valid. This is being executed on OS/400 V5R2. Here's the statement in
error:

SELECT
( CASE WHEN EXISTS (SELECT * FROM INSPRC
WHERE CNCOMP = ARCOMP AND
CNBECD = ARBECD AND
CNCACD = 'RM')
THEN 'M'
ELSE 'R' END ), ARBECD FROM INITEM

Is this statement malformed and would work with some correction or do I need
to take another approach to the problem? I'm trying to assign a code value
of 'R' or 'M' to a column based on the existence of any record in the INSPRC
file.

Thanks in advance for any advice,
Paul

Paul Morgan
Senior Programmer Analyst - Retail
J. Jill Group
100 Birch Pond Drive, PO Box 2009
Tilton, NH 03276-2009
Phone: (603) 266-2117
Fax: (603) 266-2333

Updated on 2013-03-01T09:17:44Z at 2013-03-01T09:17:44Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: No EXISTS predicate allowed in a searched WHEN clause of a CASE control statement?

    ‏2006-02-10T11:38:15Z  in response to SystemAdmin
    > SELECT
    > ( CASE WHEN EXISTS (SELECT * FROM INSPRC
    > WHERE CNCOMP = ARCOMP AND
    > CNBECD = ARBECD AND
    > CNCACD = 'RM')
    > THEN 'M'
    > ELSE 'R' END ), ARBECD FROM INITEM
    >

    I would take a somewhat different approach:

    SELECT CASE WHEN CNCOMP IS NOT NULL THEN 'M' ELSE 'R' END, ARBECD
    FROM INITEM
    LEFT JOIN INSPRC
    ON CNCOMP = ARCOMP AND CNBECD = ARBECD AND CNCACD = 'RM'

    on the assumption that the fields starting with CN belongs to table INSPRC
    and fields starting with AR belongs to INITEM.
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: No EXISTS predicate allowed in a searched WHEN clause of a CASE control statement?

      ‏2006-02-23T00:32:27Z  in response to SystemAdmin
      If there are multiple rows of INSPRC which match with a row of INITEM,
      multiple same rows will appear in result.
      If you don't want repeat same rows in result, you had better to use DISTINCT keyword.
      [code]SELECT DISTINCT CASE WHEN CNCOMP IS NOT NULL THEN 'M' ELSE 'R' END, ARBECD
      FROM INITEM
      LEFT JOIN INSPRC
      ON CNCOMP = ARCOMP AND CNBECD = ARBECD AND CNCACD = 'RM'[/code]

      This will also work on DB2 UDB for iSeries V5R3 up:
      [code]SELECT COALESCE(MR, 'R'), ARBECD
      FROM INITEM AR
      LEFT JOIN
      LATERAL
      (SELECT MAX('M')
      FROM INSPRC CN
      WHERE CN.CNCOMP = AR.ARCOMP
      AND CN.CNBECD = AR.ARBECD
      AND CN.CNCACD = 'RM'
      ) Q (MR)
      ON 0=0
      ;[/code]
      • SystemAdmin
        SystemAdmin
        3129 Posts
        ACCEPTED ANSWER

        Re: No EXISTS predicate allowed in a searched WHEN clause of a CASE control statement?

        ‏2013-03-01T09:17:44Z  in response to SystemAdmin
        select
        case when ( select '.' from Table2 T2
        where T2.KEY1=T1.KEY1 and T2.KEY2='xyz'
        fetch first 1 rows only ) is null
        then 'No' else 'Yes' end
        T1.*
        from Table1 T1
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: No EXISTS predicate allowed in a searched WHEN clause of a CASE control statement?

    ‏2006-02-10T15:00:09Z  in response to SystemAdmin
    EXISTS is not supported on CASE yet.

    Paul Morgan wrote:
    > The syntax diagram in the manual appears to allow this but when I try to
    > execute this interactively I get an error message that token EXISTS was not
    > valid. This is being executed on OS/400 V5R2. Here's the statement in
    > error:
    >
    > SELECT
    > ( CASE WHEN EXISTS (SELECT * FROM INSPRC
    > WHERE CNCOMP = ARCOMP AND
    > CNBECD = ARBECD AND
    > CNCACD = 'RM')
    > THEN 'M'
    > ELSE 'R' END ), ARBECD FROM INITEM
    >
    > Is this statement malformed and would work with some correction or do I need
    > to take another approach to the problem? I'm trying to assign a code value
    > of 'R' or 'M' to a column based on the existence of any record in the INSPRC
    > file.
    >
    > Thanks in advance for any advice,
    > Paul
    >

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)