3 Replies Latest reply: Feb 13, 2018 5:42 AM by Mark Ramiro RSS

    Normal LOAD translated to SQL SELECT

    Mark Ramiro

      Hi, I have translated my normal load script into SQL Select as I am now connecting to ODBC. Everything works fine except when I incorporate the Today() in my WHERE, suddenly no data is getting loaded. No errors. But no data is showing up. Not sure what is wrong.

       

      ORIGINAL SCRIPT:

      LOAD  reference_id as ID,

                 Sum(costs) as TotalCosts

      FROM

      [Desktop\Sample Excel.xlsx]

      (ooxml, embedded labels, table is [Sample Excel]) WHERE code='ABC' and start_date<=Today() and end_date>=Today() Group by reference_id;

       

      REVISED SCRIPT:

      Let vToday = Today();

       

      LOAD  reference_id as ID,

                Sum(costs) as TotalCosts

      Group reference_id

      ;

      SQL SELECT

      reference_id,

      costs

      FROM

      "Table from MS Access"

      WHERE

      code='ABC' and start_date<=$(vToday) and end_date>=$(vToday);