Solved

Query column formula

Posted on 2016-10-14
23
49 Views
Last Modified: 2016-10-31
Hi All,

I want to query formula from column of database.


 UPDATE B
SET AmountBasedOn = CAST(B.SalaryFormulaValue AS money) * -1
 
FROM THMSALARY AS A
INNER JOIN TDMSALARY AS B
ON A.Oid = B.HeaderSalaryID
WHERE SalaryFormula LIKE '%UMT%'

Msg 235, Level 16, State 0, Line 6
Cannot convert a char value to money. The char value has incorrect syntax.

How could I do it ?

Thank you.
0
Comment
Question by:emi_sastra
  • 11
  • 10
  • 2
23 Comments
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41843860
Pls try.. This looks like a data issue.

--

UPDATE B
SET AmountBasedOn = CASE WHEN TRY_CAST(B.SalaryFormulaValue AS MONEY) IS NOT NULL THEN CAST(B.SalaryFormulaValue AS money) * -1 END
FROM THMSALARY AS A
INNER JOIN TDMSALARY AS B
ON A.Oid = B.HeaderSalaryID
WHERE SalaryFormula LIKE '%UMT%'



--

Open in new window

0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 41843977
Can you post some of your data?  The conversion to currency would be expecting only numbers and decimals, so right off the bat would fail with the "UMT" that it's specifically expecting.

Just removing the UMT would be a start, but I'm guessing that's not the only character value you have in that column.

If you can include what the actual data looks like can offer suggestions.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844037
Try this. You need to remove the Money in casting.

--

UPDATE B
SET AmountBasedOn = CAST(ISNULL(B.SalaryFormulaValue,0) AS FLOAT) * -1
FROM TDMSALARY AS B
INNER JOIN TDMSALARY AS A ON A.Oid = B.HeaderSalaryID
WHERE SalaryFormula LIKE '%UMT%'

--

Open in new window


Also try..

SELECT CAST('9,000,000' AS MONEY)
SELECT CAST('9.000.000' AS MONEY)

Open in new window

0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 41844127
That's not going to make any difference, the money vs float isn't the issue, it's the fact that there's characters in it.

WHERE SalaryFormula LIKE '%UMT%'

You can't cast '9,000,000 UMT' to either a float or money.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844383
Hi All,

The data is (60000/25).

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844389
Hi All,

It could be like excel formula, use IF().

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844581
Try this..

UPDATE B
SET AmountBasedOn = CASE WHEN TRY_CAST(B.SalaryFormulaValue AS FLOAT) IS NOT NULL THEN CAST(B.SalaryFormulaValue AS FLOAT) * -1 END
FROM TDMSALARY AS B
INNER JOIN TDMSALARY AS A ON A.Oid = B.HeaderSalaryID
WHERE CAST(SalaryFormula AS VARCHAR(1000)) LIKE '%UMT%'

--

Open in new window


Also if the above is not working then could you post your table schema and few rows with expected output.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844602
Hi Pawan,

SELECT
SalaryFormulaValue
 --, CASE WHEN TRY_CAST(B.SalaryFormulaValue AS FLOAT) IS NOT NULL THEN CAST(B.SalaryFormulaValue AS FLOAT) * -1 END AS  A
 , CAST('(60000/25)' AS FLOAT) AS B
FROM TDMSALARY AS B
WHERE SalaryFormulaValue IS NOT NULL

I get error :

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844612
Try..

--
SELECT
SalaryFormulaValue
 ,CAST( CAST(SUBSTRING('(60000/25)',2,CHARINDEX('/','(60000/25)',0)-2) AS FLOAT) * 1. / CAST(SUBSTRING('(60000/25)',CHARINDEX('/','(60000/25)',0)+1,2) AS FLOAT) AS NUMERIC(10,2)) B
FROM TDMSALARY AS B
WHERE SalaryFormulaValue IS NOT NULL

--

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844619
Failed for this.

(60000*2/25)

You can not use substring, since its data may very.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844622
You are changing data again and again, given me complete data , will do it for that. Can you post complete data and the output you need. :)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:emi_sastra
ID: 41844624
Not changing data.
Because it is formula, and stored at database, could be anything, but it is math data.

For example :

1. (6000*2/25)
2. (6000/25*50%)

I wonder, is there function to convert string to queryable data.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844625
We can not use fixed rule to query it, just like substring.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844626
Can you give me your complete requirement, what do you want to achieve.  Table data, schema etc..
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844632
Something like .

1      (UMT /SHIFT )
2      ((UMT /SHIFT ) * 50% )
3      (UMT/SHIFT)
4      (UMT/SHIFT)
5      (UMT/SHIFT)
1      (GP/ SHIFT)
2      (GP/ SHIFT)
6      ((UMT / 10800) * MINUTE)
7      (UMT/SHIFT)

The GP, SHIFT, UMT and MINUTE will be feeded by application into Number.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844643
This is how you are storing values in the database.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41844665
(UMT /SHIFT ) become  (6000/25)
The UMT may be vary based on each individual employee.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41844666
Okies, will do this in some time.
0
 
LVL 23

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41844715
Try this..

sample Data

--

CREATE TABLE Split
(
	Vals VARCHAR(1000)
)
GO

INSERT INTO Split VALUES
('(6000 /20 )'),
('((1000 /20 ) * 50% )'),
('(6000/15)'),
('(10000/20)'),
('(10000/50)'),
('(90000/ 90)'),
('(6000/ 100)'),
('((6000 / 10800) * 5)'),
('(10000/20)')
GO

--

Open in new window


Query

--

SELECT 
CAST
(
	( CAST(RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0, CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0))))
		   AS FLOAT) * 1.00

	/

		   CAST(CASE WHEN CHARINDEX('*',RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%','')))))) > 0 
		   THEN
		   SUBSTRING(RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''))))),0,CHARINDEX('*',RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''))))))) 
		   ELSE 
		   RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%','')))))
		   END AS FLOAT) )

	*
	(
		   CAST(CASE WHEN CHARINDEX('*',RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%','')))))) > 0  	   
		   THEN
		   SUBSTRING(RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''))))),CHARINDEX('*',RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''))))))+1,LEN(RTRIM(LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),CHARINDEX('/', REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''),0)+1,LEN(REPLACE(REPLACE(REPLACE(REPLACE(Vals,'(',''),')',''),'''',''),'%',''))))))) 
		   ELSE 
		   '1'
		   END AS FLOAT)

	)
AS NUMERIC(10,2)) Outputs
FROM Split

--

Open in new window


Output

Outputs
------------------------
300.00
2500.00
400.00
500.00
200.00
1000.00
60.00
2.78
500.00
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41846022
Hi Pawan,

It works.

Thank you very much for your help.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41866420
Dear emi_sastra,

Could you please select the answer that helped you and properly close the question.

Thank you !

Regards,
Pawan
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41866464
Hi Pawan,

I am sorry.

I thought I have done it before.

Thank you.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41867872
Thank you emi_sastra !!

No problem , Bye !!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS ReportViewer report timeout 7 99
Converting Teradata SQL to Oracle SQL (exadata) 3 28
Sql Count with Select Distinct 4 24
Sql Query Datatype 2 14
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now