Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Where clause

Answered Where clause

  • Tuesday, February 12, 2013 4:28 PM
     
     
    Sign In to Vote
    0

    I`m just wondering if there`s a better way to write this where clause ? with out the OR and may be use Coalescence?

    where (code= Ccode or code is null and Ccode is null) and (l1order = "c29" or l1order is null 
     and "c29" is null) and ("c34" = leaforder or "c34" is null and leaforder is null)

    FM

     

All Replies

  • Tuesday, February 12, 2013 5:13 PM
     
     
    Sign In to Vote
    0
    Why are you using double quotation marks around your variables? 
     
  • Tuesday, February 12, 2013 6:33 PM
     
     
    Sign In to Vote
    0

    Sql_newB,

    Its a query from Cognos 7.7 version I dont plan to use them I am converting the query to re create the cognos report into a SSRS report

    FM

     
  • Tuesday, February 12, 2013 6:51 PM
    Moderator
     
     
    Sign In to Vote
    0

    Only if you know of some value which can never be in your data you can do

    where ISNULL(code,'SomeValueNeverToBeFoundInData') = ISNULL(ccode, 'SomeValueNeverToBeFoundInData')

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, February 12, 2013 8:33 PM
     
     
    Sign In to Vote
    0
    Naomi, I thought about using isnull but I am not sure about what you ask... so better to leave how it is ?

    FM

     
  • Tuesday, February 12, 2013 10:23 PM
    Moderator
     
     
    Sign In to Vote
    0

    Leave it as is, just I would probably add parenthesis, e.g.

    where (code=cCode or (code IS NULL and cCode IS NULL)) AND (SomeColumn = AnotherColumn OR (SomeColumn IS NULL OR AnotherColumn IS NULL))

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Wednesday, February 13, 2013 4:35 AM
     
     Answered
    Sign In to Vote
    0
    I would rather split the query into multiple queries to use the predicates efficiently. Though it would look more codes, it might help you and neat in the plan.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Wednesday, February 13, 2013 6:01 AM
     
     
    Sign In to Vote
    0

    Hi Frahan,

    Use Cross Apply in where clause and do your operation in and use that reference in the main select query....

     
  • Wednesday, February 13, 2013 3:31 PM
     
     
    Sign In to Vote
    0
    Another option i was thinking is , the query is huge when it comes to coding...So may dump the result into 3 tables and do a Join... I could keep updating these tables via SSIS or a Proc moving forward.. right now just tooks tooo complex.. any thoughts??????????

    FM

     
  • Friday, February 22, 2013 12:42 AM
    Moderator
     
     
    Sign In to Vote
    0
    Another option i was thinking is , the query is huge when it comes to coding...So may dump the result into 3 tables and do a Join... I could keep updating these tables via SSIS or a Proc moving forward.. right now just tooks tooo complex.. any thoughts??????????


    Post the huge query and the DDL for quick assistance. Thanks.

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012