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