CASE Statement Error: Cannot Convert A Char Value To Money
Nov 6, 2007
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one.
The field that does not work is a varchar(500) field (named Description).
I am not sure what the problem is.
The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes.
Any help is appreciated.
------------------------------------------------------------------------------------------------------
declare @Match varchar(75)
declare @NumRows int
declare @StartPos int
declare @OrderBy varchar(25)
declare @OrderDir varchar(4)
set @Match = 'dog% AND bark%'
set @NumRows = 25
set @StartPos = 100
set @OrderBy = 'Description'
set @OrderDir = 'ASC'
WITH catitems AS
(
SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels,
ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @OrderBy='ID' AND @OrderDir='ASC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='ASC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='ASC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='ASC'
THEN CAST(ci.Channels AS smallint)
END ASC,
CASE
WHEN @OrderBy='ID' AND @OrderDir='DESC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='DESC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='DESC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='DESC'
THEN CAST(ci.Channels AS smallint)
END DESC) As RowNo
FROM TableName As ci
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1
------------------------------------------------------------------------------------------------------
Thanks,
Aric
View 1 Replies
ADVERTISEMENT
Feb 22, 2008
We have the same database in three different environment. The statement below works just fine in two database environment, but I am getting the error in the third one. All the databases are Microsoft SQL Server 2005. I could not pine point the source of the issue. Please any input that you might be able to provide. Thanks
The issue in simplest from
DECLARE @UDF_Value nvarchar(255)
set @UDF_Value = 111
SELECT CONVERT(money, @UDF_Value)
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
View 7 Replies
View Related
Sep 27, 2007
I've been trying to solve this problem or just find an alternative to do this. I have filled a temp table with certain values for all columns except those columns that rely on calculations that need certain cell values from other columns in the temp table. I have a while statement that runs through each cell for one calculation column and this is the code that updates the fields on the calc column:
Code Block
EXEC('UPDATE #tblTemp
SET #tblTemp.['+@CalcColumns+'] = (CAST(ISNULL((SELECT #tblTemp.['+@Column1+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+'''), 0.00)as money) - CAST(ISNULL((SELECT #tblTemp.['+@Column2+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+''') , 0.00)as money))
WHERE #tblTemp.PK = '''+@PubKey+'''')
The problem is that it will not let me convert the two values to money. The error is:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
This is necessary to calculate the difference between these two cells from the temp table. I would appreciate any help! Thanks!
View 6 Replies
View Related
Dec 8, 1998
Msg 234, Level 16, State 2
There is insufficient result space to convert MONEY value to CHAR.
hi, I have one procedure that insert data into a table, I called the procedure 3 times ,and run it in one step, then got the above erorr message.
what can I do to avoid this error message?
and why I am getting it... thanks for your help
regards
Ali
View 2 Replies
View Related
Mar 11, 2014
How to convert an INT value from the registry to a VARCHAR but not sure how to convert from INT to VARCHAR within a CASE statement. Below is my code which current fails on "Conversion failed when converting the varchar value 'Automatic' to data type int".
DECLARE Var1 INT
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesSQLServerAgent',
@value_name = N'Start',
@value = @Var1 OUTPUT
[Code] ....
View 2 Replies
View Related
Jul 23, 2005
I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End
View 11 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Mar 8, 2004
I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?
SELECT @iError = @@error, @iRowCount = @@rowcount
IF(@iError <> 0 OR @iRowCount <> 1)
BEGIN
CONVERT(@dValue, @cValue)
set @cError = 'Error attempting to insert new record. Product : ' + @cProduct + ' Sub-account : ' + @cSubAccount + ' Cost : $' + @cValue
RAISERROR(@cError, 16, 1)
END
@cValue is a varchar(20)
@dValue is money
Any help?
Mike B
View 7 Replies
View Related
May 1, 2008
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype
Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.
select cast(IsNumeric((Value2)) as money)
fromtbl_Sales
View 7 Replies
View Related
Mar 20, 2014
cast(round([YearlyProfit]-[MonthlyProfit],0)as int),
cast(round(isnull(sum(SalesProfit]),0),0) as int
How can I convert these columns to money so that I can display the result with comma included.
View 2 Replies
View Related
Mar 8, 2006
Ok i know this is simple but i just don't know the syntax. What I haveis a bunch of values and i want to be able to display them as moneyvalues with a $ in front.eg.160000001000160000TO THIS$16,000,000.00$1000.00$16,000.000I'm using MS SQL 2000Cheers
View 1 Replies
View Related
May 1, 2008
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype
Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.
select cast(IsNumeric((Value2)) as money)
from tbl_Sales
View 7 Replies
View Related
Jan 22, 2007
Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.
Here is my code:
Alter PROCEDURE sp_getTxn (
@m1 int = Null,
@txn int = Null,
@p2 int = Null,
@amt int = Null,
@pAccountno varchar(50) = 'Null',
@DAcct int = Null,
@Balance Decimal(19,4) = NULL OUTPUT,
@pBalance Decimal(19,4) = NULL OUTPUT,
@RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON
select CASE
WHEN @m1 = 200 THEN case
when @txn = 00 then ('exec dbo.CustOrderHist (@CrAcct int)')
when @txn = 01 then ('exec dbo.Sp_withdrawal')
when @txn = 31 then exec dbo.CheckBalance(@pAccountno varchar(50), @pBalance Decimal(19,4) OUTPUT)
when @txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @m1 = 420 THEN case
when @txnType = 00 then ('exec dbo.Sp_reversal')
when @txnType = 01 then ('exec dbo.Sp_reversal2')
when @txnType = 31 then ('exec dbo.Sp_reversal3')
END
END
SET @Balance = @pBalance
Print @Balance
Or is there an alternative to the above CASE statement that is easier and faster?
Thanks
View 2 Replies
View Related
Apr 13, 2007
Hi Guys
I need your help again, I am try to update several columns and the data type is 'money'.
Below is the code I have used:
UPDATE CAT_Products SET
UnitCost ='10.00',UnitCost2 = '10.00',UnitCost3 = '10.00',UnitCost4 = '10.00',UnitCost5 = '10.00',UnitCost6 = '10.00'
WHERE ProductCode = '0008'
But it will not update, instead I get this error:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>[Error] Script lines: 1-9 -------------------------- Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query.
More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either.
Thanks
View 2 Replies
View Related
May 12, 2005
Hi,
I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code:
' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) '
Will this work? Is there any reason I should stay away from the "Money" data type?
Thanks,
David
View 3 Replies
View Related
Mar 10, 2006
I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed:
JobNumber varchar 50
Weight real 4
FreightCost varchar 8
TrackingNumber varchar 50
Shipmethod varchar 50
VOIDID varchar 3
I then have a trigger set to update the PACKAGE table as followed
CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
BEGIN
UPDATE PACKAGE
SET WEIGHT = inserted.WEIGHT,
FREIGHTCOST = inserted.FREIGHTCOST,
TRACKINGNUMBER = inserted.TRACKINGNUMBER,
COMMENTS = inserted.SHIPMETHOD
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'N'
UPDATE PACKAGE
SET WEIGHT = '',
FREIGHTCOST = '0.00',
TRACKINGNUMBER = '',
COMMENTS = 'UPS VOID'
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'Y'
END
The format of the PACKAGE table is as followed
Jobnumber varchar 50
FreightCost money 8
TrackingNumber varchar 50
Comments varchar 2000
Weight real 4
I am getting the following error
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query.
---------------------------
OK
---------------------------
How do you use the convert function to change the data before the update? Thank You!
:mad:
View 6 Replies
View Related
Oct 6, 2015
I have a column type of float and How to convert it show like this
default value =39260.80 MY db use this ',' seperator instead of '.'
wanna convert 39,260
This code is working if using '.' seperator
SELECTÂ parsename(convert(varchar,CAST(floor('39260.80') AS MONEY),1),2)
not work using ',' seperator
SELECTÂ parsename(convert(varchar,CAST(floor('39260,80') AS MONEY),1),2)
View 12 Replies
View Related
Dec 7, 2004
I've this Stored procedure on a SQLserver 2000 SP3:
SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @squadra
this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
---------------------------
OK
---------------------------
An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??
View 2 Replies
View Related
Feb 22, 2008
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks
J
declare @date
set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by
case when (@date = '2001') then internalID end desc
View 5 Replies
View Related
Oct 6, 2013
I have the below Ms.Access code that I would to transition into SQL.
Is CCUR a usable function in SQL, or would I have to use the convert to money function?
Charges: Sum(((CCUR([Fee Schedule Rate])*CCUR([Units_Charged]))))
View 1 Replies
View Related
Oct 8, 2007
I need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
Incorrect syntax near '='.
View 1 Replies
View Related
Jul 4, 2007
There is insufficient result space to convert a money value to smallmoney.
It's a huge db with millions of records and we created asp files for it, and now my asp file will only works with smallmoney and not money data type.
As with money data type i am getting error Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'CDbl'
So i want to convert this field of MS SQL server db from "Money" to "SmallMoney" .
But bec db has over million records and i am getting this error.
There is insufficient result space to convert a money value to smallmoney
any way around it in SQL or in asp, i guess it's SQL problem so you guys must be expert on this. Help appreciated.
thx
View 2 Replies
View Related
Feb 12, 2008
I'm looking through some data in a third party application, trying to figure something out for a report. There is a char(1) column holding control characters that apparently effects the status of certain records, and whether they should appear on the report. I want to look at the integer value of those characters rather than the character representation, but I can't get it to work. I keep getting "Error converting data type varchar to numeric." I'm trying to do something like this:
Code:
SELECT CONVERT(int, MyColumn) As IntValue FROM MyTable
I've tried every combination of cast and convert I can think of, as well as numeric data types other than int. There aren't that many distinct values, so I'm just going to do this manually, but I still want to know why it won't work, or what I'm doing wrong. Any thoughts?
View 2 Replies
View Related
Jun 12, 2007
I have a Column called PostNr which is of type Char(4)
I want to be able to Convert it to int
How can I accomplish that in query analyser, I tried changing it in Ent Manager, it keeps timing out.
I data in Post is in this format, does not contain illegal character just number
View 5 Replies
View Related
Sep 29, 2007
Hi all,I have a column LateHours (Varchar). I want to put it in another table which has Latehours column in Decimal(4,2) Example = +04:33 Should be 4.33 (Only + values) Char to Decimal (4,2) Please Help me,Thanks,Janaka
View 3 Replies
View Related
Jun 4, 2008
Hi all,
There are several columns called enabled with a char datatype in my database. One enabled column per table. These columns either have a value of T or F (true or false), depending on whether they're enabled or not. I want to change these columns to a bit datatype and insert the relevant value of true or false...
I guess the best way to do this is to add a new column to a table with a bit datatype, and based on the value in the current enabled column, insert TRUE or FALSE.
Anyone ideas on the best way to accomplish this?
Thanks.
View 4 Replies
View Related
May 31, 2002
I am trying to convert Varbinary to Char it keeps returning this ...
--------------------------------------------------
´å
(1 row(s) affected)
Here is the code that I am using....
declare @name varbinary(50)
set @name =0x12b4e5
SELECT Convert(char(50),@name)
Can someone please help! ~Dana
View 3 Replies
View Related
Mar 28, 2004
Hi,
I have DB2 date value 00000000. If I'm exporting to SQL server using openquery that is automaticaly converting to char of 8 and stored as the same value 00000000.
My question is how I can convert them as datetime value in SQL server 2000.?
View 1 Replies
View Related
Jul 3, 2001
Have a requirement to:
Convert char(7) YYYY-MM variable (eg. '2001-07') to both:
datetime '2001-07-01 00:00:00.000' and
datetime '2001-07-01 23:59:59.997'
thx in advance!
View 2 Replies
View Related
May 23, 2008
hi, how can I convert a float to a char so I can do:
'£' + convert(char,amount) AS [money]
When I do the above the number is like 1.07848e+006
rather than 1078480
thans for any help.
jamie
View 20 Replies
View Related
May 26, 2008
if i use the query
SELECT iditem, CONVERT(char(20), dt, 105) FROM planeamento
it works just fine.. but in this case the dt (date) field is not recognised..
SELECT planeamento.iditem, idmodelo, item_planeamento.idproduto, item_planeamento.idpele, cor, ordemfabrico, qtd, CONVERT(char(20), dt, 105) FROM produto INNER JOIN item_planeamento ON produto.idproduto = item_planeamento.idproduto INNER JOIN planeamento ON item_planeamento.iditem = planeamento.iditem WHERE planeamento.idfabrica = 51 AND alterado = 'FALSE' AND estado = NULL GROUP BY planeamento.iditem, produto.idModelo, item_planeamento.idProduto, item_planeamento.idPele, item_planeamento.cor, item_planeamento.ordemFabrico, item_planeamento.qtd, planeamento.dt
before, id just had "id" where "CONVERT(char(20), dt, 105)" is, but the date was printed in a unwanted format..
can you help me?
thanks a lot!
View 5 Replies
View Related
Dec 26, 2014
declare @new table(
id int not null identity(1,1),
dat char(10))
insert into @new
select'111214'
union
select'121214'
select cast(dat as datetime)from @new
-----------------------
2011-12-14 00:00:00.000
2012-12-14 00:00:00.000
but me need results it
2014-12-11
2014-12-12
View 2 Replies
View Related
Aug 26, 2005
Select Cast('100.1234' as float)give me the result 100.1234Now when I convert it back to char I want exactly 100.1234Select Convert(char(100),Cast('100.1234' as float))Gives me 100.123 (Here I was expecting 100.1234)When I doSelect STR(Cast('100.1234' as float),25,4)I get back the result as 100.1234However here I am not sure how many digits do I have after the decimalpoint. If I put some value likeSelect STR(Cast('100.1234' as float),25,8)I get 0's appended to it, which is again not desired.Thanks in advance,Jai
View 4 Replies
View Related