Prev: using List Tablespaces Show Detail to free pages
Next: Fetching MAX rows with one index scan.
From: Frank Swarbrick on 20 Jan 2010 14:00
Thanks, Tonkuma. This looks to get the results I am looking for.
Looking at the access plan, however, the way I had without the join and then
just getting the first row performs better, because it only scans the table
(well, index) once. With the join it's done twice. Seems to me there might
be some way to fix this, but I don't have time. Ah well.
Thanks again!
Frank
--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


n 1/17/2010 at 9:29 PM, in message
<128a2dde-5c98-45ea-a766-a3c5886b3449(a)m25g2000yqc.googlegroups.com>,
Tonkuma<tonkuma(a)fiberbit.net> wrote:
> or
>
> SELECT COALESCE(f_gl_ref.ACCOUNT_NUM , f_blank.ACCOUNT_NUM)
> FROM FB_LN_ACCT_PROD_REF f_blank
> LEFT OUTER JOIN
> FB_LN_ACCT_PROD_REF f_gl_ref
> ON f_gl_ref.LOAN_TYPE = f_blank. LOAN_TYPE
> AND f_gl_ref.CATEGORY = f_blank. CATEGORY
> AND f_gl_ref.CALL_TYPE = f_blank. CALL_TYPE
> AND f_gl_ref.FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT
> WHERE f_blank. LOAN_TYPE = :GL-REF-LOAN-TYPE
> AND f_blank. CATEGORY = :GL-REF-CATEGORY
> AND f_blank. CALL_TYPE = :GL-REF-CALL-TYPE
> AND f_blank. FAMILY_1ST_DOT = ' '
From: Frank Swarbrick on 20 Jan 2010 20:25
I said I would give up, but that never seems to work. :-)

Original way:

EXEC SQL
SELECT ACCOUNT_NUM
INTO :GL-REF-ACCOUNT-NUM
FROM (
SELECT ACCOUNT_NUM
FROM FB_LN_ACCT_PROD_REF
WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
AND CATEGORY = :GL-REF-CATEGORY
AND CALL_TYPE = :GL-REF-CALL-TYPE
AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
ORDER BY FAMILY_1ST_DOT DESC
) AS T
FETCH FIRST 1 ROWS ONLY
END-EXEC.

New way:

EXEC SQL
select coalesce(max(t.primary_acct_num)
, max(t.default_acct_num)
) as acct_num
INTO :GL-REF-ACCOUNT-NUM
from (
SELECT case when FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT then
ACCOUNT_NUM end
as primary_acct_num
, case when FAMILY_1ST_DOT = ' ' then ACCOUNT_NUM end
as default_acct_num
FROM FB_LN_ACCT_PROD_REF
WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
AND CATEGORY = :GL-REF-CATEGORY
AND CALL_TYPE = :GL-REF-CALL-TYPE
AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
) as t

This uses your idea of the coalesce, but does a pivot instead of a join to
get the two result columns that are to be coalesced.

Is it obvious what it is doing? That's another matter...
(Yes, I know that the coalesce(max(...)) stuff could be put around the inner
select in order to not require an outer select. Having it this way makes it
easier to debug.)

Frank

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


n 1/20/2010 at 12:00 PM, in message <4B56F05D.6F0F.0085.0(a)efirstbank.com>,
Frank Swarbrick<Frank.Swarbrick(a)efirstbank.com> wrote:
> Thanks, Tonkuma. This looks to get the results I am looking for.
> Looking at the access plan, however, the way I had without the join and
> then
> just getting the first row performs better, because it only scans the
> table
> (well, index) once. With the join it's done twice. Seems to me there
> might
> be some way to fix this, but I don't have time. Ah well.
> Thanks again!
> Frank
From: Tonkuma on 20 Jan 2010 20:50
> New way:
>
> EXEC SQL                                                    
>     select coalesce(max(t.primary_acct_num)
>                   , max(t.default_acct_num)
>            ) as acct_num
>     INTO :GL-REF-ACCOUNT-NUM                                
>     from (
>         SELECT case when FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT then
> ACCOUNT_NUM end
>                as primary_acct_num
>              , case when FAMILY_1ST_DOT = ' ' then ACCOUNT_NUM end
>                as default_acct_num
>         FROM FB_LN_ACCT_PROD_REF                            
>         WHERE LOAN_TYPE       = :GL-REF-LOAN-TYPE            
>           AND CATEGORY        = :GL-REF-CATEGORY            
>           AND CALL_TYPE       = :GL-REF-CALL-TYPE            
>           AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
>     ) as t
Good idea!

> This uses your idea of the coalesce, but does a pivot instead of a join to
> get the two result columns that are to be coalesced.
>
> Is it obvious what it is doing?  That's another matter...
It would be easy to understand for persons who know DB2 SQL idioms and
technique(like pivot).

> (Yes, I know that the coalesce(max(...)) stuff could be put around the inner
> select in order to not require an outer select.  Having it this way makes it
> easier to debug.)
You know well what you are doing.
From: Frank Swarbrick on 21 Jan 2010 11:31

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


n 1/20/2010 at 6:50 PM, in message
<22569c48-fe5b-4026-ab68-c002a3d3308f(a)k35g2000yqb.googlegroups.com>,
Tonkuma<tonkuma(a)fiberbit.net> wrote:
>> New way:
>>
>> EXEC SQL
>> select coalesce(max(t.primary_acct_num)
>> , max(t.default_acct_num)
>> ) as acct_num
>> INTO :GL-REF-ACCOUNT-NUM
>> from (
>> SELECT case when FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT then
>> ACCOUNT_NUM end
>> as primary_acct_num
>> , case when FAMILY_1ST_DOT = ' ' then ACCOUNT_NUM end
>> as default_acct_num
>> FROM FB_LN_ACCT_PROD_REF
>> WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
>> AND CATEGORY = :GL-REF-CATEGORY
>> AND CALL_TYPE = :GL-REF-CALL-TYPE
>> AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
>> ) as t
> Good idea!
>
>> This uses your idea of the coalesce, but does a pivot instead of a join
> to
>> get the two result columns that are to be coalesced.
>>
>> Is it obvious what it is doing? That's another matter...
> It would be easy to understand for persons who know DB2 SQL idioms and
> technique(like pivot).
>
>> (Yes, I know that the coalesce(max(...)) stuff could be put around the
> inner
>> select in order to not require an outer select. Having it this way
> makes it
>> easier to debug.)
> You know well what you are doing.

Blush. :-) Well thank you. I guess my main concern is that it may not be
understood by a less SQL-savvy person. I will run it by the person who's
actually using it and see if this way makes enough sense.

Thanks again. I would not have come up with it without your help. Which is
honestly another issue. If I couldn't come up with it on my own should I
expect the other developers here to do so, or at least understand it? I'm
torn...

Frank

First  |  Prev  | 
Pages: 1 2
Prev: using List Tablespaces Show Detail to free pages
Next: Fetching MAX rows with one index scan.