July 25, 2005 at 7:59 am
I have query that is conditional based on an input parameter. The query works for 0 or 1 but doesn't work when I combine them. Keeps saying can't convert '0,1' to datatype of int. Can someone show me a casting method or the correct usage here?
DECLARE @filter nvarchar(25)
SET @filter = 'All'
SELECT *
FROM myTable
WHERE Deleted IN (CASE @filter
WHEN 'All' THEN '0,1' --fails
WHEN 'Active' THEN '0' --works
WHEN 'Inactive' THEN '1' --works
END)
Thanks,
Chris
July 25, 2005 at 8:05 am
SELECT *
FROM myTable
WHERE @filter = CASE WHEN Deleted = 0 THEN 'Active'
WHEN Deleted = 1 THEN 'Inactive'
WHEN Deleted IN (0, 1) THEN 'All'
Regards,
gova
July 25, 2005 at 8:20 am
No,
Deleted is a bit field. The only values can be 0 1 or 0,1. The CASE statement in this query will put strings into the conditional
July 25, 2005 at 8:22 am
IF Deleted is a BIT field it can ONLY be 0 or 1 and NOT 0,1 ?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 25, 2005 at 8:27 am
BIT field can be 0,1...such as:
SELECT *
FROM myTable
WHERE Deleted IN (0,1)
This works fine. The problem I'm having is the CASE statement recognizing '0,1' as a list of bits and not a string.
July 25, 2005 at 8:29 am
My Mistake. Check This.
DECLARE @MyTable TABLE
(
MyVal Varchar(100),
Deleted BIT
)
INSERT INTO @MyTable VALUES ('MyVal1', 0)
INSERT INTO @MyTable VALUES ('MyVal2', 1)
INSERT INTO @MyTable VALUES ('MyVal3', 0)
INSERT INTO @MyTable VALUES ('MyVal4', 1)
INSERT INTO @MyTable VALUES ('MyVal5', 0)
INSERT INTO @MyTable VALUES ('MyVal6', 1)
DECLARE @filter VARCHAR(100)
SET @filter = 'All'
SELECT *
FROM @myTable
WHERE Deleted = CASE WHEN @filter = 'All' THEN Deleted
WHEN @filter = 'Inactive' THEN 0
WHEN @filter = 'Active' THEN 1 END
SET @filter = 'Active'
SELECT *
FROM @myTable
WHERE Deleted = CASE WHEN @filter = 'All' THEN Deleted
WHEN @filter = 'Inactive' THEN 0
WHEN @filter = 'Active' THEN 1 END
SET @filter = 'Inactive'
SELECT *
FROM @myTable
WHERE Deleted = CASE WHEN @filter = 'All' THEN Deleted
WHEN @filter = 'Inactive' THEN 0
WHEN @filter = 'Active' THEN 1 END
Regards,
gova
July 25, 2005 at 8:33 am
Ahhhh.....yeah that's it.
Instead of trying to force the value using IN you can just the set column equal to itself.....should have known that one.
Thanks a bunch!
July 25, 2005 at 8:37 am
HTH
Regards,
gova
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply