#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    You could try creating the table with a simple CREATE TABLE script, and then do an INSERT INTO/SELECT instead.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    850
    Rep Power
    387

    Cool


    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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?

IMN logo majestic logo threadwatch logo seochat tools logo