Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Location
    Malaysia
    Posts
    9

    Unhappy Unanswered: Is it possible to put in "IF ...ELSE" or "Case" in WHERE CLAUSE?

    Dear all...

    need your help... i am now trying to create a report using SQL reporting services... when declare all the @parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.

    select..................(blah blah)......
    ......(SELECT CASE WHEN
    ((SELECT COUNT(*)
    FROM tbl_OutpatientReg OPT
    WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType
    ............................(blah blah).......

    where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @FromDate AND @ToDate) OR (@FromDate = ' ') OR (@ToDate = ' ')
    AND (@PatientType = CASE WHEN
    (SELECT COUNT(*)
    FROM tbl_OutpatientReg OPT
    WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)



    my situition is something like above, i know i have done something wrong in teh WHERE clause for the @PatientType.... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...


    thanks in advanced ...................

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your post is kind of confusing regarding requirements, but part of your problem may be due to using brackets where they are not necessary, and not using them where they might be necessary to specify logical operations.


    Code:
    --Your Version:
    where	(CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @FromDate AND @ToDate)
    	OR (@FromDate = ' ')
    	OR (@ToDate = ' ') 
    	AND (@PatientType = CASE
    		WHEN	(SELECT	COUNT(*)
    			FROM	tbl_OutpatientReg OPT
    			WHERE	OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
    		ELSE 1
    		END)
    
    --Unnecessary brackets removed:
    where	CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @FromDate AND @ToDate
    	OR @FromDate = ' '
    	OR @ToDate = ' '
    	AND @PatientType = CASE
    		WHEN	(SELECT	COUNT(*)
    			FROM	tbl_OutpatientReg OPT
    			WHERE	OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
    		ELSE 1
    		END
    
    --Useful brackets added:
    where	(CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @FromDate AND @ToDate
    	OR @FromDate = ' '
    	OR @ToDate = ' ')
    	AND @PatientType = CASE
    		WHEN	(SELECT	COUNT(*)
    			FROM	tbl_OutpatientReg OPT
    			WHERE	OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
    		ELSE 1
    		END
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    Malaysia
    Posts
    9
    ya...thanks for reminding me...as there are too many parameters to pass, i also confused... anyway, really appreciate ur help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •