Xacute search within SQL results

[former_member67937]

I have a query that is pulling data, and I want to return a specific value from the results of that query, so I'm using the following to do this:

sqlData.Results{/Rowsets/Rowset/Row[Productid = #locProductID#]/StartDate}

And this works. The problem comes into play when I try to include a date, for example:

sqlData.Results{/Rowsets/Rowset/Row[Productid = #locProductID# and StartDate >= #locStartDate# and EndDate <= #locEndDate#]/StartDate}

The problem is that whenever I include the dates, it doesn't return any data. All dates are in XML format.

Thanks for any help.

Todd

[former_member67937 · Accepted solution]

Logically, no. We take a "before" image of the data, and then delete the records, perform some calculations, then write the new data back, and I need to compare the "before" to the "after", and I was hoping to just do it as an XML file instead of me having to create a temporary table.

[Former Member · Accepted solution]

Xpath 2.0 accepts date types.. but I think xMII 11.5 uses Xpath 1.0 ; so doing date compares are pretty much impossible. however.

What you could do, is write a stored procedure to do the calculation for what you want. Instead of using a temporary table, , with the amount of users you have, using a temp table could be bad, use a table variable. Unfortunately you have to use and SP, as you can't use table variables in your query template with your existing JDBC driver, I've tried

I wrote an SP that did a pretty complex query / compare / write function like this.. if you want I can try to dig it up and email it to you.

[HariCS23 · Accepted solution]

I guess Search using xPath is same as using Generic sort and filter action block in BLS.

That action block has only two options in filter..number and text.

So,your Best bet is to convert into number in sql or BLS.

In BLS,define a document with the same structure with date as number...and loop through your sql o/p and form a new xml with converting your date into number with your own logic. ( datefromxmlformat( xmldate,dateformat)..may help you in converting date to string or number )..

-Hari

[former_member67937]

I think I'm going to use the solution of converting the datetime to seconds, and then performing the search that way. The number of records being returned should only be less than a dozen. If it was returning hundreds or so records, then I would go with Doug's option.

Thanks for all your help!

Edited by: Todd Reddy on Oct 23, 2009 3:37 PM

[Former Member]

One thing to consider when looking at a BLS solution vs SQL is the execution speed. If your going to process thousands of rows, for example in a year to date report, your much better off using a SQL based solution. If not, your probably just as well using BLS. For reference, I had a 10,000 record query that I tried to process / calculate / store in BLS, and the transaction would take nearly 2 minutes. When I moved to a stored procedure, I dropped the execution time to 20 seconds.

[jcgood25]

Take your xml rowsets doc and do a Calculated Column action on it with the datetoseconds function. This will give you a column with the long integer equivalent and make it very condusive to sorting, filtering, etc.. Then Generic Sort Filter action it or do your xPathing as desired on the numeric column. You'll of course need to make your local properties the equivalent datetoseconds you need (not the datetime you probably have).

[former_member67937]

So I would have to convert the date to an number to be able to compare?

[Former Member]

You could, I guess. Can you pass the start date and end date into your SQL Query and let the database filter the records so you don't have to in Xacute?

[former_member67937]

<?xml version="1.0" encoding="UTF-8"?><Rowsets DateCreated="2009-10-22T14:39:48" EndDate="2009-10-22T00:00:00" StartDate="2009-10-22T00:00:00" Version="11.5.2"><Rowset><Columns><Column Description="LineID" MaxRange="1" MinRange="0" Name="LineID" SQLDataType="4" SourceColumn="LineID"/><Column Description="ProductID" MaxRange="1" MinRange="0" Name="ProductID" SQLDataType="4" SourceColumn="ProductID"/><Column Description="RunStartDate" MaxRange="1" MinRange="0" Name="RunStartDate" SQLDataType="93" SourceColumn="RunStartDate"/><Column Description="RunEndDate" MaxRange="1" MinRange="0" Name="RunEndDate" SQLDataType="93" SourceColumn="RunEndDate"/><Column Description="StartupTETarget" MaxRange="1" MinRange="0" Name="StartupTETarget" SQLDataType="2" SourceColumn="StartupTETarget"/></Columns><Row><LineID>1</LineID><ProductID>369</ProductID><RunStartDate>2009-10-21T23:00:00</RunStartDate><RunEndDate>2009-10-22T07:00:00</RunEndDate><StartupTETarget>0</StartupTETarget></Row><Row><LineID>1</LineID><ProductID>369</ProductID><RunStartDate>2009-10-22T07:00:00</RunStartDate><RunEndDate>2009-10-22T10:43:00</RunEndDate><StartupTETarget>0</StartupTETarget></Row><Row><LineID>1</LineID><ProductID>369</ProductID><RunStartDate>2009-10-22T10:43:00</RunStartDate><RunEndDate>2009-10-22T14:39:48</RunEndDate><StartupTETarget>34.6</StartupTETarget></Row></Rowset></Rowsets>

[Former Member]

I don't believe you can do date comparison filters with XPATH.

[Former Member]

Todd,

Could you post a sample of your query output XML?