Check whether no record for join condion

Posted Date: 20 Mar 2009 Posted By:: Babu Kumarasamy Member Level: Gold Member Rank: 1161 Points: 1 Responses: 1

Hi,

My requirement is that if there is no record in Log Table for the Batch Table Join Condition Log.ID = Batch.ID AND Log.LogDT = Batch.BatchDT, Then we have to get the Batch.BatchValue when

COUNT(Log.totalHrs) = 0
We have to check whether this is null or empty i.e. there is no record for the condition in Log table. This is thing I need.

else if there is record for Id and the BatchDT we get the min of Log.LogValue

totalHrs DataType is Time

SELECT Batch.BatchHrs,
Hrs =
CASE
WHEN COUNT(Log.totalHrs) = 0
OR Log.TotalHrs = '00:00:00.0000000'
THEN Batch.BatchValue
ELSE MIN(Log.LogValue)
END
FROM Batch
LEFT JOIN Log
ON Log.ID = Batch.ID
AND Log.LogDT = Batch.BatchDT
WHERE Batch.ID = '01'
AND Batch.BatchDT = '3/20/2009'
GROUP BY Batch.BatchHr

Thanks,
Babu Kumarasamy

Responses

#370773 Author: Abhishek Arya Member Level: Gold Member Rank: 21 Date: 26/Apr/2009 Rating: Points: 2
you can use if condtion too for checking the value of count. or User define function which will me more fater.

Post Reply

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

	Next : Connection pooling
	Previous : Doubt in stored procedure
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages