Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2006
    Posts
    14

    Unanswered: Different row count for select versus insert

    The short version of this is that I have a situation where a SELECT * returns a different rows count than an INSERT INTO... (SELECT *). This makes no sense at all!!!

    I am fairly new to Oracle, but I have been writing queries for DB2 and (occassionally) SQL Server for roughly 10 years, and this problem is new to me.

    For years (supposedly), this process has worked and decided to stop working once I got onto this project.

    THE PROBLEM:

    Running SELECT SYSDATE, COL1, COL2, ... COL50 FROM PSEUDOVIEW returns 156 rows.

    Running INSERT INTO PSEUDOTABLE (SELECT SYSDATE, COL1, COL2, ... COL50 FROM PSEUDOVIEW) returns 170 rows.

    The extra 14 rows create a primary key violation on PSEUDOTABLE - who's primary key is COL0 - populated by SYSDATE - + COL1.

    THE CLUES AND FACTS:

    Like I said, the extra 14 rows create a primary key violation on PSEUDOTABLE - who's primary key is COL0 - populated by SYSDATE - + COL1. However, the extra rows are caused by erroneous values in (let's say) COL50.

    PSEUDOVIEW is a comprised of a query that INNER JOINS / LEFT JOINS about 20 tables. COL50 is retrieved from a minor table that is joined via a LEFT JOIN


    Obviously, I am not happy. Any help you can offer would be greatly appreciated.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Is it possible that you're pointing to 2 different copies of PSEUDOVIEW? Are you running both of these statements from SQL*Plus, one right after the other? Or are they running from different programs/context (ie the SELECT in TOAD, and the INSERT from an application)?

    ---=Chuck

  3. #3
    Join Date
    Mar 2006
    Posts
    14
    Nope - not possible. If you comment out the INSERT INTO PSEUDOTABLE portion and just run the SELECT FROM PSUEDOVIEW, you get different results.

    I have run this by 3 or 4 people in house who also find this weird - they double checked for syntax and consistency in query components (object names).

  4. #4
    Join Date
    Mar 2006
    Posts
    14
    P.S. I am running this from PL/SQL developer, but I have gotten the same results running this from SQLPLUS

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    What happens when you run the SQL which is used to construct the VIEW as a raw SELECT, then as an INSERT INTO (SELECT...)?

    ---=Chuck

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Running INSERT INTO PSEUDOTABLE (SELECT SYSDATE, COL1, COL2, ... COL50 FROM PSEUDOVIEW) returns 170 rows.

    >The extra 14 rows create a primary key violation on PSEUDOTABLE - who's primary key is COL0 - populated by SYSDATE - + COL1.

    Please provide a complete SQL*Plus session via CUT & PASTE that substantiates the 2 statements above.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2006
    Posts
    14
    intersesting..... performing the INSERT using the SELECT query that comprises PSEUDOVIEW inserts the proper number of rows

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Do what anacedent asked, just so eveyone can take a gander at your SQL*Plus output...

    but then also search metalink for your version of Oracle, and "view" and "insert", or something like that. Perhaps it'll pop-up a bug report?

    ---=cf

  9. #9
    Join Date
    Mar 2006
    Posts
    14
    sorry about the delay - after running the INSERT using the actual SQL from the view, INSERT worked. But before the band starts playing, this problem has one more issue: this insert is called from within a package, and that package still fails when it tries to perform that insert.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >that package still fails when it tries to perform that insert.
    While the statement above is likely true, it provides readers NO meaningful information.
    My car fails to go.
    Please tell me how to make my car go.

    Are you unwilling or incapable of posting a CUT & PASTE of EXACTLY what is happening rather than you trying to describe what you think is or is not happening?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Mar 2006
    Posts
    14
    Thanks for your help last week.

    No, I was not trying to have you fix my car by saying "my car is broken - fix my car". I was more trying to say "my car is making this strange noise (inconsistent row counts) - have you heard of anything like this".

    I never got a chance to include the CUT and PASTE because I had just finished a work-around, and had to get that elevated immediately before I resume this fact-finding mission. Besides that, I had gotten to a point (between testing what you guys were suggesting and testing the work around) where I could not quickly reproduce the problem.

    At any rate, below is my cut/paste from SQL developer.

    Statement 1 is what I get when I run the work-around.
    Statement 2 is what I get when I run the original (reinstate) view.
    Statement 3 is what I get when I run the insert statement against the text from the CREATE VIEW statement (as suggested last week). I DID NOT include the whole statement because a) I am uncomfortable putting that data in a public forum on the web, and b) the CREATE VIEW statement is about 50 lines of WHERE/JOIN clauses and it would take me more time to explain it than I would be willing to invest.
    SQL>
    1) insert into liqrpt.act_part_contract
    (select sysdate, t.* from liqrpt.liq_fac_contract t);

    156 rows inserted

    Executed in 5.985 seconds

    SQL>
    2)
    insert into liqrpt.act_part_contract
    (select sysdate, t.* from liqrpt.liq_fac_contract t);

    170 rows inserted

    Executed in 0.687 seconds

    SQL>
    3)
    insert into liqrpt.act_part_contract
    SELECT DISTINCT sysdate, .....
    156 rows inserted

    Executed in 5.156 seconds

    SQL>

  12. #12
    Join Date
    Mar 2010
    Posts
    1
    Hi.. did you actually find out what was the reason for this ???

    Quote Originally Posted by bigal21 View Post
    Thanks for your help last week.

    No, I was not trying to have you fix my car by saying "my car is broken - fix my car". I was more trying to say "my car is making this strange noise (inconsistent row counts) - have you heard of anything like this".

    I never got a chance to include the CUT and PASTE because I had just finished a work-around, and had to get that elevated immediately before I resume this fact-finding mission. Besides that, I had gotten to a point (between testing what you guys were suggesting and testing the work around) where I could not quickly reproduce the problem.

    At any rate, below is my cut/paste from SQL developer.

    Statement 1 is what I get when I run the work-around.
    Statement 2 is what I get when I run the original (reinstate) view.
    Statement 3 is what I get when I run the insert statement against the text from the CREATE VIEW statement (as suggested last week). I DID NOT include the whole statement because a) I am uncomfortable putting that data in a public forum on the web, and b) the CREATE VIEW statement is about 50 lines of WHERE/JOIN clauses and it would take me more time to explain it than I would be willing to invest.
    SQL>
    1) insert into liqrpt.act_part_contract
    (select sysdate, t.* from liqrpt.liq_fac_contract t);

    156 rows inserted

    Executed in 5.985 seconds

    SQL>
    2)
    insert into liqrpt.act_part_contract
    (select sysdate, t.* from liqrpt.liq_fac_contract t);

    170 rows inserted

    Executed in 0.687 seconds

    SQL>
    3)
    insert into liqrpt.act_part_contract
    SELECT DISTINCT sysdate, .....
    156 rows inserted

    Executed in 5.156 seconds

    SQL>

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Hi.. did you actually find out what was the reason for this ???
    I doubt after almost 3 years you'll get a response from bigal21.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •