Solved

SELECT statement with CASE and CAST

Posted on 2014-09-18
3
239 Views
Last Modified: 2014-09-22
i am trying to return a string value when a timestamp field value is null.  below is my select statement.  I am getting an error message on the data type.  Can I use the cast() as varchar to resolve?

select
first_name,
last_name,
case when table.event_id = 5 and table.timestamp isnull then 'n/a' else table.timestamp end as ad_timestamp

from XXXXXX
0
Comment
Question by:szadroga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 40330359
All possible values returned in the CASE construct have to be the same datatype.

Cast the datetime/timestamp to any character (string) type and you'll be fine.


Good Luck!
Kent
0
 

Author Comment

by:szadroga
ID: 40330364
where do I perform the cast() in my syntax?  I cannot get it to work
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 40330370
select
first_name,
last_name,
case when table.event_id = 5 and table.timestamp isnull then 'n/a'
else cast (table.timestamp as varchar (20)) end as ad_timestamp
from XXXXXX
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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