Create Table Not Replicating SQL Select Results

Dev Shed Forums Sponsor:

May 10th, 2012, 08:10 AM

amstel_za

Contributing User

Join Date: Feb 2004

Posts: 378

Time spent in forums: 5 Days 14 h 38 sec
Reputation Power: 10

Create Table Not Replicating SQL Select Results

Hey all,

This is a very odd one...never encountered this before within SQL Developer

I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:

SQL Select:

COL1 COL2 COL3 COL4
___________________________________
AND 10200000017805 CG-4 CG-3

Create Table as <SQL Select>:

COL1 COL2 COL3 COL4
___________________________________
AND 10200000017805 CG-3 CG-4

The SQL Select is correct and the Create Table As <SQL Select> is wrong.

Here is my SQL:

Code:

Create table  ALTERNATENUMBERS as
SELECT ctry,
  id,
  MAX(DECODE(tp,'EN', RN)) EN,
  MAX(DECODE(tp,'RN', RN)) RN,
  MAX(DECODE(tp,'AN', RN)) AN
FROM
  (SELECT *
  FROM
    (SELECT ctry,
      id,
      tp,
      trn,
      listagg(routenum, '/') within GROUP (
    ORDER BY routenum) over (partition BY id, tp) RN
    FROM
      (SELECT ctry,
        id,
        routenum,
        rteprior,
        trn,
        CASE
          WHEN rtetyp= 5
          THEN 'EN'
          WHEN (rtetyp <> 5
          AND trn       =1)
          THEN 'RN'
          ELSE 'AN'
        END AS tp
      FROM
        (SELECT mn_rn.*,
          row_number() over (partition BY id order by lengthb(routenum) ASC, rteprior) trn
        FROM MN_RN
        )
      ) 
    )
  GROUP BY ctry,
    id,
    tp,
    rn,
    trn
  ORDER BY ctry,
    id,
    tp,
    rn,
    trn
  )
GROUP BY ctry,
  id
ORDER BY ctry,
  id

Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.

Now, I've tried:

1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables

It's very odd and I do understand that without data it's hard to replicate the issue but does anyone have any ideas as to why this statement works as a SELECT but when written to a table has data anomolies ?

Regards,
Ben

May 10th, 2012, 08:56 AM

amstel_za

Contributing User

Join Date: Feb 2004

Posts: 378

Time spent in forums: 5 Days 14 h 38 sec
Reputation Power: 10

Hey all,

Just an update to my previous post:

This command:

Code:

SELECT mn_rn.*,
  row_number() over (partition BY id order by lengthb(routenum) ASC, rteprior) trn
FROM MN_RN

orders the records correclty, but when I create a table of that select statement-the ordering is reversed and incorrect.

SO it appears the entire statement is being affected by the first SQL Select statment...

So the question is why the create table command doesn't replicate the SQL statement when using ROW_NUMBER OVER...

Last edited by amstel_za : May 10th, 2012 at 09:05 AM.

May 11th, 2012, 11:43 AM

joeflinthiggins

Registered User

Join Date: May 2012

Posts: 14

Time spent in forums: 4 h 20 m 33 sec
Reputation Power: 0

You could try creating the table with a simple CREATE TABLE script, and then do an INSERT INTO/SELECT instead.

May 11th, 2012, 01:45 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 752

Time spent in forums: 1 Week 3 h 5 m 12 sec
Reputation Power: 348

Quote:

Originally Posted by amstel_za

...Etc ... question is why the create table command doesn't replicate the SQL statement when using ROW_NUMBER OVER...

Oracle does NOT store the rows in any particular order.

__________________

May 11th, 2012, 02:54 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Following up on what LKBrwn_DBA explained:

Are you sure that the column results are truly being reversed?
Any chance you are not comparing the rows you think you are comparing?

Create Table Not Replicating SQL Select Results

Developer Shed Advertisers and Affiliates