Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Case within a case

    I was just wondering if it is possible to have nested case statements (case within a case). If so, what would the syntax be? Here is what I have now:

    case when isdate(my_date)=1 then convert(char(10),convert(datetime,cast(my_date as varchar(8))),101)
    else null end "my_date"

    I need to put another case outside this one. Any ideas?
    Thanks.
    Last edited by exdter; 01-21-04 at 15:55.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean like:

    Code:
    USE Northwind
    GO
    
    SELECT CASE WHEN EmployeeId = 4 THEN
    	    CASE WHEN CustomerId = 'SUPRD' THEN 1
    					   ELSE 0 
    	    END
    				ELSE 0
           END  
      FROM Orders
    Or did you have something else in mind?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    In my post I have that monster case that you helped me with yesterday. Now I need to take that case statement and put it inside another case statement. The case statement I posted takes an integer string converts it to char, then converts it into a date string. I have a status column in my table, and if the status is 'completed', I need to take the date string that I (you) made and put it into a matrix. Example

    status to_customer
    ------------------------------------------
    completed 12/31/2003
    completed 11/09/2003
    tentative NULL
    closed NULL

    The above is in a view. The original table has a column called check_status where it contains the to_customer entry. I will have to use this procedure more than once as the check_status column contains 8 entries besides to_customer. The way I have it set up now is a view on a view.

    Did you follow this?
    Thanks
    Last edited by exdter; 01-21-04 at 16:20.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure I follow...

    You mean like:

    Code:
    CASE WHEN Status_Code = 'Completed' THEN 
    	CASE WHEN ISDATE(my_date)=1 THEN CONVERT(char(10),CONVERT(datetime,CAST(my_date as varchar(8))),101) 
    				    ELSE NULL 
    	END
    				    ELSE NULL
    END AS my_date
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    Yes that's it!!! I think you need to be promoted from SQL Team Scrub to Sql Wise Old Sage.
    Thanks again. You are saving me so much time and table space.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by exdter
    Yes that's it!!! I think you need to be promoted from SQL Team Scrub to Sql Wise Old Sage.
    Thanks again. You are saving me so much time and table space.
    No, I am very much a scrub....title of my own choosing....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    Well you're a wise old sage to me.
    Thanks again!!!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or if your example is this simple you can use a single CASE statement with multiple criteria:

    select CASE
    WHEN Status_Code = 'Completed' and ISDATE(my_date)=1
    THEN CONVERT(char(10),CONVERT(datetime,CAST(my_date as varchar(8))),101)
    ELSE NULL END my_date
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    Thanks for your help. I've already updated my view. I will try later to see if I can do it your way because it looks alot neater and less confusing.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See...told ya I was a scrub.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Someday you may be a janitor too.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually, I'm a certified window washer...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't you mean Windows washer?
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Aug 2003
    Posts
    328
    Hi Blindman.
    I tried my case from your example and got the same results as the example from Brett. Is it possible to have 2 wise old sages of SQL Server?
    Thanks for your help too.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely. There's almost always more than one way to phrase a SQL statement. Some are bad, but many difference just come down to a matter of taste and style.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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