SQL Server Performance Forum – Threads Archive
How to compare expression with 1
I want to compare the sql query with the value 1example : (Select count(*) from BatchSummary where batch_status = 2 having count(*) =1 ) = 1 ? how it would be possible ?. I would like to put this expression in Check Constraint expression. Please guide me Thanks in advance. Regards
Vyom
Comparison can be done with IF and CASE statements.
e.g.
IF ((Select count(*) from abc having count(*) =1 ) = 1) can you please post what do you want to do with this comparison???
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
or Declare @c int
Select @c=count(*) from abc where batch_status = 2 having count(*) =1
If @c=1
—–
else
—– Madhivanan Failing to plan is Planning to fail
Your query doesn’t make much sense without a GROUP BY.
However, if all you want is to check for existance what about using EXISTS() instead of COUNT(*)? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Thanks a lot for the reply. My question is I have field called Batch_status and I want that field should have a values either null or 2 or 3 and it have only one 2 in all rows. field doesn’t have more than one 2 in the field. therefore I need to define that check constrain for batch_status. I hope you understand the problem well now. Please provide the solution accordingly. Thanks in advance.
Vyom
Hi ya, To be honest it sounds like this has been mismodelled slightly… if there is only ever one row with that status, then perhaps it is not really an attribute of the row but an attribute of a parent object e.g. parent
child( status ) where status 1 = to be run, 2 = running and 3 = done and the condition is that only one row can ever be running the alterative is to change it to
parent( running_child )
child( status ) where status 1 = to be run and 3 = done running_child being a nullable column pointing to the currently running child Cheers
Twan
]]>