Re: Strange Behaviour (with Test Case)
Date: Wed, 5 Aug 2015 19:30:30 +0800
Message-ID: <CABx0cSWpA6NpqR9Q_1XDBHQ_Y2t1DYfXX+fFFDC08ABR0=gqKg_at_mail.gmail.com>
Client is same in each case (SQL Developer) but also same in sqlplus. Below the info you requested (from sqlplus):
SQL> SET DEFINE
OFF
SQL> SET LINESIZE
180
SQL> set echo
on
SQL> set time
on
19:27:44 SQL> set timing
on
19:27:44 SQL> set feedback
on
19:27:45
SQL>
19:27:47
SQL>
19:27:47
SQL>
19:27:47 SQL> DROP TABLE
TEST_FACT;
DROP TABLE
TEST_FACT
*
ERROR at line
1:
ORA-00942: table or view does not
exist
Elapsed:
00:00:00.05
19:28:18 SQL> CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE
NUMBER);
Table
created.
Elapsed:
00:00:00.02
19:28:18
SQL>
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('R03',
1.3);
1 row
created.
Elapsed:
00:00:00.02
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('R03', 1.3);
1 row
created.
Elapsed:
00:00:00.00
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('LDU', 0.21);
1 row
created.
Elapsed:
00:00:00.00
19:28:19 SQL>
COMMIT;
Commit
complete.
Elapsed:
00:00:00.00
19:28:19
SQL>
19:28:19 SQL> -- Insert the result into a new table and retrieve the result
from that table
19:28:19 SQL> DROP TABLE
TEST_FACT_RESULT;
DROP TABLE
TEST_FACT_RESULT
*
ERROR at line
1:
ORA-00942: table or view does not
exist
Elapsed:
00:00:00.00
19:28:19 SQL> CREATE TABLE TEST_FACT_RESULT(RATE
NUMBER);
Table
created.
Elapsed:
00:00:00.00
19:28:19
SQL>
19:28:19 SQL> INSERT INTO
TEST_FACT_RESULT
19:28:19 2 SELECT
SUM(
19:28:19 3
CASE
19:28:19 4 WHEN (CHRTYPE = 'R03' OR CHRTYPE =
'LDU')
19:28:19 5 THEN RATE
/10
19:28:19 6 END
)
19:28:19 7 FROM
TEST_FACT;
1 row
created.
Elapsed:
00:00:00.00
19:28:19
SQL>
19:28:19 SQL> SELECT RATE FROM
TEST_FACT_RESULT;
RATE
-8.70E+103
1 row
selected.
Elapsed:
00:00:00.01
19:28:19
SQL>
19:28:19 SQL> -- Retrieve the result
directly
19:28:19 SQL> SELECT
SUM(
19:28:19 2
CASE
19:28:19 3 WHEN (CHRTYPE = 'R03' OR CHRTYPE =
'LDU')
19:28:19 4 THEN RATE
/10
19:28:19 5 END
)
19:28:19 6 FROM
TEST_FACT;
SUM(CASEWHEN(CHRTYPE='R03'ORCHRTYPE='LDU')THENRATE/10END)
-8.70E+103
1 row
selected.
Elapsed: 00:00:00.01
On 5 August 2015 at 18:13, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:
> I'm willing to bet that the problem is not with the database engine but
> with the client. The client has to determine the data type of the result
> and initialize an appropriate memory structure to receive the result.
>
> I'd be interested in seeing the entire output of the following script from
> SQL*Plus (including the banner, exit message, and any error messages).
>
> set echo on
> set time on
> set timing on
> set feedback on
>
> DROP TABLE TEST_FACT;
> 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;
>
> -- Insert the result into a new table and retrieve the result from that
> table
> DROP TABLE TEST_FACT_RESULT;
> CREATE TABLE TEST_FACT_RESULT(RATE NUMBER);
>
> INSERT INTO TEST_FACT_RESULT
> SELECT SUM(
> CASE
> WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
> THEN RATE /10
> END )
> FROM TEST_FACT;
>
> SELECT RATE FROM TEST_FACT_RESULT;
>
> -- Retrieve the result directly
> SELECT SUM(
> CASE
> WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
> THEN RATE /10
> END )
> FROM TEST_FACT;
>
> Cheers,
> Iggy
>
> --
>
> Iggy Fernandez
>
> Email: iggy_fernandez_at_hotmail.com
>
> Cellphone: (925) 478 3161
>
> Blog: Explaining the Explain Plan
> <http://www.toadworld.com/members/iggy_5f00_fernandez/blogs>
>
> Author of Beginning Oracle Database 12*c* Administration
> <http://www.amazon.com/Beginning-Oracle-Database-12c-Administration/dp/1484201949>
>
> Editor of the NoCOUG Journal
> <http://www.nocoug.org/Journal/NoCOUG_Journal_Latest.pdf>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 05 2015 - 13:30:30 CEST