?
Solved

Query column formula

Posted on 2016-10-14
23
Medium Priority
?
79 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 38

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 24

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 38

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 24

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 38

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 38

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 38

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 38

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 38

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 38

Expert Comment

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

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 38

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 38

Expert Comment

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

No problem , Bye !!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

579 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