[Webinar] Streamline your web hosting managementRegister Today

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
 
QlemoDeveloperCommented:
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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