Putting a CAST around a CASE statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Putting a CAST around a CASE statement?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-11-09 : 10:09:13

Hi all

How do you put a CAST statement around a CASE statement? I have the following which is a DATETIME field but needs to be converted to CHAR but can't get it to work.

(CASE
WHEN j.act_strt_date <> 0 THEN
j.act_strt_date
WHEN j.act_strt_date is NULL AND j.job_strt_date <> 0 THEN
j.job_strt_date
WHEN job_strt_date is NULL THEN
(SELECT jsl1.log_effective_date
FROM
job_stat_log jsl1
WHERE
j.job_number = jsl1.job_number AND
jsl1.stat_code = '0105' AND
jsl1.job_log_number
IN
(SELECT max(jsl1.job_log_number)
FROM job_stat_log jsl1
WHERE
j.job_number = jsl1.job_number AND
jsl1.stat_code = '01111'))
ELSE 0
END) as Programmed_work

Kristen
Test

22859 Posts

Posted - 2010-11-09 : 10:21:32

CONVERT(char(11), CASE WHEN ... THEN .. ELSE ... END, 113)

You need suitable values for the bits in Red - See Documentation section on "CONVERT" for details

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 10:23:43

See this for example:
select
convert(varchar(10),
case
when 1=1 then getdate()
when 2=2 then getdate()-1
else 0
end
,101)
as result

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 10:24:14

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 10:26:24

[code]SELECT CAST(
CASE WHEN someCondition THEN someValue
ELSE otherValue
END
as CHAR(11)
)
FROM tableExpression[/code]

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-11-09 : 10:37:48

Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 10:41:01

<<
I have the following which is a DATETIME field but needs to be converted to CHAR
>>

Why?

Madhivanan

Failing to plan is Planning to fail

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-11-09 : 11:26:31

I have a query that unions data for different purposes and some of which include these case statement for dates.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-09 : 13:48:46

>> How do you put a CAST statement around a CASE statement? <<

You don't. That is because there is no such thing as CAST() statement or a CASE statement in SQL; they are expressions. SQL is a declarative language, not a procedural one.

>> I have the following which is a DATETIME field [sic] but needs to be converted to CHAR but can't get it to work. <<

Columns are nothing like fields. Your whole approach to SQL is wrong. You are writing 1950's COBOL in SQL. The biggest error is that you to format data into display strings inside the database. Unlike the monolithic architecture you used with file systems, am tiered architecture will send a DATE as a temporal data tyep to the front end. The front end will do the display work.

Think that it makes sense to compare a date to a numeric is absurd. What is Christmas plus Ramadan, to three decimal places?

Finally CASE expressions have to return scalar values at the same level of aggregation. They also test the WHEN clauses in order. They do not check is the predicate; so how can this be true: (J.act_strt_date IS NULL AND J.job_strt_date <> 0), even assuming that there is a date zero in the Common Era calender?

(CASE
WHEN J.act_strt_date <> 0 --absurd
THEN J.act_strt_date

WHEN J.act_strt_date IS NULL -- think about it!
AND J.job_strt_date <> 0
THEN J.job_strt_date

WHEN job_strt_date IS NULL -- wrong aggregation level
THEN (SELECT JSL1.log_effective_date
FROM Job_Stat_log AS JSL1
WHERE J.job_number = JSL1.job_number
AND JSL1.stat_code = '0105'
AND JSL1.job_log_number
IN (SELECT MAX(JSL1.job_log_number)
FROM Job_Stat_Log AS JSL1
WHERE J.job_number = JSL1.job_number
AND JSL1.stat_code = '01111'))
ELSE 0 END) AS programmed_work

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 14:00:23

quote:
Originally posted by jcelko

Columns are nothing like fields.

This is incorrect. While technically they aren't exactly the same thing, it is acceptable to use the terms interchangeably.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 14:01:09

quote:
What is Christmas plus Ramadan, to three decimal places?

Um...let me guess:
A very awkward holiday for the in-laws and just plain confusing for the grand-kids?

Be One with the Optimizer
TG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 14:04:23

quote:
Originally posted by TG

quote:
What is Christmas plus Ramadan, to three decimal places?
Um...let me guess:
A very awkward holiday for the in-laws and just plain confusing for the grand-kids?

Be One with the Optimizer
TG

Nahh, just another of joe's famous rants

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-09 : 17:44:58

not when I spent ten years on ANSI X3H2

In 25 words or less it is "Logical versus Physical", but it goes beyond that. If you do not have a good conceptual model, you hit a ceiling and cannot get past a certain level of competency.

A file is made up of records, records are made up of fields. Files are independent of each other, while tables in a database are interrelated. You open an entire database, not tables; you open individual files. The original idea of a database was to collect data in a way that avoided redundant data in too many files and not have it dependent on a particular programming language.

A file is ordered and can be accessed by a PHYSICAL location, while a table is not. "first record", "last record", and "next n records" make sense in a file but not in a table. Rows have no magical "id" or "row_id' like the physical record number in a file.

In fact, columns, rows and tables do not have to have any physical existence at all. VIEWs, derived tables and CTEs are virtual tables, I can have computed columns, e5tc.

Look at how many times we get a posting where someone wants to sequentially read a table. In a procedural language, "READ a,b,c FROM FileX;" does not give the same results as "READ b,c,a FROM FileX;" and some languages will let you write "READ a,a,a FROM FileX;"

A file is usually associated with a particular language -- ever try to read a FORTRAN file with a COBOL program? A database is language independent; the internal SQL datatypes are converted into host language data types.

A field exists only because of the program reading it; a column exists becasue it is in a table in a database. A field is fixed or variable length, can repeated with an OCCURS in COBOL, etc. It does not have to be a scalar value like a column. A field can change datatypes (union in 'C', VARIANT in Pascal, REDEFINES in COBOL); a column cannot. You have no idea whatsoever how a column is represented internally; you never see it. SQL is more abstract than a file system; you think of a number as a NUMBER, and not as a physical string of numerals. you think of a date as a DATE, and not as a physical string of numerals and local punctuation marks.

I wish that more programmers had worked with a magnetic tape system. It is based on physical contiguous storage for everything. This is where the idea of a primary key came from. Everything in a tape system depends the files being sorted on the same key, so you can merge data. It was awhile before Dr. Codd changed his mind and said that all keys are keys, and we don't need a special one in a relational database, like you did in a tape system.

Dr. Codd defined a row as a representation of a fact. A record is usually a combination of a lot of facts. That is, we don't normalize a file; you stuff data into it and hope that you have everything you need for an application.

Rows and columns have constraints. Records and fields can have anything in them and often do!! Talk to anyone who has tried to build a data warehouse about that ...

The other "un-learning" that I see when I teach an SQL class is with people who learned spreadsheets. They think that tables are made of rows and columns; no, tables are made of rows and rows are made of columns. Columns hold scalar values, not pointers or formuAlas.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 21:38:42

I don't disagree with you Joe on most of your points. I disagree with the way you plaster folks. Usually you follow that up with sound advice which is helpful.

It is the condescending attitude that you occassionally display that quite frankly, ticks me off.

No one doubts your expertise. There are times that we can agree to disagree, but when people dispute facts, there's little room for argument. Sure there's one or two points that I'd argue, but nothing important.

Your expertise has helped many, and I'm sure that you have the royalty checks to prove my point. But your chastising beginners sends them off to buy books from people far less qualified than yourself to help them.

My opinion only, of course.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 21:40:17

By the way, had to lol about the spreadsheet point you made. Too true.

Kristen
Test

22859 Posts

Posted - 2010-11-10 : 02:40:45

quote:

Originally posted by russell

SELECT	CAST(
		CASE WHEN someCondition THEN someValue
		ELSE otherValue
		END
	as CHAR(11)
	)
FROM	tableExpression

Apart from being double sniped (a first?!!) is there a way to indicate the Format when using CAST rather than CONVERT? (I never use CAST having always used CONVERT through habit, so I'm just curious)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 03:02:08

No there isn't.
And that's the mostly stated fact when someone asks for the difference between CAST() and CONVERT().

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-10 : 09:50:52

quote:
Originally posted by Kristen

quote:
Originally posted by russell

SELECT CAST(
CASE WHEN someCondition THEN someValue
ELSE otherValue
END
as CHAR(11)
)
FROM tableExpression

Apart from being double sniped (a first?!!) is there a way to indicate the Format when using CAST rather than CONVERT? (I never use CAST having always used CONVERT through habit, so I'm just curious)

1 Use CAST for actual convertions
2 Leave formation to front end (if not possible use CONVERT)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-11-10 : 18:24:50

Actually I prefer CONVERT for Casting.

I like to see the "Meat" - hence I do:

CONVERT(varchar(999), some-long-expression)

because

CAST some-long-expression AS varchar(999)

may mean that the "varchar(999)" bit is off the right side of the screen, and I miss the fact that "999" won't be large enough.

Likewise I prefer:

SELECT [ColName] = some-expression
to
SELECT some-expression AS [ColName]

for the same reason. and


FROM MyTable AS T
    JOIN MyOtherTable AS O
        ON O.Col1 = T.Col1
       AND O.Col2 = T.Col2

to


FROM MyTable AS T
    JOIN MyOtherTable AS O ON
       O.Col1 = T.Col1 AND
       O.Col2 = T.Col2

and so on.

I think it helps spot potential errors earlier in code review (well, more sort of "code glancing" I suppose!)

Just my $0.02 worth

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-10 : 19:11:06

quote:
Originally posted by Kristen

Actually I prefer CONVERT for Casting.
<snip>
Just my $0.02 worth

I completely disagree with all those points. But, hey, that's why there are no SQL standards besides what we make.

Unless you count the ISO-11179. But, even that is debated. Heck even Celko states that the 11179 standard says you should have plural table names. But, all I can find that standard talking about are singular "classes" which I assume they are equating to entities...?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-10 : 20:33:56

There is no "should" when it comes to naming conventions. The only "should" is that it should make sense in context.

Other than that, it is strictly preference.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources