Falling into infinite loop...

Question

Falling into infinite loop...

Bagath

SSCrazy

Points: 2409
More actions
August 30, 2006 at 6:38 am

#114627

The below query is falling into infinite loop....where is the problem? can any one help me?
SELECT DEAL_DATE=CASE
WHEN(SELECT MAX(DEAL_DATE) FROM DATA_PURCHASE WHERE DEAL_DATE BETWEEN GETDATE()-5 AND GETDATE())<>NULL
THEN(SELECT MAX(DEAL_DATE) FROM DATA_PURCHASE WHERE DEAL_DATE BETWEEN GETDATE()-5 AND GETDATE())
ELSE
(SELECT MAX(DEAL_DATE) FROM DATA_PURCHASE)
END
FROM DATA_PURCHASE
any suggestion would be very helpful to me....
Rao Aregaddan.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

First you should not use <> null; you should use "is not null" (you can search for articles on this matter on this site... there must be like 5-10 of 'em).

Second, can you show us what you need to do exactly?

Sample data, required result from that data will be essential if you want a quick answer.

Bagath SSCrazy Points: 2409 More actions · Answer 2

Ya i have posted one query with the subject "needs a single query for selecting dates..." today itself only.

That is only my req.I have wrote one query like this but it is falling into infinite loop and getting the result.

Thanks,

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here's the other post:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=304896

I don't think it's a "falling into infinite loop" issue - I think it may have just been selecting the result for every row into your table. But anyway, see my suggestion in the other thread.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

SQLZ SSChampion Points: 12872 More actions · Answer 4

Rao,

The query you've written is not falling into an infinite loop at all. It might take a long time but that's all. You're basically trying to calculate the MAX(Deal_Date) for every row in Data_Purchase, which doesn't make sense.

If I'm not mistaken what you want is the MAX(Deal_Date) that lies between 5 days ago and today or, if there is no max between those dates, the overall MAX(Deal_Date).... correct me if I'm wrong.

So why don't you do:

IF

EXISTS(SELECT 1 FROM DATA_PURCHASE WHERE DEAL_DATE BETWEEN GETDATE()-5 AND GETDATE())

SELECT MAX(DEAL_DATE) FROM DATA_PURCHASE WHERE DEAL_DATE BETWEEN GETDATE()-5 AND GETDATE()

ELSE

SELECT MAX(DEAL_DATE) FROM DATA_PURCHASE

Karl
source control for SQL Server

Bagath SSCrazy Points: 2409 More actions · Answer 5

Ya you are right i want exactly that only....

Finally i have got the query from our team only....

Thanks for ur valuable suggestion...

Rao Aregaddan.