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

    Unhappy Unanswered: IF ELSE statement in WHERE clause

    actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

    i have try to use:

    if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help.....

    ================================================== ==
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
    tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
    FROM tbl_Receipt WITH (nolock) INNER JOIN
    tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
    tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
    tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

    WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND
    (tbl_Receipt.IssueUserID = @IssueUserID)
    ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    A trick I have used in this case would be to code:

    ...
    AND (tbl_Receipt.IssueUserID =
    ISNULL(@IssueUserID, tbl_Receipt.IssueUserID )
    )
    ...

    In other words, if @IssueUserId is not present, you join the column to itself, which gets all the data...

    I've done this in stored procedure reports where I can default the passed parameters, e.g. :

    create proc myproc (... , @IssueUserID int = NULL, ....)

    HTH,

    Bill

  3. #3
    Join Date
    Nov 2004
    Location
    Malaysia
    Posts
    9
    thank you for ur help...

    but when i try to edit the queries, i won't get all the data when the IssueUserID is blank.

  4. #4
    Join Date
    Feb 2004
    Posts
    88
    Quote Originally Posted by wendy_yfl
    thank you for ur help...

    but when i try to edit the queries, i won't get all the data when the IssueUserID is blank.
    so @IssueUserID is a character data type, and when it's "blank" you don't want it to be considered in the where clause...?

    try

    AND ( (@IssueUserID = ' ') OR (@IssueUserID != ' ' AND table.column = @IssueUserID))

    HTH

    Bill

  5. #5
    Join Date
    Nov 2004
    Location
    Malaysia
    Posts
    9
    actually i really appreciate your help. the way u teach is working when i try in SQL Query Analyzer. When comes to the MS.NET reporting, the query is not working..so sad.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Very confusing here whether you are checking for NULLs, zero-length strings ('') or values with just a single space (' '). Your MS.NET reporting layer may be interpreting things differently than Query Analyzer.

    Your code indicates a single space, but that is an unusual value to be submitted by an interface to indicate the absence of data.

    This code handles both NULL values and zero-length strings:

    AND (nullif(@IssueUserID, '') is null OR (nullif(@IssueUserID, '') is not null AND table.column = @IssueUserID))
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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