Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Question Unanswered: Dynamic replacement of values in where clause

    Hi,

    I am stuck in modification of a query which requires dynamic replacement in the where clause.

    My sample query is as follows:
    Code:
    select count(1) from Pat where PID=101 and smokerFlg=?
    I am getting the values to be replaced with '?' from the user.
    The condition for replacement of value in ? is

    When I get the value as 'Y', the ? should be replaced with '1'
    When I get the value as 'N', the ? should be replaced with '0'
    When I get the value as null, the ? should be replaced with '0'

    All these conditions have to be handled in a single SQL. I can't write a SP for this.

    Can you please help me how to do this.
    Thanks in advance.

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    Use case statement. What will hold "Y" and "N". Some text box or some variable?

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    How to use CASE over here? I don't want it to be part of my SELECT clause. The condition has to be in the WHERE clause.
    My first part of the query has to be 'select count(1) from Pat' only.

    What will hold "Y" and "N". Some text box or some variable?
    The value comes from the HL7 message, if you know this ... Its a healthcare standard message, which is used for data flow.

    I get the data from this HL7 message as the actual value.
    However am stuck, as I dont want to replace it directly. Also, I cant write the logic in some programming language. All the conditions have to be incorporated in a simple query only..

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    Case will be part of the where clause. What I wanted to know is how are you going to store Y/N received from "HL7" message. If you could tell me then I can post the answer....

    Assuming, you are going to store smoker flag in a variable called @HL7. Below is the solution:

    Code:
    select count(1) from Pat where PID=101 and smokerFlg = case when isnull(lower(rtrim(ltrim(@HL7))),"n") = "y" then 1
                                                                when isnull(lower(rtrim(ltrim(@HL7))),"n") = "n" then 0
                                                            end

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    Thanks Man..
    That solves my problem.....

Posting Permissions

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