T-SQL (SS2K8) :: XQuery Syntax To Evaluate Math Formula

Feb 25, 2015

I want to evaluate a math formula inside of a function, so I can't use dynamic SQL.

I found that the query on an XML-variable with a literal works well.

DECLARE @sParsedFormula varchar(200);
DECLARE @xFormula xml;
SET @xFormula = '';
SET @sParsedFormula = '1+2';
SELECT @xFormula.query('3+3') , @xFormula.query('sql:variable("@sParsedFormula")')Output (1st value is correctly evaluated, 2nd not):
"6", "1+2"

I can't directly pass @sParsedFormula to the query otherwise I get "The argument 1 of the XML data type method "query" must be a string literal". What is the correct XQuery-syntax to evaluate the SQL:variable?

View 2 Replies


ADVERTISEMENT

Need To Evaluate Stored String Formula

Jun 18, 2007

In 'MyTable' I have the following columns...

TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)

'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.

Here are some examples of the actual strings it stores...

Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)

etc etc

All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.

Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.

I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.

Here is the vb version i use in Ms Access...

getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)

If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If

'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)

'Evaluate the math of the literal expression
getFormula = Eval(strExpression)

-----------

Many Thanks

View 1 Replies View Related

T-SQL (SS2K8) :: Divide Using Formula From Different Table

Jan 23, 2015

I need to evaluate a formula in sql server 2008

Table 1 contains

Entity Value
A 2424053.500000
B 1151425.412500
C 484810.700000
Table 2 contains

Entity Formula
A (2100*(1-0.0668)*24*mday*10)
B (1000*(1-0.0575)*24*mday*10)
C (1260*(1-0.09)*24*mday*10)

Where mday is number of days taken from user

I need to calculate the output of value/formula for each entity can you provide me the query for the same

The datatype for formula column is varchar

I do not have the liberty to use cursors or loops.mday will be a input fromt the user say 'mday = 31' ..i need to divide the value in the first table with the computed value of the formula after replacement

View 1 Replies View Related

T-SQL (SS2K8) :: How To Perform Mathematical Formula Without Using Case

Sep 17, 2014

I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.

I want to perform following without using case if it is possible.

When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty

And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.

Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.

View 9 Replies View Related

T-SQL (SS2K8) :: Arithmetic Formula To Stuff Digits

Apr 6, 2015

I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.

SELECT Cg.Fiid,
--Original Formula
SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),
--My solution
STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid)))
FROM (VALUES(71927),
(498932),
(498934),
(38061278))Cg(Fiid)

View 9 Replies View Related

Reporting Services :: Add Formula To Formula Bar In Excel

Sep 1, 2015

In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.

View 3 Replies View Related

T-SQL (SS2K8) :: Syntax Error With Trigger

May 9, 2014

I have a syntax error...

[code = "sql"]CREATE TRIGGER Trigger_20321 ON FACT_CUST_GRP_ICM_MO
AFTER DELETE
AS
/* DELETE trigger on FACT_CUST_GRP_ICM_MO */
/* default body for Trigger_20321 */

[Code]...

Msg 102, Level 15, State 1, Procedure Trigger_20321, Line 21
Incorrect syntax near ')'.

The error is in line: raiserror (@errno, @errmsg)

View 1 Replies View Related

T-SQL (SS2K8) :: Incorrect Syntax Near Keyword (pivot)

Oct 8, 2014

I'm struggling with one Syntax error

CREATE TABLE #ToolCompliance
(
SOFTWAR_ID INT
,CONTROL_CODE VARCHAR(100)
,CONTROL_STATUS VARCHAR(100)
)
INSERT INTO #ToolCompliance
VALUES(1000,'AC','SUCCESS')

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:

"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."

What is the cause of this issue?

Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,

[Code] ....

View 3 Replies View Related

T-SQL (SS2K8) :: OPENQUERY Syntax To Insert Into Server Table From Oracle Linked Server

Aug 28, 2014

I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.

View 7 Replies View Related

XQuery Error

Jan 31, 2007

Does anyone know why this works:
SELECT [MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable]
But this produces an error:
SELECT [MyDatabase].[dbo].[MyTable].[MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable]
 
System.Data.SqlClient.SqlException: Invalid column name 'MyXmlColumn'.

View 7 Replies View Related

XQuery Vs OpenRowset

Apr 5, 2006

Please let me know the advantages and disadvantages of XQuery vsOpenRowSet in SQL Server 2005. Which would be better?Regards,Shilpa

View 1 Replies View Related

Xquery Help SqlServer2005

Nov 22, 2006

I have the following XML in an XML column in a SQL 2005 Database.<DeliveryList xmlns="http://schemas.adventure-works.com/DeliverySchedule"><Delivery SalesOrderID="43659"><CustomerName>Steve Schmidt</CustomerName><Address>6126 North Sixth Street, Rockhampton</Address></Delivery><Delivery SalesOrderID="43660"><CustomerName>Tony Lopez</CustomerName><Address>6445 Cashew Street, Rockhampton</Address></Delivery></DeliveryList>I need to query that column using the value method to retreive the addressof the first delivery. I come up with this,SELECTDeliveryList.value('data((/DeliveryList/Delivery/Address)[1])','nvarchar(100)')DeliveryAddressFROM Sales.DeliverySchedulebut it tells meXQuery [Sales.DeliverySchedule.DeliveryList.value()]: There is no elementnamed 'DeliveryList'I have tried every permutation of the path and I can not seem to get it towork? Any ideas?

View 2 Replies View Related

Xquery Return On If Node Has A Value

Sep 29, 2007

Hi,I am using xquery to extract XML fragments from my database. How do I xquery a table containing XML (typed) and return only nodes that meet the xquery.For example, the following returns empty cells if there is not @id = "op1". How I I only return values and not empty cells?  SELECT xmldata.query('declare namespace ACM="www.mydomain.com"; (

for $p in //ACM:oproc[@id = "op1"]

return $p

)' ) FROM mytable   Thanks

View 1 Replies View Related

Yukon XQuery Question

Jul 27, 2005

I am trying to query data in an XML variable and I am having troublegetting the data out in the format that I would like. Given thefollowing XML:declare @myDoc xmlset @myDoc = '<OwnershipTeamGuid="23EA393A-3926-4A55-8329-FE747593379D" TeamName=".NETFramework"><Roles><Role Guid="93BDB4E2-95A0-438D-96E4-43032EB2BA16" Name="API ReviewPM Contact" ContactAlias="John.Smith" Inherited="0" ArtifactName="" /><Role Guid="8A7145EF-B48B-4A93-BFA5-28B53995C022" Name="Architect"ContactAlias="John.Doe" Inherited="0" ArtifactName="" /><Role Guid="BF727283-B18C-415A-A38C-CEC15CD341D9" Name="Corp VP"ContactAlias="None" Inherited="0" ArtifactName="" /></Roles></Ownership>'What I would like to do is run an XQuery against the variable and getattribute data back in normal rows and columns like the following:Guid NameContactAlias------------------------------------ ---------------------------------93BDB4E2-95A0-438D-96E4-43032EB2BA16 API Review PM Contact John.Smith8A7145EF-B48B-4A93-BFA5-28B53995C022 Architect John.DoeBF727283-B18C-415A-A38C-CEC15CD341D9 Corp VP NoneI've checked the Query and Value methods and have not been able to getthem to do this for me. Is this even possible w/XQuery? Do I need touse OpenXML to do what I am trying to accomplish?Thanks for your help!

View 2 Replies View Related

Where To Write XQuery Statements?

Mar 27, 2006

Where to write XQuery statements?

View 3 Replies View Related

Some XQuery/SQL Server 2005 Help Please

Nov 27, 2006

This querySELECT DeliveryList.query('declare default element namespacehttp://schemas.adventure-works.com/DeliverySchedule;<DeliveryRoute><RouteNo>{ sql:column("D.DeliveryRoute") }</RouteNo>for a$ in /DeliveryList/Delivery/Addressreturn<Address>a$</Address></DeliveryRoute>')as ResultFROM Sales.DeliverySchedule DReturns this XML<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule"><RouteNo>3</RouteNo>for a$ in /DeliveryList/Delivery/Addressreturn<Address>a$</Address></DeliveryRoute>I need it to return this XML<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule><RouteNo<3>/RouteNo><Address>6126 North Sixth Street, Rockhampton</Address><Address>6445 Cashew Street, Rockhampton</Address></DeiveryRoute>What am I doing wrong? Any ideas? All help is appreciated.TIA, ~CK

View 1 Replies View Related

Update Query Using XQuery

Nov 27, 2006

UPDATE Sales.DeliveryScheduleSET DeliveryList.modify('declare namespacedf=http://schemas.adventure-works.com/DeliverySchedule;replace value of (df:DeliveryList/df:Delivery/df:Address)[1]' with "7194Fourth St., Rockhampton"')WHERE Sales.DeliverySchedule.ScheduleID = 1This query gives me the following errorXQuery [Sales.DeliverySchedule.DeliveryList.modify()]: Invalid sourcecharacter 0x2019Any ideas? What am I doing wrong here? Thanks for any advice.TIA,~CK

View 2 Replies View Related

XQuery Requires QUOTED_IDENTIFIER

Dec 13, 2005

I am working for a client that would has XML data being passed into a Service Broker queue.

View 8 Replies View Related

Xquery Return Limited Nodeset

Dec 6, 2007

Im using the Xquery:SELECT @xmlDoc.query('
for $item in (/Collection/Content)
where $item/Html/root/DocInfo/Webinar = "White Paper"
order by $item[1]/Html[1]/root[1]/DocInfo[1]/Title[1] ascending
return $item
');
 I only want to return the TOP 5 nodes (not the entire nodelist). What is the FLOWR expression or simplest way to return the limited nodeset?
Thanks

View 1 Replies View Related

Query Data From XML Column With XQuery

Jan 22, 2013

I am trying to learn XQuery and Xpath in SQL Server. I created a sample file and uploaded it to a Table with 2 columns ID, XMLDoc. The below code is within the document in the XMLDoc column so it is the only record in the column.

I am trying to query the file so it will show all the results in a table like a normal select statement would. How would you construct the select statement to select all the information like a select * ? How would you select one field like all suppliers? I would like to select the supplier, requestor for each item.

Here is the xml:

Code:
<tst:Document xmlns:tst ="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Classification="Test" UniqueIdentifier="1234" Title="Test">
<tst:Revision RevNumber="0" TimeStamp="2013-01-21T12:56:00">
<tst:Author Name="Me" Guid="1234" />
</tst:Revision>

[Code] ....

View 1 Replies View Related

SQL 2012 :: XQuery Delete From XML Variable

Nov 20, 2014

I'd like to delete A3 from the XML variable below, however I get "XQuery [modify()]: The XQuery syntax '/function()' is not supported."

DECLARE @XML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A3>6529</A3>
</Attribute>'

DECLARE @n VARCHAR(100) = 'A3'

SET @XML.modify('delete (/Attribute/sql:variable("@n")[1]) ')
SELECT @XML

View 3 Replies View Related

Xquery Support In SQL-Server 2005

Jul 23, 2005

Hi,does anyone kown if the final release or one of the next Betas ofSQL-Server 2005 will support XQuery-Statemets including the LET-Feature.Thanks,Tobias

View 1 Replies View Related

Getting All Nodes And Node Details From Xml Using Xquery

Apr 8, 2006

Hi,I haev the following code:DECLARE @x xmlSET @x='<Root><row id="1"><name>Larry</name><oflw>some text</oflw></row><row id="2"><name>Joe</name></row><row id="3" /></Root>'exec sp_xml_preparedocument @idoc OUTPUT, @xSELECT * FROM OPENXML(@idoc, '/Root')This gives the following detailsidparentidnodetype localnameprefixnamespaceuridatatypeprev textI want to get the same details using XQuery, please let me know how togo about it.Regards,Shilpa

View 2 Replies View Related

Xquery - Select Not Returning All Attributes / Values

Jan 29, 2013

I have a table:

Code:
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED

[Code] .....

With a schema structure:

Code:
<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Baseid="34433" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>

[code]....

I am trying to return the id, number, name, and location of the visitors

Something like:

Code:
RevNumber Function Id Number Visitor Location Sender
========= =========== ======== ======= ======== ======
0 A1 1 Dev01 STLRF FGY(14A)
0 A1 1 Dev02 STLRF FGY(14A)
0 A1 1 Dev03 FGRTY FGY(14A)
0 A2 1 GHFF NULL W33R
0 A2 2 UDT NULL RJ4

Here is the table insert

Code:
INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
BULK 'C:Users123DesktopPractice.xml',
SINGLE_BLOB) AS x;

I Have gotten a little further, but the number is not showing for A2 and the Sender, visitor is showing null.

Code:
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )
SELECT
Document.value('@Title' , 'NVARCHAR(MAX)') Title,
Functions.value('@Id', 'NVARCHAR(MAX)') Functions,
A1.value('@Number', 'INT') Number,

[code]....

Results I am getting that are not all correct:

Code:
Title Functions Number VisitorName Location Sender
======== ========= ====== =========== ======== ======
Ordering A1 1 Dev01 STLRF NULL
Ordering A1 1 Dev02 STLRF NULL
Ordering A1 1 Dev03 FGRTY NULL
Ordering A2 NULL NULL NULL NULL

Not I changed Rev to Title

View 1 Replies View Related

Transact SQL :: Blank Element In XML Not Read By XQuery

May 14, 2015

declare @inputXml xml
set @inputXml='<root>
  <row>
    <col>start_date</col>
    <col>1</col>
    <col>rpton#on#13-May-2015|$|rpton#on#13-May-2015</col>

[Code] ...

I get below output ....

But I need below output ....

View 7 Replies View Related

Math Or Text

Dec 13, 2006

in the below sql why is    year(classdate)  " + " a " + " MONTH(classdate)  a math command giving me   2006 - 12 = 167
and not "2006/12" as text? please help me
 cmdGetCat = New SqlDataAdapter("SELECT DISTINCT   year(classdate)  " + " a " + " MONTH(classdate) AS  monthcode  FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL) ", conNorthwind)
 
 

View 4 Replies View Related

Sql Math Question

Nov 18, 2007

How do i update a sql table so that the result cannot be less than zero?for example, lets say I have the column "Number"I have a sql update statement that subtracts 1 from number:"Update oTable SET Number = (Number - 1)"Except that Number cannot be less than zero.  Is there a way to do this in sql statement so that I don't have to have a select statement just to check that Number is greater than zero to begin with? Thanks 

View 4 Replies View Related

Doing Math On Tables

Oct 26, 2004

Hello everyone:

I have a database for one of my websites, a picture rating site. Anyways, right now there are quicte a few tables, and I was wondering how to give the server a break and was wondering if this was possible:

Basicly I have a members table, and a votes table. Members will rate other users pcitures on a scale of one to ten, then the votes will be inserted into the votes table. The only problem with this is that calcuating all the votes a user has can put a straing on the server. I was wondering if it would be possible to create a math column in the members table that would automaticly figure out the users average and having it stored in a field in the members table, so all I would have to do is query the members average located in the mebers table, rather than tallying all the votes in the votes table for each member.

Hope this makes sense, a tutorial or any suggestions would be great!

Thanks

View 1 Replies View Related

Some Math Logics

Feb 2, 2004

I have 2 questions.

1) Lets say we have a table

CREATE TABLE TEST
(
N INT
)

These table have 10 records - the numbers from 1 to 10.

I need 1 query ONLY which will update the table and make it with 100 records - the numbers from 1 to 100.

2) How with 1 query ONLY i have select only the prime numbers

View 4 Replies View Related

Date Math In SQL - How To Do?

Jan 16, 2006

I have some dates stored in a field named "visit"

In a select they show as the format:
2005-07-28 10:45:00.000


So I need to write a query that will select Visits that are more than 90 days old. I thought it might be something simple like:

Select * from patientVisit where ((getdate())-Visit>90)

But that is pulling all visits not just 90 day old ones.

How do I pull the current date/time and compute the cutoff date time that would be 90 days prior for my selection Where clause?

Thanks

View 3 Replies View Related

Math Between Columns

Jun 30, 2006

I need to create a new column where I subtract one column by another.
I've been looking fo that for a while now, can someone explain how to do this?

View 7 Replies View Related

Math Functions

Mar 31, 2006

Hi,How to find the list of SQL math functions in SQL Server 2005?ThanksKai

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved