1 Reply Latest reply: Aug 29, 2012 3:34 AM by sbruckert RSS

    Date interpretation in incremental load

      Hi everybody.

       

      I had an issue with my script (which is only a proof of concept in order to implement it i a larger qvw afterward).

       

      I've tested it but it only load the entries within the QVD file and not from teh ODBC data.

       

      I assume that the problem is coming from the WHERE close of the select query.

       

      When you look at the Sage forum about their ODBC driver (...) many post talk about a specific format which is : {d YYYY-MM-DD}

       

      In another script I have a where close which is as foloowing : Date > 2012-01-01

       

      and it works

       

      So I've tried different combination for my incremental script and when you look at the reference book, they give a #$(variable)# as example assuming its a number comparison. But with the format given previously I think their should be a problem where the sql query should do a date comparison.

       

      Here is my script whether one of you as an idea on how to handle my issue.

       

      SET ThousandSep=' ';

      SET DecimalSep=',';

      SET MoneyThousandSep=' ';

      SET MoneyDecimalSep=',';

      SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

      SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

       

       

      ODBC CONNECT TO [SageBijouxLocal;DBQ=C:\Users\Public\Documents\Sage\Gestion commerciale\Gescom Bijou.gcm] (XUserId is cfMGLeEIELbSGXFNTDdEXQROFLdEHfAF);

       

       

      Let MomentDernExéc = '{d ' & Date(ReloadTime(),'YYYY-MM-DD') &'}';

      Let ExecTime = '{d ' & Date(Now(),'YYYY-MM-DD') &'}';

       

       

      Articles:

       

       

      SQL SELECT * FROM "F_ARTICLE"

       

       

      WHERE AR_DATEMODIF >= $(MomentDernExéc) AND AR_DATEMODIF < $(ExecTime);

       

       

      IF Not IsNull(QvdCreateTime('Bijoux_Articles.qvd')) THEN

       

      Concatenate (Articles)

       

       

      LOAD * FROM Bijoux_Articles.QVD (qvd) WHERE NOT Exists(AR_REF);

       

       

      END IF

       

       

      IF NoOfRows(Articles) > 0 THEN

       

       

      Let vMessage = NoOfRows(Articles) & ' nouvel article';

      MsgBox( $(vMessage), 'msgbox', 'OK', 'ICONASTERISK');

       

       

       

       

      STORE Articles INTO Bijoux_Articles.QVD (qvd);

       

       

      END IF