| View previous topic :: View next topic |
| Author |
Message |
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
 Posted: Tue Jun 12, 2007 9:29 am Post subject: subselect in a CASE structure |
 |
|
Hi,
Would it be possible to give a SUBSELECT in a CASE structure?
For example
| Code: |
SELECT CONTRACT_NO ,
CASE
WHEN TEMP.RETURN_TYPE = '9'
THEN (SELECT AMT1 AS AMT FROM DNTAMTTAB R
WHERE R.CONTRACT_NO =
TEMP.CONTRACT_NO )
ELSE '0.00'
END
FROM DNTCONTRACTTAB TEMP;
|
I am getting error while trying some thing like this
Much appreciated
JA |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 12, 2007 9:36 am Post subject: |
|
|
Jamylady,
It would be great if you could explain as to what you are trying to do with example data.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Tue Jun 12, 2007 9:44 am Post subject: |
|
|
I want to retrieve the amout from DNTAMTTAB when the return type is '9'.
I know I can do it using a join, but the problem is that I have various values for RETURN_TYPE and for RETURN_TYPE, I need to get the amount from different table. So I thout it would be easy if I can use the CASE--WHEN structure
I hope I made it clear now.
Thanks |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Tue Jun 12, 2007 10:34 am Post subject: |
|
|
 What is the entire error message? |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Tue Jun 12, 2007 10:42 am Post subject: |
|
|
Hi, Following is the error message I got. | Code: |
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "AMT1". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: END
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 243 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'000000F3' X'00000000' SQL DIAGNOSTIC INFORMATION
|
I tried putting the subselect inside brackets
Then it showed another error | Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT413I SQLCODE = -4700 INVALID SQLCODE
DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOCNR SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+ |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 12, 2007 10:49 am Post subject: |
|
|
Jamylady,
Ok I assumed the following
1. You have 2 tables DNTCONTRACTTAB & DNTAMTTAB and the matching key is the Contract_no
2. your task is to fetch the amount from DNTAMTTAB for all rows with retrun_type = 9 from DNTCONTRACTTAB. If the contract_no matches then get the amount else default the amout to zero. is that right?
If that is indeed the case then try this
[code:1:c236df9d73]
SELECT A.CNO
,A.AMT
FROM (SELECT B.CNO CNO
,SUM(B.AMT) AMT
,SUM(B.CD) CD
FROM (SELECT CONTRACT_NO CNO
,DEC(0) AMT
,INT(1) CD
FROM DNTCONTRACTTAB
WHERE RETURN_TYPE = 9
UNION
SELECT CONTRACT_NO CNO
,AMT AMT
,INT(2) CD
FROM DNTAMTTAB) B
GROUP BY B.CNO) A
WHERE A.CD
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Tue Jun 12, 2007 12:34 pm Post subject: |
|
|
Thanks Kolusu.
But just curious to know that whether it is still possible to have a subselect in the CASE ?
Thanks
JA |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 12, 2007 12:42 pm Post subject: |
|
|
Jamylady,
You cannot have a select statement on another table within a case statement
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Tue Jun 12, 2007 1:39 pm Post subject: |
|
|
| Thank you very much kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
