Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Writing a nested 'if' statement

    Hello! I do most of my work in Crystal and am trying to write a query in SQL. I have a basic understanding of coding but I'm having a lot of trouble with writing a nested if statement. A little background:

    My company has a hierarchy for its carriers; some carrier parents have a level 1 code with several level 2 and 3 children. I need to write an if statement that rolls all the children to the parent. The table I use has the company code field, as well as the rollup carrier field. However, because a level 3 would only roll up to level 2 this way, I use the same table twice and write a formula to get everything rolled up to level 1. In Crystal, the formula appears like this:

    if (isnull({carriers.rollupcarrier})) then {carriers.companycode] else
    if (isnull({carriers_1.rollupcarrier})) then {carriers.rollupcarrier} else
    {carriers_1.rollupcarrier}

    Any ideas on how to translate that in SQL? Any help is greatly appreciated!!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SWITCH(carriers.rollupcarrier IS NULL, carriers.companycode, carriers_1.rollupcarrier IS NULL, carriers.rollupcarrier,1 = 1, carriers_1.rollupcarrier)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can nest conditional if Iif() expressions which is similar to the If() expression in Excel, but it quickly gets ugly when nested.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    (CASE WHEN carriers.rollupcarrier IS NULL THEN carriers.companycode
    ELSE (CASE WHEN carriers_1.rollupcarrier IS NULL THEN carriers.rollupcarrier
    ELSE carriers_1.rollupcarrier
    END)
    END)
    Last edited by kandrusatish; 12-22-09 at 07:02.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wouldn't this be easier?
    Code:
    Coalesce(carriers_1.rollupcarrier, carriers.rollupcarrier, carriers.companycode)
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by kandrusatish View Post
    (CASE WHEN carriers.rollupcarrier IS NULL THEN carriers.companycode
    ELSE (CASE WHEN carriers_1.rollupcarrier IS NULL THEN carriers.rollupcarrier
    ELSE carriers_1.rollupcarrier
    END)
    END)
    You don't need the ELSE clauses or the nesting, just consecutive WHENs. The statement is satisfied when the first test returns true.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - apols OP - I thought this was in the Access forum
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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