Problem with Update

  • liyaka

    SSC Veteran

    Points: 282

    We run sp to insert data into the table.

    The query contains couple of joins ant outer joins.

    When we run the query against the relatively small amount of data - 500000 records it works right, however running it against 3000000 records does not give correct results.

    Have you ever encountered such problem?

    And how you solved it?

    Thank you.

  • guarddata

    SSCertifiable

    Points: 6583

    Outer joins have occasionally caught me off-guard. When the test data didn't have the same join condition as the full set, it was hard to catch the error in my join clause.

    You might get a second pair of eyes to look at the code to see if there is anything you might have overlooked.

    Guarddata-

  • padmakumar_pga

    Right there with Babe

    Points: 716

    Try Join Hints or option with Join .

    The Query Optimizer always tries to identify the fastest way to JOIN tables.

    The fastest JOIN method is the Loop JOIN, followed by the Merge and the Hash JOIN

  • liyaka

    SSC Veteran

    Points: 282

    Thank you.

    here is my statement

    INSERT table1 (col1, col2, col3

    SELECT col1,col2,col3

    FROM rdwMHSmedmas AS l

    JOIN table2 x

    ON l.clm = x.clm

    JOIN table3 AS pl

    l.poscod = pl.poscod

    JOIN view1 AS pg

    ON l.grpnum = pg.grpnum

    LEFT OUTER JOIN table4 AS pr

    ON l.provno =pr.provno

    LEFT OUTER JOIN viewdDup dup

    ON l.claimno = dup.claimno

    WHERE l.brancd = '01'

    AND l.statcd = 'P'

    AND l.unitct > 0

    AND l.dat >= '01/01/2001'

    AND dup.clmnum IS NULL

    This insert should not give me records existing in viewdDup.

    Am I wrong?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply