Re: DB2 CASE select : more than 1 result columns

Hadn't thought of AI !!

Me : Can you optimise the statements so that the same controle of "type"
is only used once?


Certainly! Since both CASE statements use the same condition on type, we
can optimize this by evaluating type once and then mapping the results to
two separate columns. Here’s an optimized query:

sql
Copy code
CASE
type
WHEN '1' THEN '1A|2A'
WHEN '2' THEN '1B|2B'
ELSE ' | 'END AS combined_column

Then, we can separate the results of combined_column into col1 and col2 by
splitting on the | separator.

In a SQL environment that supports string splitting (such as PostgreSQL or
MySQL 8.0+), you can use SUBSTRING_INDEX() to extract the values for each
column:

sql
Copy code
-- Extract col1 and col2 from combined_column
SUBSTRING_INDEX(combined_column, '|', 1) AS col1,
SUBSTRING_INDEX(combined_column, '|', -1) AS col2

Full Query Example (MySQL 8.0+):

sql
Copy code
SELECT
SUBSTRING_INDEX(
CASE
type
WHEN '1' THEN '1A|2A'
WHEN '2' THEN '1B|2B'
ELSE ' | '
END, '|', 1
) AS col1,
SUBSTRING_INDEX(
CASE
type
WHEN '1' THEN '1A|2A'
WHEN '2' THEN '1B|2B'
ELSE ' | '
END, '|', -1
) AS col2FROM your_table;

Explanation

- The CASE statement now evaluates type only once.
- Depending on the value of type, it assigns combined_column a single
string with the values for col1 and col2, separated by |.
- SUBSTRING_INDEX() then extracts the first and second parts of this
combined string as col1 and col2, respectively.

This approach reduces redundancy and improves readability by evaluating
type
only once.





- *Subject*: Re: DB2 CASE select : more than 1 result columns
- *From*: Bryan Dietz <bdietz400@xxxxxxxxx>
- *Date*: Tue, 5 Nov 2024 12:44:19 -0600

------------------------------
this looked like a fun question to ask copilot AI, not bad...


<ai>
In this example, you have two separate CASE statements, but each CASE
statement is applied to the column type without repeating the conditions.
Unfortunately, you can't directly merge these into a single CASE statement
because they produce different values for col1 and col2.

It might feel like repetition, but each column's logic is isolated for
clarity and simplicity. If you have additional logic or if you're
calculating more values based on type, sticking with separate CASE
statements can keep it straightforward and maintainable.
</ai>

On Tue, 5 Nov 2024 at 16:30, Dave <dfx1@xxxxxxxxxxxxxx> wrote:

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.