Re: Strange Behaviour (with Test Case)

CHR RATE RUN_TOT
--- ---------- ----------

R03        1.3        .13
R03        1.3        .26
LDU        .21       .281

On 5 August 2015 at 06:39, Venkat Krish <venkat.lear_at_gmail.com> wrote:

> May be some query like this would help see closer on how the exadata is
> arriving at it's value ?
>
> SQL> ed
> Wrote file afiedt.buf
>
> 1 select t.*,
> 2 sum(case when (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
> 3 then rate/10
> 4 end) over(order by rowid) run_tot
> 5* from test_fact t order by rowid
> SQL> /
>
> CHR RATE RUN_TOT
> --- ---------- -------
> R03 1.3 0.130
> R03 1.3 0.260
> LDU .21 0.281
>
>
> Regards
> Venkat
>
>
> On Tue, Aug 4, 2015 at 5:51 AM, Patrick Jolliffe <jolliffe_at_gmail.com>
> wrote:
>
>> I have been working though a strange case, seems specific to Exadata 12c.
>> I have simplified as much as possible for now, testcase below.
>> Obviously we are following up with Oracle support, but wonder if anyone
>> is able to confirm what we are seeing.
>> And/Or anybody want to hazard a guess as to what is going wrong?
>> TIA
>> Patrick
>>
>>
>> CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE NUMBER)
>> INSERT INTO TEST_FACT VALUES('R03', 1.3)
>> INSERT INTO TEST_FACT VALUES('R03', 1.3)
>> INSERT INTO TEST_FACT VALUES('LDU', 0.21)
>> COMMIT;
>>
>> SELECT SUM(
>> CASE
>> WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
>> THEN RATE /10
>> END )
>> FROM TEST_FACT;
>>
>> Result on 11.2.0.4.4 and 12.1.0.2.3 (Linux)
>> 0.281
>>
>> 12.1.0.2.7 (Exadata)
>> -8.70E+103
>>
>
>

--
http://www.freelists.org/webpage/oracle-l