Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query column formula

Posted on 2016-10-14
23
Medium Priority
?
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 2
23 Comments
 
LVL 32

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 23

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 32

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 23

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 32

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 32

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 32

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
 
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 32

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 32

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 32

Expert Comment

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

Accepted Solution

by:
Pawan Kumar earned 2000 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 32

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 32

Expert Comment

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

No problem , Bye !!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 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