Solved

Query column formula

Posted on 2016-10-14
23
44 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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

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

Accepted Solution

by:
Pawan Kumar Khowal 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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

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

No problem , Bye !!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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