Post reply

Urgent Question

  • November 20, 2006 at 12:45 pm

    #171285

    HI ,
    I have a report parameter that will give choice to the user has to pick minimum 1  and maximun 4 value from 4  choice as a input to run the report.For example if the user picked 1 option i have to assign other values as not NULL.Because my qurry is ANDing all input to get the result.For example if A,B,C,D are the user input,My qurry is doing A AND B AND C AND D.

    If any one input is null the result will become NULL.I can't substitute OR instead of AND .

    Can you please help me to assign a default vale NOT NULL ,but it shouln't affect my requirement.

    Thanks

    Robinson

  • November 20, 2006 at 12:50 pm

    #673044

    Select ColLIst FROM dbo.TableName where SomeCol IN (ISNULL(@p1, 0), ISNULL(@p2, 0), ISNULL(@p3, 0), ISNULL(@p4, 0))

  • November 20, 2006 at 2:48 pm

    #673076

    Hi Ninja,

    I am soo thankful for your reply.

    After i updateg your logic  I am getting the following error.

    Could not generate a list of fields for the query.

    Check the query syntax, or click Refresh Fields on the query toolbar.

    ------------------------------

    ADDITIONAL INFORMATION:

    Invalid column name 'SomeCol'.

    Invalid column name 'SomeCol'.

    Invalid column name 'SomeCol'. (Microsoft SQL Server, Error: 207)

     

    Could you please help me???????

    Thanks

    Rob

     

     

     

     

  • November 20, 2006 at 8:20 pm

    #673119

    Replace somecol with the list of columns you want to display.

  • November 20, 2006 at 10:01 pm

    #673128

    Thank you very much for your answer.

    I tried your approach. The only hiccup that I face here is that the if the user does not give Value A then the value A defaults to 0.  So the query looks for A with value 0, which is not desirable. What I'm looking for is value A not being considered when the query is run. To simply put it the value A should disappear from the query and no longer should pose any constraint on the dataset.

    Please let me know if you have any suggestions.

    Thanks

    Rob

     

  • November 21, 2006 at 3:06 am

    #673140

    Select ColLIst FROM dbo.TableName

    Where

    (Case When @Par1 Is Null then 1 When @Par1=SomeCol1 then 1 Else 0 End)=1

    And

    (Case When @Par2 Is Null then 1 When @Par2=SomeCol2 then 1 Else 0 End)=1

    And

    (Case When @Par3 Is Null then 1 When @Par3=SomeCol3 then 1 Else 0 End)=1

    And

    (Case When @Par4 Is Null then 1 When @Par4=SomeCol4 then 1 Else 0 End)=1

  • November 21, 2006 at 10:38 am

    #673266

    Thank you so much for your help.I changed my code like this

    SELECT     x1, x2 

    FROM       xx,yy

    WHERE(

      (CASE

              WHEN  @y1 Is Null THEN 1

                        Else dbo.xx.y1=@y1            End)

    AND

      (CASE 

              WHEN  @y2 Is Null THEN 1

                        Else dbo.yy.y2=@y2    End)

    AND

    (CASE 

              WHEN  @y3 Is Null THEN 1

                        Else dbo.yy.y3=@y3   End)

    AND

    (CASE 

              WHEN  @y4 Is Null THEN 1

                        Else dbo.yy.y4=@y4    End))

     

    I am getting a error "Incorrect syntax near '='. (.Net SqlClient Data Provider)" In line no 6

    Could you please help me

    Rob

     

     

     

     

      

  • November 21, 2006 at 11:26 am

    #673273

    Here's a simpler approach:

    Select ColLIst

    FROM dbo.TableName

    Where (SomeCol1 = @Par1 Or @Par1 Is Null)

    And (SomeCol2 = @Par2 Or @Par2 Is Null)

    And (SomeCol3 = @Par3 Or @Par3 Is Null)

    And (SomeCol4 = @Par4 Or @Par4 Is Null)

    Or

    Select ColLIst

    FROM dbo.TableName

    Where SomeCol1 = IsNull(@Par1, SomeCol1)

    And SomeCol2 = IsNull(@Par2, SomeCol2)

    And SomeCol3 = IsNull(@Par3, SomeCol3)

    And SomeCol4 = IsNull(@Par4, SomeCol4)

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • November 21, 2006 at 12:21 pm

    #673291

    Thank You so much

    I got it now

  • November 22, 2006 at 7:26 pm

    #673625

    Think Robert Davis  second one is better.

    Stay away from OR's if possible.

  • November 22, 2006 at 10:05 pm

    #673633

    Nah, the performance will be the same with either one of them.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • November 23, 2006 at 6:32 am

    #673656

    Yes in that case... but it is a good reflex to try to convert the ors to something else and check for performance benefits .

  • November 25, 2006 at 10:15 am

    #673898

    As we say in my hood, "True dat!!"

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply