Solved

SQL Nested Case Select Help

Posted on 2011-03-23
3
472 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
ID: 35202702
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
ID: 35202775
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 69

Expert Comment

by:Qlemo
ID: 35202808
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Awk & Pythagoras - applying csv file data to the problem 26 61
AJAX pass along a variable 3 67
SQL Syntax 24 52
Query - Duplicate dates with different activities counts 10 43
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.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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