Resultset way too large

  • My query below is returning an unusally large number of results.  1,225,686 to be exact.  This number far exceeds the amount of records in both the tables I am pulling from(a total of about 8,000).

    For the life of me, I can't figure out what is going on.  Many of the results are duplicated 100's of times...which is why I'm getting over a million total results.

     

    My query looks like this:

    SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay INTO #temptable

    FROM Diaryentry, workrequest

    WHERE 1=1

    AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'

    AND DEentryTYPEID='ENRPAY'

    --

    When I do a SELECT * FROM #temptable...is where I get the million results.

    Any thoughts?

    M

     

     

     

  • It's the same as doing a cross join (

    SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay

    FROM Diaryentry cross join workrequest

    WHERE WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'

    AND DEentryTYPEID='ENRPAY'

    )

    You'll need to use an inner join to filter out the previous statement :

    SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay

    FROM Diaryentry inner join workrequest on Diaryentry.id = workrequest.id

    WHERE WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'

    AND DEentryTYPEID='ENRPAY'

  • Thank you Ten Centuries(cool name btw)...but the reason I need the above code is because I am using the #temptable as part of a larger query.  Specifically....

    SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay INTO #temptable FROM diaryentry, workrequest

    WHERE 1=1

    AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'

    AND DEentryTypeID='ENRPAY'

    GO

    SELECT WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, WRsendToID, WRbillToID, WRrequestorID,

    IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',

    IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',

    IsNull(req.COcontactNameLast,'NA') AS 'Client Rep',

    DEaltKeyValue=CASE

    WHEN PayDay IS NOT NULL

    THEN (SELECT DEaltKeyValue AS TotPaid)

    WHEN WRcurrentStatus=110

    THEN (SELECT DEaltKeyValue AS TotCancel)

    WHEN PayDay IS NOT NULL OR WRcurrentStatus=110

    THEN (SELECT DEaltKeyValue AS TotOpen)

    END

    FROM workrequest

    LEFT JOIN #temptable AS cnt ON WRid=JobNumber

    LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid

    LEFT JOIN clientoffice billto ON WRsendToID=billto.COid

    LEFT JOIN clientoffice req ON WRsendToID=req.COid

    , diaryentry

    WHERE 1=1

    AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'

    AND DEkeyValue=WRid

    AND diaryentry.DEentryTypeID='ENRINV'

    AND DEdeletedDate Is Null

    AND WRjobTypeID <> 'TRJTINT'

    GO

  • M,

    I think if you please re-read Remi's post the problem is you are not telling SQL how the 2 tables relate to each other Diaryentry, workrequest

    What you are getting is for EVERY ENTRY in BOTH tables EVERY SINGLE COMBINATION for EACH ROW.

    You will need to tell SQL how Diaryentry, workrequest

    should be joined.  Once you tell it that you should get the # of records you are expecting....

    Hope this helps......



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • I understand that, but the fact is that the query is still wrong. You can use select into in combinaison with the inner join.

    Also if this is something that will go in production, I would strongly suggest that you first create the table, then insert into it so it doesn't lock up the system tables while the insert is made. I would also have a look at the table variable type.

    One last thaught is that unless you are going to reuse that result set again in the proc, you don't need to put it in a temp table.

  • BTW my name is Remi, ten centuries is because I posted 1000+ message. Just like you'll be flagged as a newbie untill you post your 10th message.

  • Yeah, I figured that out Remi. Thanks for your quick responses....

     

  • HTH.

  • What about the WHERE 1 = 1 part...? Seems a bit redundant to me.

    /Kenneth

  • I just used that so I can keep writing AND queries(because 1 will always equal 1)....I guess it does seem like a strange way to approach it.

     

  • Not really a strange way.. but I would suggest you try to write a stored proc to resolve this search problem Read this for more details : Dynamic Search Conditions

  • "...WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'..."

    Plesase note that BETWEEN is inclusive so unless you really intend to include rows with a date of '2005-05-20 00:00:00' you should change that to:

    ...WRenteredDate >= '2005-04-03'

    AND WRenteredDate < '2005-05-20'...

     

  • I'll remember to check for that someday... good thing you're there .

  • A little late, but the term describing the initial SQL statement is 'almost' a 'cartesian join' ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 14 posts - 1 through 13 (of 13 total)

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