Strange date result SQL script

juleshartley — Sat, 16 Nov 2024 04:56:17 GMT

Hi all,

I have a very strange issue! We are loading some data using a simple sql script from a view:

ie. select * from AView

There is a field in the view called 'datefeesreceived' that is 0 in the database for certain rows, but when it is viewed in the qvw is shows a date (and I don't mean 30/12/1899 or similar but 24/6/2016!).

If I add a 'where' clause to the sql statement so that it only returns the rows that I expect to have 0 value then they come back with 0!

I cannot understand what is going on...

Thanks!

Re: Strange date result SQL script

Nicole-Smith — Tue, 18 Dec 2018 17:49:54 GMT

QlikView uses "duck typing" where it will treat the field like whatever it looks to be. Since you have a combination of numbers and dates, it treats it like the date, but when it's only the number, it treats it like a number. A single column will only be cast as one data type--so when you have a combination of multiple, it's going to choose one or the other, not both. It's really poor design that there is a mixture of two data types in a single column in your SQL view--those zeroes really should be NULL values. If you're unable to update the view itself, I would add a case statement to your SQL script to update those zeroes to NULLs, then everything should flow through properly.

topic Strange date result SQL script in QlikView

Strange date result SQL script

Re: Strange date result SQL script