Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

SQL Nested Case Select Help

, Trying to a nested case

See code below..Cant get it to work            
CASE 
     WHEN @LoginAgentClient ='Agent' 
       and	dbo.ufn_User_RetrieveCount_UniqueView_v000101(l.ListID,@LoginID,@UserGroupID,100001)>0 
        THEN 'Y'
    ELSE
      CASE
        WHEN @LoginAgentClient ='Client'
          THEN Null
     ELSE  'N'	            				
       END AS  CountListViewed,

Open in new window

0
cheryl9063
Asked:
cheryl9063
  • 2
2 Solutions
 
jimyXCommented:
Try this:
CASE
     WHEN (@LoginAgentClient ='Agent')
       and      (dbo.ufn_User_RetrieveCount_UniqueView_v000101(l.ListID,@LoginID,@UserGroupID,100001)>0) THEN 'Y'
    ELSE (CASE
                   WHEN (@LoginAgentClient ='Client') THEN Null
                   ELSE  'N' END) END) AS  CountListViewed,
0
 
jimyXCommented:
BTW yours is correct you just forgot one END:
CASE
     WHEN @LoginAgentClient ='Agent'
       and      dbo.ufn_User_RetrieveCount_UniqueView_v000101(l.ListID,@LoginID,@UserGroupID,100001)>0
        THEN 'Y'
    ELSE
      CASE
        WHEN @LoginAgentClient ='Client'
          THEN Null
     ELSE  'N'                                          
       END END AS  CountListViewed,
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What's the sense in trying to nest cases? You can use this syntax without nesting instead:
case
   when @LoginAgentClient ='Agent' and     dbo.ufn_User_RetrieveCount_UniqueView_v000101(l.ListID,@LoginID,@UserGroupID,100001)>0
             then 'Y'
   when @LoginAgentClient ='Client'
             then NULL
   else 'N' end as CountListViewed

Open in new window

or even better
case @LoginAgentClient 
   when 'Agent' and     dbo.ufn_User_RetrieveCount_UniqueView_v000101(l.ListID,@LoginID,@UserGroupID,100001)>0
             then 'Y'
   when 'Client'
             then NULL
   else 'N' end as CountListViewed

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now