Oppolzer - Informatik / Blog

What I would question first about this coding is not a SQL topic but a real
world question.

The CASE expression only yields a non blank result, if a non-blank (or not null)
pair of dates occurs in sync in the first place. That is:

the dates that are compared are

K06 - K29
K05 - K28
K04 - X66
K03 - X65
K02 - X64
K01 - X63

The expression does not care about where the non-blank dates match.

But: if, for example, we have a non-blank date in K05, with a blank date in K28,
the overall result will be blank, even if later (for example in K02 and X64)
both dates will be non blank. That is, because K05/K28 is tested first in the
CASE, and this determines the overall result ... the rest isn't tested any more.

I could imagine that this is not what was desired. Maybe the different
conditions should be ORed instead ??

I would ask this question to the original coder of the SQL.

Kind regards

Bernd








Am 29.01.2015 um 11:01 schrieb Bernd Oppolzer:
> The rewrite of the SQL part should have been:
>
> K29.LS_ACTUAL_COMPL_DT <> '' and
> K29.LS_ACTUAL_COMPL_DT IS not NULL
>
> '' was missing ...
>
>
>
> Am 29.01.2015 um 10:57 schrieb Bernd Oppolzer:
>> I looked at the SQL that was attached to this post.
>>
>> There are many codings that seem somehow strange to me,
>> for example:
>>
>>        CASE
>>           WHEN    IFNULL(K06.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(K29.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN K29.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           WHEN    IFNULL(K05.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(K28.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN K28.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           WHEN    IFNULL(K04.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(X66.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN X66.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           WHEN    IFNULL(K03.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(X65.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN X65.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           WHEN    IFNULL(K02.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(X64.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN X64.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           WHEN    IFNULL(K01.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>>                   CASE
>>                    WHEN    IFNULL(X63.LS_ACTUAL_COMPL_DT, '') <> ''
>>                            THEN X63.LS_ACTUAL_COMPL_DT
>>                    ELSE    ''
>>                   END
>>           ELSE    ''
>>          END) <> ''
>>
>> this is part of the WHERE condition.
>>
>> If I don't miss anything, every such coding
>>
>> IFNULL(K29.LS_ACTUAL_COMPL_DT, '') <> ''
>>
>> can be rewritten to
>>
>> K29.LS_ACTUAL_COMPL_DT <> and
>> K29.LS_ACTUAL_COMPL_DT IS not NULL
>>
>> and we all know how critical index usage is with respect to functions in the where clause.
>>
>> (BTW: why are all those columns allowed to have NULLs? With a "normal" DB2 design
>> they would be NOT NULL ...)
>>
>> I don't know:
>>
>> maybe other DBMSes do more effort in algebraic rewrite of complex SQL statements,
>> which DB2 leaves to the coder. So the difference in CPU and run time to me would be
>> evident from this design decision. This can be complained about, but I would tune
>> the SQL first, regardless of the target DBMS. (I have no time to investigate this
>> further in this special case, unfortunately; this SQL has just too many things
>> which look very strange to me ... some debate with the original author would be
>> needed).
>>
>> I never had a performance problem with DB2 z/OS, 25 years of experience.
>>
>> But I had serious performance problems with other DBMSes, and these were
>> technical performance problems which had nothing to do with SQL coding
>> or index usage, but, for example, poor networking implementations etc.
>>
>> Kind regards
>>
>> Bernd
>>
>>
>>
>>
>> Am 28.01.2015 um 22:19 schrieb T.W.:
>>>
>>> Quite true Kurt.
>>>
>>> I could try running it later this evening when the system would be more quiet.
>>>
>>> The email I got this afternoon this is run times they got.  SQL Server (SS) has more data too.
>>>
>>>  SS:  Rows:  506 Execution time:  18 s
>>> DB2: Rows:  282 Execution time:  1 min 25s
>>>
>>> I attached the BMC SQL Performance Explain, SQL and other information.
>>>
>>> Attachment Links: EXPLAIN.txt (21 k)
>>
>