| shortest way to concatenate multiple messages based on column multiple values. [message #603047] |
Wed, 11 December 2013 03:28  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
I have written the below query to display message based on one "N" value.
SELECT CASE
WHEN CONSUMER_VALID = 'N' THEN 'CONSUMER'||' '||CONSUMER||' '||'IS NOT EXISTED'
WHEN CID_VALID ='N' THEN 'C_ID'||' '||C_ID||' '||'IS NOT EXISTED'
WHEN LOC_VALID = 'N' THEN 'LOCATION'||' '||LOCATION||' '||'IS NOT EXISTED'
WHEN PERIOD_VALID = 'N' THEN 'PERIOD'||' '||PERIOD||' '||'IS NOT EXISTED'
ELSE 'No MESSAGE'
END AS MESSAGE
FROM CONSUMER_DETAILS;
If multiple "N" values are there for one record it should concatenate multiple messages and show.
For Example for the below record
the message should be C_ID 180 IS NOT EXISTED AND LOCATION ABC IS NOT EXISTED
C0814818 35 180 ABC Y Y N N
If I write as above I have to write multiple CASE statements for all combinations.
It will bocome lengthy code.Is there any shortest way to write the query.
The data as below in the table.
CONSUMER PERIOD C_ID LOCATION CONSUMER_VALID PERIOD_VALID CID_VALID LOC_VALID
C0814826 105 160 BANG Y N Y Y
S0814827 103 666 SDF N N N N
C0814818 30 190 HYD Y Y N Y
C0814818 35 180 ABC Y Y N N
C0814830 0 160 SRT Y N Y N
C0814831 0 160 BANG Y N Y Y
C0814828 0 160 BANG Y N Y Y
C0814829 300 160 BANG Y N Y Y
C0814832 150 200 BANG Y N N Y
Please help me.
Thanks.
|
|
|
|
|
|
|
|
| Re: shortest way to concatenate multiple messages based on column multiple values. [message #603052 is a reply to message #603050] |
Wed, 11 December 2013 03:48  |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
Lalit Kumar B wrote on Wed, 11 December 2013 10:40martijn wrote on Wed, 11 December 2013 15:01see if decode() can help you.
Even with decode OP would have to write multiple decode statements for each expression.
Yup that's right.
But as far as I understood the question, I think that, one way or another, the different columns need to be checked for 'Y' or 'N'.
Decode always is a helpfull compadre to me, dor this kind of problems.
(Or...I didn't read the question good enough, and now I am way off)
|
|
|
|
| Re: shortest way to concatenate multiple messages based on column multiple values. [message #603055 is a reply to message #603052] |
Wed, 11 December 2013 03:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
martijn wrote on Wed, 11 December 2013 15:18the different columns need to be checked for 'Y' or 'N'.
OP has mentioned that he wants to only check for value 'N'. And the same value needs to be checked for different expressions.
@ajaykumarkona, after close to 400 posts, you should provide the DDLs and insert statements alongwith your question. And post the required output.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
