topic Re: Using DATEADD or GETDATE in SQL Query in Qlikview in QlikView

Using DATEADD or GETDATE in SQL Query in Qlikview

AyCe1082 — Sat, 16 Nov 2024 00:20:43 GMT

Hi, I am trying to do an SQL query where load.LAST_UPDATED is within 7 days of the current date. I have seen many posts where people wanted the QV version of this but I need to have the SQL version work in the statement itself during a load. I keep getting invalid identifier errors or missing right parenthesis errors.

I have tried

load.LAST_UPDATE < DateAdd(m, -7, GetDate())

load.LAST_UPDATE < GetDate()-7

and also tried putting the second half of each of those lines in extra parenthesis. Nothing seems to work in Qlikview.

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

JustinDallas — Fri, 10 Jul 2020 18:42:51 GMT

We would have to see the entire SQL LOAD statement.

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

AyCe1082 — Fri, 10 Jul 2020 18:54:29 GMT

No problem. I don't think there is anything too sensitive here.

Fixtures: SQL SELECT sched.game_code "PK_gameId", sched.league_id "PK_leagueId", sched.season_id "PK_seasonId", sched.game_date_ct "FIXTURE_gameDateTime", sched.home_team_name, sched.away_team_name, field.field_length_live, field.field_width_live, field.periods, load.LAST_UPDATE, DateAdd(m, -3, GetDate()) FROM customer_data.cd_soccer_schedule sched, customer_data.cd_soccer_games field, commercial.SOCCER_GAME_LOADED load WHERE sched.season_id IN (201949,201939) AND sched.game_code = field.game_code AND load.table_type_id = 4 AND load.GAME_CODE = sched.GAME_CODE AND load.GAME_CODE = field.GAME_CODE AND load.LAST_UPDATE < DateAdd(m, -7, GetDate()) AND load.LOADED_STATUS = 1;

The final statement will be larger and pull a lot of data. LAST_UPDATE is in the format DD/MM/YYYY hh:mm:ss AM/PM

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

rubenmarin — Sat, 11 Jul 2020 10:29:37 GMT

Hi, you can create a variable using Qlikview syntax to set the date you want to start load, and use this variable in Sql statement using $(varName), like:

SET vStartDate = Date(Today()-7); // substracts 7 days from today

SQL SELECT ...

AND load.LAST_UPDATE < '$(vStartDate)'

In your current sql statement I think you need to use 'd' instead of 'm'

DateAdd(d, -7, GetDate())

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

AyCe1082 — Sat, 11 Jul 2020 12:48:56 GMT

Hi, thanks for the suggestion.

I get anerror when I try it. It seems to be the variable isn't working during the load.

SQL##f - SqlState: S1000, ErrorCode: 1858, ErrorMsg: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected Fixtures: SQL SELECT sched.game_code "PK_gameId", sched.league_id "PK_leagueId", sched.season_id "PK_seasonId", sched.game_date_ct "FIXTURE_gameDateTime", sched.home_team_name, sched.away_team_name, field.field_length_live, field.field_width_live, field.periods FROM customer_data.cd_soccer_schedule sched, customer_data.cd_soccer_games field, commercial.SOCCER_GAME_LOADED load WHERE sched.season_id IN (201949,201939) and sched.game_code = field.game_code and load.table_type_id = 4 AND load.GAME_CODE = sched.GAME_CODE AND load.GAME_CODE = field.GAME_CODE AND load.LAST_UPDATE < 'Date(Today()-7)' AND load.LOADED_STATUS = 1

I also tried changing SET to LET and get a different error:

SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month

Fixtures:
SQL SELECT
sched.game_code "PK_gameId",
sched.league_id "PK_leagueId",
sched.season_id "PK_seasonId",
sched.game_date_ct "FIXTURE_gameDateTime",
sched.home_team_name,
sched.away_team_name,
field.field_length_live,
field.field_width_live,
field.periods

FROM customer_data.cd_soccer_schedule sched,
customer_data.cd_soccer_games field,
commercial.SOCCER_GAME_LOADED load

WHERE sched.season_id IN (201949,201939)
and sched.game_code = field.game_code
and load.table_type_id = 4
AND load.GAME_CODE = sched.GAME_CODE
AND load.GAME_CODE = field.GAME_CODE
AND load.LAST_UPDATE < '04/07/2020 12:00:00 AM'
AND load.LOADED_STATUS = 1

I also get the not a valid month error with

AND TO_DATE( load.LAST_UPDATE, 'DD/MM/YYYY') < '$(vStartDate)'

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

rubenmarin — Sat, 11 Jul 2020 15:27:57 GMT

Hi, yes sorry, it should be with LET.

Have you tried?:

AND load.LAST_UPDATE < TO_DATE($(vStartDate),'DD/MM/YYYY')

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

AyCe1082 — Sat, 11 Jul 2020 15:55:19 GMT

Unfortunately it just gives this error:

SQL##f - SqlState: S1000, ErrorCode: 1858, ErrorMsg: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected

Fixtures:
SQL SELECT
sched.game_code "PK_gameId",
sched.league_id "PK_leagueId",
sched.season_id "PK_seasonId",
sched.game_date_ct "FIXTURE_gameDateTime",
sched.home_team_name,
sched.away_team_name,
field.field_length_live,
field.field_width_live,
field.periods

FROM customer_data.cd_soccer_schedule sched,
customer_data.cd_soccer_games field,
commercial.SOCCER_GAME_LOADED load

WHERE sched.season_id IN (201949,201939)
and sched.game_code = field.game_code
and load.table_type_id = 4
AND load.GAME_CODE = sched.GAME_CODE
AND load.GAME_CODE = field.GAME_CODE
AND load.LAST_UPDATE < TO_DATE(04/07/2020,'DD/MM/YYYY')
AND load.LOADED_STATUS = 1

Re: Using DATEADD or GETDATE in SQL Query in Qlikview

AyCe1082 — Sat, 11 Jul 2020 16:58:38 GMT

This is what worked in the end:

LET vStartDate = Date(Today()-3,'DD-MMM-YYYY'); // substracts 7 days from today

while using TRUNC() in the where clause in the SQL load.

Apparently oracle did not like the date format and would only work if I changed it to the format 04-Jul-2020.