Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
in my script the LOAD-Command filters with a Where-Statement for a calender week. But is there a way to move the Where-Statement into the SQL-Statement? My goal is to shorten loading time, because the Where-Statement in the in the LOAD-Command takes a lot of time.
LOAD Distinct WEEK(TIME_NEW) AS WEEK_LOADWHERE WEEK(TIME_NEW) = 14//Remove this to the SQL-Statement ; SQL SELECT TIME_NEW FROM IN.TEST WHERE WEEK(TIME_NEW) = 14 //But unfortunately that does not work ;
Thanks and see you soon
David
Unfortunately not, but you gave me an idea! Because we work with oracle and this works great:
TO_CHAR(TO_DATE(TIME_NEU, 'dd.mm.yyyy'), 'WW') = 14
Thanks for the Inspiration!
If your db is sql server, you can try using datepart(), like:
SQL SELECT TIME_NEW FROM IN.TEST WHERE DatePart(week, TIME_NEW) = 14
May be you can load all rows first and in field you use week along with num (just to make sure week is giving numeric value) functions to get numeric week value from the time field. Then use this intermediate table to load final table where you are only loading rows with a particular value. I think that may help in performance.
intermediatetable:
LOAD Distinct
WEEK(TIME_NEW) AS WEEK_LOAD;
finaltable:
LOAD //distinct may not be needed here as distinct in intermediate table would take care of it
//or you can remove from there and use it here as per your need
WEEK_LOAD
resident intermediatetable
where WEEK_LOAD = 14;
drop table intermediatetable;
I hope this helps. You may also want to check/seek online SQL help.
Unfortunately not, but you gave me an idea! Because we work with oracle and this works great:
TO_CHAR(TO_DATE(TIME_NEU, 'dd.mm.yyyy'), 'WW') = 14
Thanks for the Inspiration!