Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Issue with Select Case

Posted on 2013-06-27
3
Medium Priority
?
557 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 2000 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 27

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
Ranking ecommerce websites is a vital process. You need to have a strong SEO (Search Engine Optimization) strategy. If you don’t have one, you are losing out on brand impressions, clicks and sales. Check this guide on how to improve website traffic …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

885 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