Re: DB2 CASE select : more than 1 result columns

[Dave <dfx1@xxxxxxxxxxxxxx>]

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