Re: DB2 CASE select : more than 1 result columns

Perhaps this is more natural using a CTE or join lateral, given the case:
Only note - default will be nulls


-- Just s simple input table: create table test (
typevalue char (1)
);

insert into test values ('1') ,('2') , ('3');


-- using CTE
with map_type (typevalue , col1 , col2 ) as (
values
('1' , '1A' , '2A' ),
('2' , '1B' , '2B' )
)
select * from test a left join map_type b on a.typevalue = b.typevalue;


-- using join lateral
Select * from test a
join lateral (
values
('1' , '1A' , '2A' ),
('2' , '1B' , '2B' )
) as b (typevalue , col1 , col2 )
on a.typevalue = b.typevalue ;

On Tue, Nov 5, 2024 at 9:36 PM Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

Just for fun:

drop table qtemp/test;
create table qtemp/test (Type *char*(*1*));

*insert* into qtemp/test (Type) values('1') , ('2') , ('3') , ('4') ,
('A');

drop table qtemp/testcol;
create table qtemp/testcol (Valid *char*(*10*) , vCol1 *char*(*10*), vCol2
*char*(*10*), vCol3 *char*(*10*));

*insert* into qtemp/testcol values(' ABC' , ' 111' , ' 222' , ' 333');

SELECT
*TRIM*(
*CONCAT*(
*SUBSTR*(vCol1, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*),
*0*
) + *1*, *1*), *SUBSTR*(
Valid, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*), *0*) +
*1*, *1*))) AS Col1,
*TRIM*(
*CONCAT*(
*SUBSTR*(vCol2, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*),
*0*
) + *1*, *1*), *SUBSTR*(
Valid, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*), *0*) +
*1*, *1*))) AS Col2,
*TRIM*(
*CONCAT*(
*SUBSTR*(vCol3, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*),
*0*
) + *1*, *1*), *SUBSTR*(
Valid, *COALESCE*(*TRY_CAST*(Type AS *INTEGER*), *0*) +
*1*, *1*))) AS Col3
FROM qtemp/test
CROSS JOIN qtemp/testcol;


--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno mar 5 nov 2024 alle ore 16:30 Dave <dfx1@xxxxxxxxxxxxxx> ha
scritto:

Hello,

Can I merge these statements into 1 CASE statement or do I need to repeat
the conditions as below?
Thanks

CASE
type
WHEN '1' THEN '1A'
WHEN '2' THEN '1B'
ELSE ' '
END col1

CASE
type
WHEN '1' THEN '2A'
WHEN '2' THEN '2B'
ELSE ' '
END col2
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.