Solved

SQL Nested Case Select Help

Posted on 2011-03-23
3
468 Views
Last Modified: 2012-05-11
, 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
Comment
Question by:cheryl9063
  • 2
3 Comments
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 500 total points
Comment Utility
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
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now