I have a view defined as follows:

SELECT d1.RecordID, d.RecordID AS WellID, d.WellName, d.Operator, d1.ReportDate, 
TIMESTAMPDIFF(SQL_TSI_HOUR, d.Well_Start, d1.ReportDate + 1) / 24.0 AS DaysStart, 
TIMESTAMPDIFF(SQL_TSI_HOUR, d.Well_Spud, d1.ReportDate + 1) / 24.0 AS DaysSpud, 
MDFrom, MDTo, Progress, d.ReportTime 
FROM Well d, Daily d1
WHERE (d1.ParentID = d.RecordID)

On this view I perform a query in my application to select a subset from the view:

SELECT * FROM id_Daily
WHERE WellID = :RecordID
ORDER BY ReportDate

This has always worked fine in ADS 10.x and 11.1. Without code modifications, the query now yields error 5070: "The given data type is not valid for the requested operation. Expected a numeric value. Field DaysStart" (d.WellStart is a Timestamp field and d1.ReportDate is a Date field).

The strange thing is that I can browse the View, but intermittently (!) get the same error message. Has anything changed in ADS 12.0 that causes this error? Perhaps ReportDate must be changed to TimeStamp, but this doesn't seem logical as the SQL worked before without any issue.

Thanks, Mark

asked 07 Feb '16, 22:51

Softdrill%20NL's gravatar image

Softdrill NL
accept rate: 0%

From my perspective the error is right because a timetamp is different to a date. And IIRC I've seen the same error with older versions aswell.


answered 09 Feb '16, 16:43

Joachim%20Duerr's gravatar image

Joachim Duerr
accept rate: 18%

I agree it sounds logic. However, the error has never (!) popped up with version 10.1 and 11.1 (no reports from end users either) and - even more intriguing - the query (on the view) immediately raises the exception, but scrolling through the view in ARC, the exception is only raised intermittently.

(10 Feb '16, 23:08) Softdrill NL

I have now changed to

IIF(d.Well_Start IS NULL, -1000, (TIMESTAMPDIFF(SQL_TSI_MINUTE, d.Well_Start, CAST(d1.ReportDate AS SQL_TIMESTAMP)) / 1440.0)) AS DaysStart

but I still (intermittently!) get the same error. Does any have suggestions to the cause, and more importantly, the solution to this problem? Note that I changed to SQL_TSI_MINUTE as well because I need partial days (i.e. Current - Start Date)

I suppose changing the field type for d1.ReportDate to TimeStamp would be one, but that has several implications with numerous databases requiring an upgrade.

Thanks, Mark


answered 03 Mar '16, 13:48

Softdrill%20NL's gravatar image

Softdrill NL
accept rate: 0%

Maybe a particular set of data makes that TIMESTAMPDIFF to return something else than numeric. (a NULL maybe?)

If you say that sometimes you can scroll thru that view without error, try to scroll the lines one by one and when the errors popup see what is different in that line.

Or even better add a new column CAST(d1.ReportDate AS SQL_TIMESTAMP) - I suppose that this is the source of the error - and see what is inside other than numeric values.


answered 03 Mar '16, 19:50

Titus's gravatar image

accept rate: 0%

Choose to work around it by removing the "problem" calculation from the view and including d.Well_Start. Then created Calculated Fields in Delphi to calculate at run-time. Not the most elegant solution but at least it works.

NEXT PROBLEM; at random (!!) DBImage Fields on my ReportBuilder reports generate a Stream Read Error when previewing the reports. These images are simple logo's stored in ADS BLOB fields.

What's going on? Previous upgrades (i.e. to 10.x and 11.x) went flawless but this upgrade to ADS 12 suddenly yields all sorts of issues that were never experienced before (i.e. the Delphi code must be good; the program is "in production" with no reported issues, using ADS 10 & 11)

What to do? Re-install ADS? With all the waiting for components etc, this is now becoming a truly painful process! Only reason for not "pulling the plug" on ADS (yet) is that I have invested heavily in Delphi written Stored Procedures (too complex for SQL). Does anyone know of a reliable DB that can use Delphi SP's?

Thanks, Mark


answered 04 Mar '16, 17:18

Softdrill%20NL's gravatar image

Softdrill NL
accept rate: 0%

For information: issues solved.

The "image issue" was due to some corrupted BLOB content. Not sure if this was caused by the upgrade, but replacing the images in problem records solved the issue.

The other issue was (apparently) caused by the /1440.0 part. This resulted in an Integer divided by a Float, causing the error. Changing to /1440 already solved it, but because I require fractional days I add an explicit cast:

IIF(d.Well_Start IS NULL, -1.0, CAST(TIMESTAMPDIFF(SQL_TSI_MINUTE, d.Well_Start, CAST(d1.ReportDate AS SQL_TIMESTAMP)) AS SQL_DOUBLE) / 1440.0) AS DaysStart

Rgds, Mark

(06 Mar '16, 20:27) Softdrill NL
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 07 Feb '16, 22:51

Seen: 2,752 times

Last updated: 06 Mar '16, 20:27

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.