Solved

SELECT statement with CASE and CAST

Posted on 2014-09-18
3
235 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
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kdo
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:
Kdo 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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