Solved

Issue with Select Case

Posted on 2013-06-27
3
510 Views
Last Modified: 2013-07-01
Hi Experts,


select distinct
        CASE WHEN TRIM(Cast(decile as varchar(3))) =''  THEN 'U'
        ELSE Trim(Cast(decile as varchar(3))) END  decile
from Table1
where model_name = 'TopTier'

Note : decile is tinyint of length 3 in table 1

I want this select statement to return 'U' as there are no records for model name :TopTier in table 1.

But this statement is returning 0 records. Please correct this statment.

Thanks ,

SAI
0
Comment
Question by:n_srikanth4
3 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39282109
Try this.

;with Tbl1 AS
(
SELECT decile
FROM Table1
WHERE model_name = 'TopTier'
)
select distinct
        CASE WHEN TRIM(Cast(decile as varchar(3))) =''  THEN 'U'
        ELSE Trim(Cast(decile as varchar(3))) END  decile
from Tbl1
union select 'U'
where not exists (select decile from Tbl1)
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39283098
This should work.
select 'U' where not exists (select decile from Table1 where model_name = 'TopTier')
union
select distinct
        CASE WHEN TRIM(Cast(decile as varchar(3))) =''  THEN 'U'
        ELSE Trim(Cast(decile as varchar(3))) END  decile
from Table1
where model_name = 'TopTier'

Open in new window

0
 

Author Closing Comment

by:n_srikanth4
ID: 39290045
good
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

22 Experts available now in Live!

Get 1:1 Help Now