SQL query syntax error

[furman]

I have having issues with Prestashop 1.6.0.8 and integration with Linnworks.

 

Linnworks does a query check and has flagged MarkAsProcessedOrders

 

INSERT INTO ps_order_history (id_employee, id_order, id_order_state, `date_add`)
SELECT
1 as id_employee,
o.id_order as id_order,
'4' as id_order_state,
'[{dProcessedOn}]' as `date_add`
FROM ps_orders o
INNER JOIN 
(
SELECT id_order, id_order_state as order_status
FROM ps_order_history
) os ON os.id_order = o.id_order
WHERE concat('PS-', o.id_order, '-', CONVERT(o.date_add, UNSIGNED)) =  '[{ReferenceNum}]';


--GO;--

 

Linnworks support have said that there is an extra ' near 4 and suggested:

 

SELECT
   0 as id_employee,
   o.id_order as id_order,
   '4' as id_order_state,
   '[{dProcessedOn}]' as `date_add`

But that did not work.  Am I missing something?

 

 

[bellini13]

what was the SQL error that you received when you execute the original command?  Looking at the original query, I am not seeing any additional '

 

what I do find invalid is this..

'[{dProcessedOn}]' as `date_add`

you cannot execute this command without the module replacing [{dProcessedOn}] with an actual date...

 

the same thing goes for this statement, what is  '[{ReferenceNum}]' supposed to be?

WHERE concat('PS-', o.id_order, '-', CONVERT(o.date_add, UNSIGNED)) =  '[{ReferenceNum}]';

I think you need to provide a valid SQL statement that has been parsed by the module where it has replaced those placeholder variables.