January 15, 2011 at 3:56 pm
What's wrong with my thinking below?
If @ID is provided, I want the WHERE clause to only reflect ID=@CaseID. If not provided then just RMARequestedDate > (GetDate()- @D).
WHERE CASE @ID
WHEN >0 THEN ID=@CaseID
WHEN 0 THEN RMARequestedDate > (GetDate()- @D)
END
thanks!
January 15, 2011 at 4:22 pm
Preferably, I would say have two queries
IF @ID>0
SELECT column_list
FROM Table as Alias
WHERE ID=@CaseID
ELSE
SELECT column_list
FROM Table as Alias
WHERE RMARequestedDate > DATEADD(DAY, 0 - @D, GetDate())
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 16, 2011 at 2:23 pm
Here are two other alternatives:
SELECT
column_list
FROM
MyTable
WHERE
ID = @CaseID
UNION ALL
SELECT
column_list
FROM
MyTable
WHERE
@CaseID = 0
AND RMARequestedDate > DATEADD(DAY, 0 - @D, GetDate());
SELECT
column_list
FROM
MyTable
WHERE
(@CaseID = 0
AND RMARequestedDate > DATEADD(DAY, 0 - @D, GetDate()))
OR (ID = @CaseID);
January 17, 2011 at 9:40 am
shank-130731 (1/15/2011)
What's wrong with my thinking below?If @ID is provided, I want the WHERE clause to only reflect ID=@CaseID. If not provided then just RMARequestedDate > (GetDate()- @D).
WHERE CASE @ID
WHEN >0 THEN ID=@CaseID
WHEN 0 THEN RMARequestedDate > (GetDate()- @D)
END
thanks!
The CASE statement can't return a Boolean value in SQL. You can either move the Boolean expression into the WHEN part of the CASE statement or to the WHERE clause.
WHERE CASE WHEN @ID > 0 AND ID = @CaseID THEN 'True'
WHEN @ID > 0 THEN 'False'
WHEN RMARequestedDate > DateDiff(Day, -@D, GetDate()) THEN 'True'
ELSE 'False'
END = 'True'
In this sample I've used 'True'/'False' to make it clear that these are pseudo-Boolean values being returned, but usually I use 1/0 in actual scripts.
You should also use the DateDiff function instead of plain subtraction.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2011 at 3:27 pm
SELECT
column_list
FROM
MyTable
WHERE
(@CaseID = 0
AND RMARequestedDate > DATEADD(DAY, 0 - @D, GetDate()))
OR (ID = @CaseID);
prodigious!:-)
January 19, 2011 at 1:58 am
Ideally the query should be like this
WHERE ID =
CASE
WHEN @ID >0 THEN @CaseID
WHEN @ID = 0 THEN (RMARequestedDate > (GetDate()- @D))
END
January 19, 2011 at 9:46 am
Deeptiprasad (1/19/2011)
Ideally the query should be like thisWHERE ID =
CASE
WHEN @ID >0 THEN @CaseID
WHEN @ID = 0 THEN (RMARequestedDate > (GetDate()- @D))
END
"(RMARequestedDate > (GetDate()- @D))" is a Boolean expression, so it CANNOT be returned as a value for the CASE statement. That is exactly the problem that the OP was running into.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 21, 2011 at 2:50 pm
WHERE (@ID > 0 And ID=@)
Or (@Id = 0 and RMARequestedDate > (GetDate()- @D)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply