How to query one day where date = number massive data set

[mp802377]

I have a massive data set. Hundreds of millions of rows and the data is going back 3 years. I only need a handful of days. I am creating a QVD for each day. I have done this many times before, but the date field was always a day. Below is how the date is in the SAS database. 

My query

SVC:

Load *;

SQL Select

*

From SASTable.SVC

where date = '44482';

I get a syntax error. I have tried without the tick marks. As I said, I have done this before with large datasets, but the date fields were all in a date format. Never a number. I pulled in the first 1000 rows to see the date format (see below)

[JonnyPoole]

I would try as a number (without single quotes) 

--> where date = 44482;

If it fails, please share the error.

[mp802377]

Here is the error message when I remove the ' marks. It works if I do a where statement on any other field that is not a date.

[marcus_sommer]

A real date is always a number - any kind of separating and ordering year/month/day information in a short/long version and any delimiter between them is a formatting which may have even an own data-type from a data-base point of view.

This means you need to look within the data-base how the field is declared and further in the help how to address an access syntactically in the right way. Pure numbers shouldn't be require any quoting but by all other data-types you will probably need any - it depends on the data-base if it are ' or " or ` or ´ or ....

A shortcut to the above would be to use a query/load-wizard in the data-base / Qlik just to see the used syntax by applying a condition to this field. 

[mp802377]

That is the problem, right. In SAS, when they pull the data from SAS Enterprise Guide, they have (I have as well), pull the data like below. But neither works in Qlik. Same error. And they tell me it is a Qlik issue, works for them. I have pulled a ton of data from SAS, querying by dates. Nothing like this before. And I have used both the first and second queries below. I used the wizard (thank you, I forget about that). Tried the query with the date format (see images) and I still get the syntax error. 

select *
from saslibrary.mytablename
where date = '04Mar2024'd

or

select *
from saslibrary.mytablename
where date = today() - 8

[JonnyPoole]

I don't have a place to test SAS but I can see Qlik not looking at the trailing 'D' in the where clause and that causing an error:

'04Mar2024'd

Maybe construct a variable like this:  

let vDate=chr(39) & '04Mar2024' & chr(39) & 'd'

(which would evaluate to '04Mar2024'd)

and try some variations of using that variable in the WHERE clause

• where date = $(vDate);
• where date= '$(vDate)';

or maybe you need to try escaping the single quotes to ensure they persist. Some ideas to try here:

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/use-quotes...

[marcus_sommer]

It's definitely not a Qlik issue because Qlik doesn't execute the query else just transferring the statement as string per driver to the data-base and get on the reverse direction the query-results. The error-message shows this also by stating a sql-error.

This means either the syntax with the single-quotes is wrong or the applied value doesn't fit to the data-type. I don't know this type of data-base but I wouldn't be surprised if the data-type "SQL_DATETIME (9)" isn't a date else a timestamp. Further be careful by taking the shown preview-data (regardless of the tool) as an absolute truth because the previews may display the data in a formatted respectively interpreted way.

Beside looking on the data from the data-base point of view you may do it from the Qlik side by ensuring that only this query is performed without any transformations (some TOP or FIRST logic to avoid to load the millions records would be sensible - and maybe also an extra text(date) as dateText in the preceding part to prevent any interpreting on the Qlik side) - none loads of any data in beforehand or afterwards and then pulling the relevant fields in a table-box and exporting it to a txt-file and opening it with an editor like Notepad++. Maybe the table-box and/or the editor-view provides some hints on the real existing data.

Another approach could be to apply some kind of cast- and/or date-functions on the queried field and/or the wanted filter-value. If it would be a direct Qlik load it might be look like:

...
resident X
where floor(datetime) >= makedate(2024, 3, 1) and floor(datetime) < today();

but you couldn't use these Qlik-functions else you need to take similar ones which your data-base is supporting.