convert help - SQL Server Forums

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

convert help

Author

Topic

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 05:12:19

hi im attempting to amend a stored procedure so that certain records have a money field overwritten with 'TBA' rather than the value as these are not confirmed yet so dont want to show them...

i have

SELECT TOP 1
CourseID,
(Case when year >'2009' then 'TBA' else TuitionFee end) as TuitionFee,
(Case when year >'2009' then 'TBA' else MaterialsFee end) as MaterialsFee,
(Case when year >'2009' then 'TBA' else ResidFee end) as ResidFee,
(Case when year >'2009' then 'TBA' else ExamFee end) as ExamFee,
(Case when year >'2009' then 'TBA' else OtherFees end) as OtherFees,
(Case when year >'2009' then 'TBA' else TotalFee end) as TotalFee

FROM dbo.Courses

However the Fees fields are all money fields so i get an error - cannot convert money to char

How do i include a convert/cast to get this working in the assignment of 'TBA'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 05:17:54

Convert Fee columns to varchar

ex

cast(TuitionFee as varchar(20))

Madhivanan

Failing to plan is Planning to fail

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 05:19:53

yes so how do i set it as 'TBA'

cast(TuitionFee as varchar(20) as 'TBA') ??

if not > 2009 if want normal money value, no need to convert then

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 05:32:27

You should do this in your front end application
Otherwise

use this logic

(Case when year >'2009' then 'TBA' else cast(TuitionFee as varchar(20)) end) as TuitionFee,

Madhivanan

Failing to plan is Planning to fail

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 05:43:41

this doesnt seem to pull any value back at all for any courses, before or after 2009 now

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 05:53:27

what is the datatype of year column?

Madhivanan

Failing to plan is Planning to fail

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 05:56:41

varchar(4)

basically if year > 2009 i want the TuitionFee field (money datatype) to be set as 'TBA' otherwise if not > 2009 i just want it left as it is (ie the fee value)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 06:00:20

Why did you use varchar to store integer values?

Remove single quotes aroud 2009 and try

Madhivanan

Failing to plan is Planning to fail

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 06:10:19

no idea, im working with stored procedures that someone else created...

anyway still not working... still no fees at all coming through, not sure what else to try

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-03 : 06:13:54

Another approach might be splitting the resultset into two parts and combining them using UNION
like this
SELECT CourseID, 'TBA' as TuitionFee,'TBA' as MaterialsFee,...........
FROM dbo.Courses
WHERE year >2009
UNION
SELECT CourseID,..........
FROM dbo.Courses
WHERE year <2009

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 06:23:29

still get this error - Cannot convert a char value to money. The char value has incorrect syntax.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-03 : 10:56:40

Seems to work for me:

DECLARE @Courses TABLE (CourseID INT, [Year] VARCHAR(4), TuitionFee MONEY, MaterialsFee  MONEY)

INSERT @Courses
SELECT 1, '2009', $100.00, $50.00
UNION ALL SELECT 1, '2010', $50.00, $123.00
UNION ALL SELECT 1, '2011', $99.65, $82.09


SELECT
	CourseID,
	CASE WHEN [year] > '2009' THEN 'TBA' ELSE CAST(TuitionFee AS VARCHAR(20)) END AS TuitionFee,
	CASE WHEN [year] > '2009' THEN 'TBA' ELSE CAST(MaterialsFee AS VARCHAR(20)) END AS MaterialsFee
FROM
	@Courses

evvo
Starting Member

16 Posts

Posted - 2010-06-03 : 11:22:48

its strange ive got identical select statement in my SP but only ever get the money values returned in the columns never 'TBA' for any years > 2009, im stumped as your example does indeed work...i just cant see whats different in mine

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-03 : 11:31:03

Why do you have the TOP 1 in your OP? I dont see any ORDER BY associated with it.

Can you post the complete and exact query that you tried.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources