Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Question Unanswered: SQL/400 Case When Subquery

    I have a query that I've been able to do in T-Sql using an 'in' predicate in a Case/When. I modified this to run in SQL/400 by the error is get is:

    Simple comparison operators other than equal and not equal cannot be used with a list of items. ANY, ALL, and SOME comparison operators must be followed by a fullselect, rather than an expression or a list of items, and cannot be specified in a JOIN condition or in a CASE expression

    Code:
    SELECT CLIENT, MACTNM, CLMSEQ, CHKDAY,
    	CASE WHEN CHKDAY = 0 THEN NULL 
    	WHEN CHKDAY IN 
    		(SELECT BADVAL FROM BADDATES  
    			WHERE RTRIM(TBL_SRC) = 'X' AND 
    			RTRIM(COL_SRC) = 'Z'  )  THEN NULL 
    	ELSE CAST(SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 2 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 4 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 6 , 2 ) AS DATETIME)
    	END AS CHKDATE   
    FROM MYTABLE
    WHERE CLIENT = 'X' AND MACTNM = 'Y' 
    ORDER BY CLIENT, MACTNM, CLMSEQ;
    For the life of me, I can't figure out how to modify this subquery to get it to run.

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What is "SQL/400" ?

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    Quote Originally Posted by shammat View Post
    What is "SQL/400" ?
    IBM's version of SQL for AS/400 or System i (I've often seen it referred to as SQL/400).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat View Post
    What is "SQL/400" ?
    It is the "nom du jour" for DB2 for the AS 400.

    On that note, I'll move this thread to the DB2 Forum where it ought to get faster/better responses than in ANSI SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2009
    Posts
    6
    Quote Originally Posted by Pat Phelan View Post
    It is the "nom du jour" for DB2 for the AS 400.

    On that note, I'll move this thread to the DB2 Forum where it ought to get faster/better responses than in ANSI SQL.

    -PatP
    Thanks. Sorry for posting it in the wrong forum.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No problem with posting in the wrong forum, it takes a while for new users to figure out where to post to get quick/good answers so we (the moderators) will often help out.

    I'm posting a guess on this because I don't have an AS/400 system to test it on, but I'd suggest something like:
    Code:
    SELECT CLIENT, MACTNM, CLMSEQ, CHKDAY,
    	CASE WHEN CHKDAY = 0 THEN NULL 
    	WHEN EXISTS (SELECT 1 FROM BADDATES  
    			WHERE RTRIM(TBL_SRC) = 'X' AND 
    			RTRIM(COL_SRC) = 'Z'  AND
                                          CHKDAY = BADVAL)  THEN NULL 
    	ELSE CAST(SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 2 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 4 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 6 , 2 ) AS DATETIME)
    	END AS CHKDATE   
    FROM MYTABLE
    WHERE CLIENT = 'X' AND MACTNM = 'Y' 
    ORDER BY CLIENT, MACTNM, CLMSEQ;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2009
    Posts
    6
    You nearly had it, thanks. The 'EXISTS' threw it off, but if I change that to
    "WHEN '1' = (SELECT 1 FROM BADDATES...)" then it worked just fine.

    Alternatively, if I just used "WHEN CHKDAY = (SELECT BADVAL FROM BADDATES ...)" then that worked, too. I had tried that earlier and it wouldn't run but now it does so I must have had something else wrong in my earlier trial.

    Thanks for the help. Case closed.

    Quote Originally Posted by Pat Phelan View Post
    No problem with posting in the wrong forum, it takes a while for new users to figure out where to post to get quick/good answers so we (the moderators) will often help out.

    I'm posting a guess on this because I don't have an AS/400 system to test it on, but I'd suggest something like:
    Code:
    SELECT CLIENT, MACTNM, CLMSEQ, CHKDAY,
    	CASE WHEN CHKDAY = 0 THEN NULL 
    	WHEN EXISTS (SELECT 1 FROM BADDATES  
    			WHERE RTRIM(TBL_SRC) = 'X' AND 
    			RTRIM(COL_SRC) = 'Z'  AND
                                          CHKDAY = BADVAL)  THEN NULL 
    	ELSE CAST(SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 2 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 4 , 2 ) || '/' || 
    		SUBSTRING ( CAST ( CHKDAY + 
    			1000000 AS CHAR ( 7 ) ) , 6 , 2 ) AS DATETIME)
    	END AS CHKDATE   
    FROM MYTABLE
    WHERE CLIENT = 'X' AND MACTNM = 'Y' 
    ORDER BY CLIENT, MACTNM, CLMSEQ;
    -PatP

Tags for this Thread

Posting Permissions

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