Re: Strange behavior of subquery

From: Hans Mayr <mayr1972_at_gmx.de>
Date: Mon, 25 Feb 2008 06:59:12 -0800 (PST)
Message-ID: <bd863cd7-f011-4358-9fcc-89da7a024ce7@q70g2000hsb.googlegroups.com>

Hello David,

Thanks for your efford. Here is an example:

v_tmp_base contains the columns ob_date date, prod varchar2, T0 number, T1 number, DIFF number.

1. Simple version of my query with correct result:

WITH ex_average AS

        (SELECT oth_months.last_day ob_date,
                v_tmp_base.prod            ,
                SUM(v_tmp_base.T0) T0      ,
                SUM(v_tmp_base.T1) T1      ,
                SUM(v_tmp_base.DIFF) DIFF  ,
                SUM(v_tmp_base.DIFF)/SUM(v_tmp_base.T0) ex_RATE
        FROM    oth_months,
                v_tmp_base
        WHERE   v_tmp_base.ob_date              <= oth_months.last_day
            AND add_months(v_tmp_base.ob_date,6) > oth_months.last_day
            AND prod                             = 'ABC'
        GROUP BY oth_months.last_day,
                v_tmp_base.prod
        HAVING oth_months.last_day <= MAX(v_tmp_base.ob_date)
        ORDER BY oth_months.last_day,
                v_tmp_base.prod
        )

SELECT *
FROM ex_average
WHERE 1=1 Result:

30.09.07 ABC 2114777775,14 2082990331,4 31787443,74 0,0150311035578646770589874130920170854203 31.10.07 ABC 4286417823,59 4223015591,38 63402232,21 0,0147914260390273341388572032489130143492 30.11.07 ABC 6505336028,04 6407051268,97 98284759,07 0,015108329323245170699285235012002763618 31.12.07 ABC 8756944214,99 8628665346,64 128278868,35 0,014648816436493250878196319823053933112 31.01.08 ABC 11017854284,23 10848230250,51 169624033,72 0,0153953782056080660190286960973955280436

B) Extended Version with wrong result

WITH ex_average AS

        (SELECT oth_months.last_day ob_date,
                v_tmp_base.prod            ,
                SUM(v_tmp_base.T0) T0      ,
                SUM(v_tmp_base.T1) T1      ,
                SUM(v_tmp_base.DIFF) DIFF  ,
                SUM(v_tmp_base.DIFF)/SUM(v_tmp_base.T0) ex_RATE
        FROM    oth_months,
                v_tmp_base
        WHERE   v_tmp_base.ob_date              <= oth_months.last_day
            AND add_months(v_tmp_base.ob_date,6) > oth_months.last_day
            AND prod                             = 'ABC'
        GROUP BY oth_months.last_day,
                v_tmp_base.prod
        HAVING oth_months.last_day <= MAX(v_tmp_base.ob_date)
        ORDER BY oth_months.last_day,
                v_tmp_base.prod
        ),
        ex_average_first AS
        (SELECT ex_average.ob_date,
                ex_average.prod   ,
                ex_average.T0     ,
                ex_average.T1     ,
                ex_average.DIFF   ,
                ex_average.ex_RATE
        FROM    ex_average,
                (SELECT MIN(ob_date) min_date
                FROM    ex_average
                ) ex_average_min
        WHERE   ex_average.ob_date = ex_average_min.min_date
        ),
        ex_average_last AS
        (SELECT ex_average.ob_date,
                ex_average.prod   ,
                ex_average.T0     ,
                ex_average.T1     ,
                ex_average.DIFF   ,
                ex_average.ex_RATE
        FROM    ex_average,
                (SELECT MAX(ob_date) max_date
                FROM    ex_average
                ) ex_average_max
        WHERE   ex_average.ob_date = ex_average_max.max_date
        )

SELECT *
FROM ex_average
WHERE 1=1 UNION ALL SELECT *
FROM ex_average_first
WHERE 1=0 UNION ALL SELECT *
FROM ex_average_last
WHERE 1=0 Result:

31.08.07 ABC 2114777775,14 2082990331,4 31787443,74 0,0150311035578646770589874130920170854203 30.09.07 ABC 4286417823,59 4223015591,38 63402232,21 0,0147914260390273341388572032489130143492 31.10.07 ABC 6505336028,04 6407051268,97 98284759,07 0,015108329323245170699285235012002763618 30.11.07 ABC 8756944214,99 8628665346,64 128278868,35 0,014648816436493250878196319823053933112 31.12.07 ABC 11017854284,23 10848230250,51 169624033,72 0,0153953782056080660190286960973955280436

The first column has changed its values by one month.

OTH_MONTHS is a simple table which makes it easier for me to do some date manipulation, it looks like that:

"PERIOD" NUMBER(6,0) NOT NULL ENABLE,
"FIRST_DAY" DATE NOT NULL ENABLE,
"LAST_DAY" DATE NOT NULL ENABLE,
"NEXT_PERIOD" NUMBER(6,0) NOT NULL ENABLE
The columns should be selfexplaining, however I only use LAST_DAY in this example.

What have I done wrong? Or is Oracle mixing something up? There might be more elegant ways to calculate the average over six months than the way I do it, same for the first and the last values, but I do not see why it should not work the way I do it.

Best,

Hans Received on Mon Feb 25 2008 - 08:59:12 CST