Formatting Case statement results

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

SQL Server 2005 Forums

Transact-SQL (2005)

Formatting Case statement results

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-11 : 08:08:18

I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.

CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)

Any ideas how I construct this CASE statement to get the desired results.

Thanx,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 08:09:41

Try:-
Right('0000' + Cast(CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as varchar (4)),4)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:11:26

CASE substring(PCRACT, 5, 1)
WHEN '4' THEN 3641
WHEN '5' THEN 2012
WHEN '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)

You can't name the columns with normal select. You will have to use dynamic sql for this.
http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 08:47:24

Trudye -- Again, JUST RETURN RAW DATA FROM YOUR DATABASE with clear, consistent and generic column names and let your client application -- reporting tools, web application, excel file, windows app, etc -- do ALL of the formatting, including labeling column names. That is the very foundation of the way databases are designed to be used -- T-SQL is *not* a tool used to make things pretty and present output in a specific format, it is strictly a "raw data manipulation" language used to edit and retrieve raw data.

You are working much, much harder than you need to and making simple task extremely difficult and overcomplicated by avoiding simple common best practices.

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-12 : 06:07:10

Jsmith I agree with you but I just work here, and I do what they tell me to do. The expaination is that's what the client wants and that's what we contracted to provide.

Visakh16 your solution worked GREAT.

Thanks to all who responded, I appreciate all of your help and hard work.

Be well,
Trudye

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 06:09:57

quote:
Originally posted by Trudye

CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END , as Right('0000' + Cast(Sys as varchar(4)), 4)

Any ideas how I construct this CASE statement to get the desired results.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-12 : 11:52:49

I guess this has already been solved, but I think the intention is clearer (assuming I understand the requirement) to do it like this:

CASE 
	WHEN substring(PCRACT, 5, 1) = '4' THEN '3641'
	WHEN substring(PCRACT, 5, 1) = '5' THEN '2012'
	WHEN substring(PCRACT, 5, 1) = '6' THEN '9694'
	ELSE '0000'
END AS MyColumnName

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 12:57:43

quote:
Originally posted by jsmith8858

Trudye -- Again, JUST RETURN RAW DATA FROM YOUR DATABASE with clear, consistent and generic column names and let your client application -- reporting tools, web application, excel file, windows app, etc -- do ALL of the formatting, including labeling column names. That is the very foundation of the way databases are designed to be used -- T-SQL is *not* a tool used to make things pretty and present output in a specific format, it is strictly a "raw data manipulation" language used to edit and retrieve raw data.

No offense but this is extremely old school mentality. T-SQL is an very robust language that allows many options. Just recently I wrote a query that returned several columns that represented seconds. Each column is then formatted in the final select and formatted into a human readable format of (n)d (n)m (n)h (n)s - where(n) is some number. And only the parts that apply are shown. A very simple task for SQL that got wrapped into a function.

Mind you, I'm not advocating either way is right or wrong, just that there are options either way and based on the situation you use the appropriate approach.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-12 : 18:02:18

tfountain -- You are correct -- a screw driver can indeed be used to hammer nails. I'll continue to recommend that people use a hammer, however. If you are calling a function to format seconds into a specific format within T-SQL, you just made your database code slower, more complicated, and less versatile and now a client that wants to accept your "time format" must re-parse it back into a standard DateTime or Int datatype in order to sort it, do date math, format it differently, or basically do anything at all with it other than blindly display it. Of course, instead of writing and calling a custom T-SQL function to do the job, you could use proper data types and a simple format string and do no coding at all ...

No one says formatting cannot be done in T-SQL -- it certainly can. If you want your database code to suffer and to lose flexibility and performance, and if you want to forgo strong data typing and return nothing but VARCHARS, you are certainly welcome to do it there. I will personally do things the easiest, simplest, most efficient and most flexible way and also follow best practices while having the proper tool do the job it's designed for. I will also continue to recommend that others do the same.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-12 : 18:08:00

Oh, almost forgot:

quote:

No offense but this is extremely old school mentality.

Actually, you have it completely backwards: "Old-school" mentality is to use strings to represent everything and to write long, complicated manual expressions to do simple tasks that modern languages now can do for you with little or no coding at all.

- Jeff
http://weblogs.sqlteam.com/JeffS

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 19:17:28

Oh boy, one of these arguments - black and white and no shades of grey. I concede - we'll have to agree to disagree.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-12 : 19:35:52

No, there's tons of shade gray. and in some cases you may need to do some sort of formatting in T-SQL. but that is always your last resort if no better option is available. Makes sense?

- Jeff
http://weblogs.sqlteam.com/JeffS

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 19:37:41

quote:
No, there's tons of shade gray. and in some cases you may need to do some sort of formatting in T-SQL. but that is always your last resort if no better option is available. Makes sense?

Thanks for summarizing my original statement.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-12 : 19:41:53

OK, then why did you feel you are disagreeing with me? What did I say that you disagree with?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources