Convert Money Columns To Update?
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
ADVERTISEMENT
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
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
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
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
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
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
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
Sep 3, 2015
I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?
View 3 Replies
View Related
Nov 23, 2014
When i try to use the following command;
Update (table name)
set (Field name)='0'
where ID='1449' (1449 location of the record).
The "Field name" value is 875 and i need to edit the value to 0(zero).
It appears an error -
----------------------------
Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.table name', column 'field name'. Use the CONVERT function to run this query.
-----------------------------
So if i change the data type from right click design menu to nvarchar(50) can it be edited from the command that i used above?
View 3 Replies
View Related
Oct 16, 2007
I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
View 9 Replies
View Related
Aug 30, 2004
Hi,
I have some data in one table.
I need to get the data in the same table, into different rows.
I mean to say that, I have data like
InvNo InvAmt GstAmt
12 1900 79
and I need to get data like
InvNo Amt
12 1900
12 79
Can anyone help me as to how can i get data in this format.
Thanks.
View 2 Replies
View Related
Jul 11, 2001
My table:
Table1
T1T2T3T4T5
---------------
abcde
How to query to have following result:
Table2
abcde
---------------
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
Thanks in advance.
Janasha H
View 1 Replies
View Related
Aug 21, 2014
I've a table like below
ColA ColB
A12
B22
C45
A56
A34
B23
and I need to convert the rows into column names like below
ABCA1A2B1
122245563423
View 1 Replies
View Related
May 29, 2007
how to convert rows into columns.
Data in table
empcode basic
1 1000
2 2000
Required output
empcode 1 2
basic 1000 2000
View 3 Replies
View Related
Nov 13, 2007
Hi there
Assuming I have the following data where the header represents budget cost and the value represent no. of projects
<2K 2K-5K >5K
--------------------
10 15 5
For the above table: the following is my sql:
SELECT
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL<2000000 THEN 1 ELSE 0 END) <2K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 THEN 1 ELSE 0 END) 2K-5K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=5000000 THEN 1 ElSE 0 END) >5K
FROM
PRJ_PROJECTS AS PRJ
How do I program it in sql such that the data will be displayed as below? Thanks
Budget_Range No_of_prj
-----------------------
<2K 10
2K-5K 15
>5K 5
View 5 Replies
View Related
Jul 23, 2005
Hi All,I need to help with converting rows to columns in SQL2k.Input:Id Name Role58Ron Doe Associate58Mark BonasDoctor59Mike JohnsonDoctor59John SmithAssociate102Chris CarterAssociate102Ron Doe Associate102James JonesAssociateOutput should look like:IdDoctorAssoc1Assoc2Assoc358Mark BonasRon Doe NULLNULL59Mike JohnsonJohn SmithNULLNULL102NULLChris CarterRon Doe James JonesThere could be more than 3 associates in the input but I only need 3above columns for associates.I used following query:SELECT Q.sales_id,doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'and Q2.sales_id = Q.sales_id),assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id)FROM view1 QGROUP BY sales_idand I get this error "Subquery returned more than 1 value" since thereare multiple associate for Id 102.Thenks
View 3 Replies
View Related
Apr 24, 2006
hi
i have a sample data like
patientno visit experimentdate
101 1 23-Dec-2004
101 2 23-Mar-2005
101 3 23-Jul-2005
102 1 23-Dec-2004
102 2 23-Mar-2005
102 3 23-Jul-2005
i want it to display like this
patientno visit1 visit1date visit2 visit2date visit3 visit3date
101 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
102 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
..please suggest how can we do it
View 4 Replies
View Related
Feb 28, 2008
Hi I have a stored proc that pulls in data, I have four columns that, where there is data I need to times by 100 but when empty to remain empty, currently where empty I end up with 0's
Do i need to do a convert or something? can someone help me out thanks
Code Snippet
USE [WINDRUSHDAL]
GO
/****** Object: StoredProcedure [dbo].[spgiffordstelesalesdev2] Script Date: 02/28/2008 12:34:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spgiffordstelesalesdev2]
AS
BEGIN
SET NOCOUNT ON;
SELECT
BLCUSA AS Company,
BLCUSB AS Account,
BLDELN AS DeliverTo,
blcnam AS [Account Name],
BLCTS1 * 100 AS Monday,
BLCTS2 * 100 AS Tuesday,
BLCTS3 * 100 AS Wednesday,
BLCTS4 * 100 AS Thursday,
BLCTS5 * 100 AS Friday
FROM
LIVEAS400.S65C422B.WRFDTA.PARDADR AS PARDADR_1
WHERE
(BLCUSA = '2')
Union
SELECT
abCUSA AS Company,
abCUSB AS Account,
0 AS DeliverTo,
abcnam AS [Account Name],
abCTS1 * 100 AS Monday,
abCTS2 * 100 AS Tuesday,
abCTS3 * 100 AS Wednesday,
abCTS4 * 100 AS Thursday,
abCTS5 * 100 AS Friday
FROM
LIVEAS400.S65C422B.WRFDTA.PARNADR AS PARNADR_1
WHERE
(abCUSA = '2')
END
View 7 Replies
View Related
Apr 10, 2008
Hi,
I have a table which consists of some rows
i want to get rows as columns
For eg:
table1
id name dept
1 x a
2 y b
3 z c
i want to write a select query to fetch result as
1 2 3
x y z
a b c
can anyone help please
View 10 Replies
View Related
Nov 11, 2011
I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?
View 4 Replies
View Related
Sep 17, 2014
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from
[code]....
View 7 Replies
View Related
Apr 3, 2008
Hello All,
I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....
now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.
Current Format :-
code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null
Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null
i hope that i am clear with my question.
Can any one help me?
Thanks
Prashant
View 4 Replies
View Related
Apr 4, 2008
Hello,
Table - 1 Format:-
Line_No Entity_Id Mid
1157530
2157540
3157550
4157560
5157570
6157580
7157590
81575100
Now i need the data into below Format :-
Line_NoEntity_Id Ticker Mid1Mid2 Mid3 Mid4 Mid5 Mid6 Mid7 Mid8
11575 COFP 30 40 50 60 70 80 90 100
Can any one help me out?
Thanks
Prashant Hirani
View 1 Replies
View Related
Nov 14, 2014
I am converting some access to SQL at work and I am having a problem with a query that uses the access FIRST function
My query looks like this in access
SELECT
TBL1.ID
, FIRST(TBL1.Code) AS FirstCode
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND Left(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
I have tried to convert it using a slightly different method. So instead of picking the first value of the Code field that appears in the select, (which is what access would do and SQL can’t do as far as I’m aware). I first select the ID and code with the highest value associated with them. The below script is what I did. I would’ve thought they would produce the same number of records but they don’t.
SELECT
ID
, Code FirstCode
FROM
(
SELECT
ID
, Code
, MAX(Value) MaxValue
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND LEFT(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
, Code
) T1
View 3 Replies
View Related
