case statement problem in select

Question

Post reply

case statement problem in select

dulanjan

SSC Rookie

Points: 45
More actions
September 9, 2008 at 9:30 am

#192447

SELECT CASE WHEN h.cloned_wrksht_indcr IS NULL THEN AVG(ABS(CAST(dbo.fn_workdays(h.job_execution_dt,l.creatn_dt) AS MONEY)))
ELSE AVG(ABS(CAST(dbo.fn_workdays(h.job_execution_dt,h.mdfctn_dt) AS MONEY))) END
FROM order_mgmt.dbo.task_wrksht_labor l WITH(NOLOCK)
JOIN order_mgmt.dbo.task_wrksht_hdr h WITH(NOLOCK) ON l.wrksht_hdr_id=h.wrksht_hdr_id
JOIN order_mgmt.dbo.task_job_hdr j WITH(NOLOCK) ON j.job_hdr_id = h.job_hdr_id
WHERE CASE WHEN h.cloned_wrksht_indcr IS NULL THEN h.creatn_dt
ELSE h.mdfctn_dt END BETWEEN @wb_date AND @we_date
AND ISNULL(h.cloned_wrksht_indcr, 'n') <> 'y'
AND LEFT(j.mastr_sls_order_no,2) = @branch
so im trying to implement the above...and i keep getting an error saying cloned_wrksht_indcr is not in an agg or group by clause. .but i dont want to group by cloned_wrksht_indcr!
merely want one set of calculations if its null...and another set of calculations if not...
any help??

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Are you trying to get two rows returned, or one row returned?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Also, are you aware that the NOLOCK query hint can cause miss-reads, duplicate-reads, and reading of uncommitted data? This query hint is very often misused and misunderstood.

dulanjan SSC Rookie Points: 45 More actions · Answer 3

hey earl,

yes you're right...but these are simply on a reporting perspective and my company is such that you dont want to lock up any data while you're trying to extract any data for reporting.

i was planning on returning one value and actually found a solution. i moved the case from the outside to inside the function fn_workingdays. this solved it perfectly.

thank you so much for your input!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

But remember that it is not just preventing locking, it is also able to potentially read the same record multiple times, skip records that are in the table, and read records that will be later rolled back.

Using snapshot isolation is much safer for ensuring data integrity and it also eliminates blocking. It does add some other overhead, but is often a better solution.