| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-04-11, 10:59
|
|
Registered User
|
|
Join Date: Jun 2009
Posts: 6
|
|
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:
Quote:
|
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.
|
|
01-04-11, 11:15
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,407
|
|
|
|
01-04-11, 11:16
|
|
Registered User
|
|
Join Date: Jun 2009
Posts: 6
|
|
|
|
Quote:
Originally Posted by shammat
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).
|
|
01-04-11, 11:22
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Quote:
Originally Posted by shammat
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.
|
|
01-04-11, 11:24
|
|
Registered User
|
|
Join Date: Jun 2009
Posts: 6
|
|
Quote:
Originally Posted by Pat Phelan
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.
|
|
01-04-11, 12:21
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
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.
|
|
01-04-11, 12:38
|
|
Registered User
|
|
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
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 |
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL/400 Case When Subquery
