where & dollar sign expansion

[#1]

Dear community,

I'm having some scripting issues with trying to create an optimised qvd.

Below is my script pulling information from a MySQL database - the Where Clause isn't working - please can you help?

TempT:

Load

timestamp(max(inserted_date)) as MaxID

Resident PackBench;

Let MaxID = peek('MaxID');

Drop Table TempT;

ODBC CONNECT32 TO dbserver;

Concatenate (PackBench)

SnapPackBench:

Load  

    collectdate,

    cslsupplierref,

    easyWebref,

    `export_stamp`,

    `inserted_date`,

    `lock_stamp`,

    ownerid,

    `processed_stamp`,

    `qc_reason`,

    `qc_stamp`,

    `qc_user`,

    `snap_ref`,

    stu;

SQL SELECT collectdate,

    cslsupplierref,

    easyWebref,

    `export_stamp`,

    `inserted_date`,

    `lock_stamp`,

    ownerid,

    `processed_stamp`,

    `qc_reason`,

    `qc_stamp`,

    `qc_user`,

    `snap_ref`,

    stu

FROM snap.shipmentlog

Where `inserted_date`>$(MaxID);

[#2]

Perfect!

All i need to do now is get my dollar sign expansion to return the date with single quote marks.

I've reformatted it but i get this error: I think I'm missing those quote marks

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.3(w) Driver][mysqld-5.1.73]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16:15:23' at line 15

SQL SELECT collectdate,

    cslsupplierref,

    easyWebref,

    `export_stamp`,

    `inserted_date`,

    `lock_stamp`,

    ownerid,

    `processed_stamp`,

    `qc_reason`,

    `qc_stamp`,

    `qc_user`,

    `snap_ref`,

    stu

FROM snap.shipmentlog

Where `inserted_date`>2016-01-28 16:15:23

[sunny_talwar · Accepted solution]

Try this:

TempT:

Load Max(inserted_date) as MaxID

Resident PackBench;

Let MaxID = Date(Peek('MaxID'), 'YYYY-MM-DD hh:mm:ss');

Drop Table TempT;

ODBC CONNECT32 TO dbserver;

Concatenate (PackBench)

SnapPackBench:

Load

    collectdate,

    cslsupplierref,

    easyWebref,

    `export_stamp`,

    `inserted_date`,

    `lock_stamp`,

    ownerid,

    `processed_stamp`,

    `qc_reason`,

    `qc_stamp`,

    `qc_user`,

    `snap_ref`,

    stu;

SQL SELECT collectdate,

    cslsupplierref,

    easyWebref,

    `export_stamp`,

    `inserted_date`,

    `lock_stamp`,

    ownerid,

    `processed_stamp`,

    `qc_reason`,

    `qc_stamp`,

    `qc_user`,

    `snap_ref`,

    stu

FROM snap.shipmentlog

Where `inserted_date`> '$(MaxID)';

[santharubban]

Can you try

MaxID = Date(Floor(Datefeild));