Conditional where statement

[zsyed]

Having a syntax error and can someone help?

select imtpartid, imtCreatedDate from parttransactions where imtpartid = 'XYZ' and imtpartrevisionid = 'A' and (case imtsource when 9 then (imtCreatedDate = (select max(A.imtCreatedDate) from parttransactions A where A.imtpartid = parttransactions.imtpartid and A.imtpartrevisionid = parttransactions.imtpartrevisionid) and imtTransactionType = 2)
else (imtCreatedDate = (select max(A.imtCreatedDate) from parttransactions A where A.imtpartid = parttransactions.imtpartid and A.imtpartrevisionid = parttransactions.imtpartrevisionid)) end)

 

[philhege]

This may get you started down the right path. I don't vouch for the last part of the statement, and this was not tested against any dataset, just parsed for syntax.

select imtpartid, imtCreatedDate from parttransactions 
where imtpartid = 'XYZ' and imtpartrevisionid = 'A' and 
	imtCreatedDate = 
	(case when imtsource = 9 
		then 
		 
			(select max(A.imtCreatedDate) 
			from parttransactions A 
			where A.imtpartid = parttransactions.imtpartid 
			and A.imtpartrevisionid = parttransactions.imtpartrevisionid)
		 --and caseimttransactiontype = 2
		else 
	
		(select max(A.imtCreatedDate) 
		from parttransactions A 
		where A.imtpartid = parttransactions.imtpartid 
		and A.imtpartrevisionid = parttransactions.imtpartrevisionid) 
	end)
		and caseimtTransactionType = case when imtsource = 9
		then
		 2
		 else
		 caseimtTranscationType
		end

-----------
With business clients like mine, you'd be better off herding cats.