none
T-sql if else statement

    Question

  • I have the following 

    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc)

    I need to change this to an if/else select where the rolekey=17 I need to make it so if there is no rolekey17 I want to select rolekey=3

    This is in a stored proc, right after an IF/End statement.


    shawnrye

    Tuesday, July 09, 2013 2:07 PM

Answers

  • If I understand what you are asking, you could do

    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch 
      where rolekey in (3,17) and entrykey = @i_entrykey and defaultbu=1 
      order by rolekey desc, defaultrole, contactkey desc)

    If there are one or more rows with rolekey = 17, it will return the top row with rolekey = 17 and the if there are only rows with rolekey = 3, then you will get the top row with rolekey = 3.

    Tom

    Tuesday, July 09, 2013 2:20 PM

All replies

  • If I understand what you are asking, you could do

    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch 
      where rolekey in (3,17) and entrykey = @i_entrykey and defaultbu=1 
      order by rolekey desc, defaultrole, contactkey desc)

    If there are one or more rows with rolekey = 17, it will return the top row with rolekey = 17 and the if there are only rows with rolekey = 3, then you will get the top row with rolekey = 3.

    Tom

    Tuesday, July 09, 2013 2:20 PM
  • try these,

    if exists (SELECT 1 from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1 )
    BEGIN 
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 
    else 
    begin
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 
    
    --OR
    
    set @i_icid =cASE WHEN (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc) IS NULL then 
    (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc)
    else 
     (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 

    Tuesday, July 09, 2013 2:22 PM
  • simple logic, first initialize your variable to null, then get assign the value if you have roleKey = 17, in the next statement check if you variable is still null, if so then make it 3 or else let it have the value it has.

    SET @i_icid = NULL

    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey             and defaultbu=1  order by defaultrole,contactkey desc)

    if @i_icid IS NULL

     SET @i_icid = 3


     

    Nothing is Permanent... even Knowledge.... <br/> <a href="http://everysolution.wordpress.com/">My Blog</a>

    Tuesday, July 09, 2013 2:28 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    The attribute property “_key” is a complete violation of ISO-11179 rules and basic data modeling. That is meta data that tells us HOW the attribute is used in one table, but not WHAT is by its nature. 

    The use of the proprietary TOP(n) is how you tell the world that you do not know Standard SQL or have a SQL mindset. We do not like local variables! SQL is a declarative language, so we put the expression directly in the statement where it is used. 

    I will also bet that the “v_” prefix is to tell the world this is a VIEW. We do not do that in RDBMS. 

    SELECT MAX(contact_key) 
      FROM contact_user_branches
     WHERE CASE role_key 
           WHEN 17 THEN 'T' 
           WHEN 3 THEN 'T'
           ELSE 'F' END = 'T'
       AND entry_key = @in_entry_key 
       AND default_bu = 1; 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 3:47 PM
  • I am new to SQL, Relax. I am C# developer, not SQL Admin.

    Unfortunately the Sql guy is on vacation and my senior engineer is busy with another .Net project.


    shawnrye

    Tuesday, July 09, 2013 4:03 PM
  • Did you try Tom's suggestion? If we understood your query correctly as you want to get RoleKey 17 row, but if it doesn't exist, RoleKey 3, then it should work for you.

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


    My blog


    My TechNet articles

    Tuesday, July 09, 2013 4:04 PM
    Moderator
  • Yes that is correct. I checked the Views the parameters etc... and I still don't see how the person with rolekey=40 is being assigned rolekey=17.

    basically this is a web page form you fill out and depending on which Branch the person belongs to they get an intake coordinator assigned to them. The Intake coordinator has a rolekey=17 for a branch(entrykey)

    if there is no Intake coordinator for a specific branch selected I want to make the Branch manager the default intake coordinator the branch managers have a rolekey=3.

    for some reason those who have a rolekey=40 are being grabbed and place into the role.

    keep in mind the rolekey 40 will be the person filling out the form under their login.

    If the Branch has an Intake coordinator with a rolekey=17 than it works great, but if they dont then the person with rolekey=40 who is filling out the form gets passed as the Intake coordinator instead of the person with rolekey=3.


    shawnrye

    Tuesday, July 09, 2013 4:18 PM
  • I tried it and it doesn't work.


    shawnrye

    Tuesday, July 09, 2013 4:31 PM
  • I think you need to provide DDL and sample data and exact output based on the sample data. We don't have your tables and structure so it's hard to figure out your requirements.

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


    My blog


    My TechNet articles

    Tuesday, July 09, 2013 4:40 PM
    Moderator
  • I am new to SQL, Relax. I am C# developer, not SQL Admin.

    Unfortunately the Sql guy is on vacation and my senior engineer is busy with another .Net project.

    If the company doctor goes on vacation, do they make you do surgery, too?? :)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 4:55 PM
  • That doesn't work, I am still getting the wrong data. 


    shawnrye

    Tuesday, July 09, 2013 5:35 PM
  • I can't set the @i_icid = 3 becuase this is contactkey a primary key and it will be different for different people.

    shawnrye


    Tuesday, July 09, 2013 5:36 PM
  • NO, But nobody is going to die here.

    If anybody dies I will let you know. Thanks for all your help.


    shawnrye

    Tuesday, July 09, 2013 5:38 PM
  • Unfortunately there is a lot of sensitive data and my employer won't let me give out DDL sorry.

    Do you know of any other forums I can go too?

    This below Almost works but if there is no rolekey=17 its like the sql statement stops if there is no rolekey=17 and doesn't look for the rolekey=3.

    if exists (SELECT 1 from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey   and defaultbu=1 )
    BEGIN 
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey   and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 
    else 
    begin
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey    and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 


    shawnrye

    Tuesday, July 09, 2013 5:48 PM
  • Have you tried simply seeing what is being returned here?  If you can see that someone is incorrectly being assigned something, I assume you know the variables.  Just do a Select * From and use the entry key and defaultbu values, with your order by, and see what returns.

    With the given scenario, Tom's solution will work.  If it does not work for you, it means there is some inconsistency between the given scenario and the real scenario.  As such, if you cannot provide DDL and sampled data, you are not likely to get much more help than guesswork, regardless of where you go.

    Tuesday, July 09, 2013 6:44 PM
  • Could the contactKey be NULL? In your query or Tom's query which is correct and the same scenario as yours CHANGE

    SELECT TOP (1) CONTACTKEY ...

    to

    SELECT TOP (1) COALESCE(ContactKey, "No Key") as ContactKey

    This is assuming that ContactKey is character. If it's numeric, try using some value which is unlikely to be in the data, say, -99999


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


    My blog


    My TechNet articles


    Tuesday, July 09, 2013 6:54 PM
    Moderator
  • Contactkey is number INT. 

    Rolekey is an INT

    entrykey is an INT.


    shawnrye

    Tuesday, July 09, 2013 7:10 PM
  • Unfortunately there is a lot of sensitive data and my employer won't let me give out DDL sorry.

    Do you know of any other forums I can go too?

    This below Almost works but if there is no rolekey=17 its like the sql statement stops if there is no rolekey=17 and doesn't look for the rolekey=3.

    if exists (SELECT 1 from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey   and defaultbu=1 )
    BEGIN 
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey   and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 
    else 
    begin
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey    and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 


    shawnrye

    You can that condition in ELSE clause just like for rolekey=17.
    Tuesday, July 09, 2013 7:11 PM
  • in else clause?


    shawnrye

    Tuesday, July 09, 2013 7:28 PM
  • None of the following work..


    ----Below works as long as there is a IC for the Branch otherwise gives back whoever is filling out the form
    if exists (SELECT 1 from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey and defaultbu=1 )
            begin
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17  and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 
    else 
    begin
    set  @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 

    ----Below works as long as there is a IC for the Branch otherwise gives back whoever is filling out the form
    set @i_icid =cASE WHEN (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and rolekey<>40 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc) IS NULL then 
    (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and rolekey<>40 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    else 
      (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and rolekey<>40 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    end 

    ----Tried the following didn't work
    select MAX(contactkey) 
    from vcontactuserbranch
    where case rolekey 
      when 17 then 'T' 
      when 3 then 'T'
      else 'F' end = 'T'
    and entrykey = @i_entrykey 
    and defaultbu = 1; 

    ---------
    begin
    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch 
    where rolekey=17 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    if (@i_icid is null) 
    SET @i_icid = NULL
    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch 
    where rolekey=3 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    end


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

    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    if @i_icid = null
    begin 
    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey and defaultbu=1 order by defaultrole,contactkey desc)
    end

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

    SELECT TOP (1) COALESCE(ContactKey, "-9999999") as ContactKey from vcontactuserbranch 
        where rolekey in (17,3) and entrykey = @i_entrykey and defaultbu=1 
        order by rolekey desc, defaultrole, contactkey desc
        
        
        ------------------
        
        select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=17 and entrykey = @i_entrykey and defaultbu=1 
    order by defaultrole,contactkey desc)
    if @i_icid = null
    begin 
    select @i_icid = (SELECT TOP(1) (contactkey) from vcontactuserbranch where rolekey=3 and entrykey = @i_entrykey and defaultbu=1 order by defaultrole,contactkey desc)
    end
        
        ----------------------------
        
        SELECT TOP (1) COALESCE(@i_icid, "0") as ContactKey from vcontactuserbranch 
        where rolekey in (17,3) and entrykey = @i_entrykey and defaultbu=1 
        order by rolekey desc, defaultrole, contactkey desc
        
        
        


    shawnrye

    Tuesday, July 09, 2013 7:28 PM

  • ----Below works as long as there is a IC for the Branch otherwise gives back whoever is filling out the form

    So is @i_icid already set by the time this runs?
    Tuesday, July 09, 2013 7:42 PM
  • Is it possible that you don't have RoleKey 17 and RoleKey 3 for that entry key?

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


    My blog


    My TechNet articles

    Tuesday, July 09, 2013 7:45 PM
    Moderator
  • You could try that as well:

    select @i_icid = (SELECT TOP(1) (contactkey) 
    from vcontactuserbranch 
    where (rolekey=17 or rolekey=3)
    and entrykey = @i_entrykey             
    and defaultbu=1 
    order by defaultrole,contactkey desc)


    Amar Deep Singh

    Tuesday, July 09, 2013 9:15 PM
  • Yes the Rolkeys are there for example when I do a select * from 

    vcontactuserbranch where rolekey in (3,17)

    I will get all contacts and the following firstname, lastname, branch(entrykey),rolekey, defaultbu

    All Branches have a contact with a rolekey=17 but one branch which I am using to test doesn't they have any contact with a rolekey=17 so I need the contact who has a rolekey=3 which this Branch HAS.


    shawnrye

    Tuesday, July 09, 2013 9:58 PM
  • Unfortunately I did try that, it was the first thing I tried, but that didn't work.

    shawnrye

    Tuesday, July 09, 2013 9:59 PM
  • No the select is trying to find the correct contactkey. But the contactkey does exsist, I just can't get the right one.


    shawnrye

    Wednesday, July 10, 2013 4:05 PM