Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: To check for the non existence in a table

    Need to check for the non existence in a table

    Code:
    CREATE TABLE TABLE1(COLUMN1 VARCHAR(20),COLUMN2 VARCHAR(20),COLUMN3 VARCHAR(20))
    
    INSERT INTO TABLE1 VALUES('A','W','SEAT');
    INSERT INTO TABLE1 VALUES('B','S','SEAT1');
    INSERT INTO TABLE1 VALUES('C','S','SEAT1');
    INSERT INTO TABLE1 VALUES('D','S','SEAT1');
    INSERT INTO TABLE1 VALUES('A','W','AFGH');
    
    select DISTINCT CASE WHEN (select 'TRUE' from TABLE1 WHERE NOT EXISTS (select * from TABLE1 WHERE COLUMN2<>'S' OR COLUMN2<>'W'))='TRUE' THEN 'TRUE' ELSE 'FALSE' END FROM TABLE1 ;

    Im trying the above query but not getting result as expected.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    WHERE COLUMN2<>'S' OR COLUMN2<>'W'

    this is always true as long as column2 isn't NULL




    Do you mean a query like this:

    SELECT CASE WHEN CNT = 0 THEN 'TRUE' ELSE 'FALSE' END FROM
    (select count(*) AS CNT from TABLE1 WHERE COLUMN2<>'S' AND COLUMN2<>'W') A
    Last edited by umayer; 04-07-09 at 10:37.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another variation.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CASE
           WHEN NOT EXISTS
              ( select * from TABLE1
                 WHERE COLUMN2 NOT IN ('S', 'W')
              ) THEN
                'TRUE'
           ELSE 'FALSE'
           END  "Answer is"
      FROM SYSIBM.SYSDUMMY1;
    ------------------------------------------------------------------------------
    
    Answer is
    ---------
    TRUE     
    
      1 record(s) selected.

  4. #4
    Join Date
    Jul 2008
    Posts
    94
    hi tonkuma,umayer both the queries worked perfectly.
    thank you.

Posting Permissions

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