Topic
  • 4 replies
  • Latest Post - ‏2013-03-01T09:17:44Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts

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

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

    ‏2006-02-10T11:38:15Z  
    > 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

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

    ‏2006-02-10T15:00:09Z  
    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)
  • SystemAdmin
    SystemAdmin
    3129 Posts

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

    ‏2006-02-23T00:32:27Z  
    > 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.
    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

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

    ‏2013-03-01T09:17:44Z  
    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]
    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