Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

    Unanswered: identify rejected row

    load from cursor is rejecting a row:

    SQL1194W The value in row "192774" corresponding to target column number "17"


    The dumpfile modifier can't be used with the cursor filetype. Is there a way to find out the rejected row by querying the source table for row #192774? Maybe using rownum or some other option? The source table is too large to unload to a file.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    export with "fetch first 192774 rows" to a file ?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by BELLO4KA View Post
    querying the source table for row #192774? Maybe using rownum or some other option?
    I guess it may only work if you explicitly order the cursor. I'd rather figure out why those values don't go in and find them by other means. What is the problem with source values?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    The column in question is varchar. I think the input value is longer than the target column. Loading from cursor will reject it (sql1194w), but loading from file will truncate it (sql3125w).


    Question related to row_number:

    $ db2 "select * from test"

    C1 C2
    ----------- -----------
    1 1
    2 2
    3 3
    4 4
    9 9
    5 5

    6 record(s) selected.



    I want to sort by c1 and then select row #5:


    $ db2 "select c1, c2 from (select row_number() over(order by c1) as rownum, t.* from test t) as t2 where rownum = 5"

    C1 C2
    ----------- -----------
    5 5

    1 record(s) selected.



    I'm sure there is a better way to write this query. Could someone suggest how this can be re-written?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How'bout "select * from test where length(longcol) > 15" (or whatever the target column length is)?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    I like your suggestion, but the table is very large.


    I think they already found the row by exporting with fetch first... or some other way. Going back to row_number. Is there a better way to write this query?

Posting Permissions

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