[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

Query column formula

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
emi_sastra
Asked:
emi_sastra
  • 11
  • 10
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
Snarf0001Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Snarf0001Commented:
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
 
emi_sastraAuthor Commented:
Hi All,

The data is (60000/25).

Thank you.
0
 
emi_sastraAuthor Commented:
Hi All,

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

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
emi_sastraAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
emi_sastraAuthor Commented:
Failed for this.

(60000*2/25)

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

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
emi_sastraAuthor Commented:
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
 
emi_sastraAuthor Commented:
We can not use fixed rule to query it, just like substring.

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
Can you give me your complete requirement, what do you want to achieve.  Table data, schema etc..
0
 
emi_sastraAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
This is how you are storing values in the database.
0
 
emi_sastraAuthor Commented:
(UMT /SHIFT ) become  (6000/25)
The UMT may be vary based on each individual employee.

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
Okies, will do this in some time.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
emi_sastraAuthor Commented:
Hi Pawan,

It works.

Thank you very much for your help.
0
 
Pawan KumarDatabase ExpertCommented:
Dear emi_sastra,

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

Thank you !

Regards,
Pawan
0
 
emi_sastraAuthor Commented:
Hi Pawan,

I am sorry.

I thought I have done it before.

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
Thank you emi_sastra !!

No problem , Bye !!
0

Featured Post

Technology Partners: 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!

  • 11
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now