0

I'm trying to pull a credit memo from the directly from the database but i keep getting the below error

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM sales_flat_creditmemo as o LEFT JOIN sales_flat_creditmemo_grid AS i ON i.`' at line 1

Here is my query

SELECT 
    o.`increment_id` as `Credit Memo #`, 
    CONCAT(YEAR(o.`created_at`), '/', LEFT(MONTHNAME(o.`created_at`),3), '/', DAY(o.`created_at`), ' ', TIME(o.`created_at`)) AS 'Created At',
    i.`order_increment_id` as `Order number`,
    CONCAT(YEAR(i.`order_created_at`), '/', LEFT(MONTHNAME(i.`order_created_at`),3), '/', DAY(i.`order_created_at`), ' ', TIME(i.`order_created_at`)) AS 'Order Date',
    i.`billing_name` AS `Bill to Name`,
    j.`sku` AS `SKU`,
    j.`name` as `Description`,
    j.`qty` AS `QTY`,
    j.`price_incl_tax` AS `Item Value(incl)`,
    o.`creditmemo_status` AS `Status`,
FROM sales_flat_creditmemo as o
LEFT JOIN sales_flat_creditmemo_grid AS i ON i.`entity_id` = o.`entity_id`
LEFT JOIN sales_flat_creditmemo_item AS j ON j.`entity_id` = o.`entity_id`
WHERE date(o.`created_at`) >= ADDDATE(LAST_DAY(ADDDATE(now(), INTERVAL -2 MONTH)), INTERVAL 1 DAY)
AND date(o.`created_at`) <= LAST_DAY(ADDDATE(now(), INTERVAL -1 MONTH))
AND date(i.`order_created_at`) >= ADDDATE(LAST_DAY(ADDDATE(now(), INTERVAL -2 MONTH)), INTERVAL 1 DAY)
AND date(i.`order_created_at`) <= LAST_DAY(ADDDATE(now(), INTERVAL -1 MONTH))
AND i.`parent_item_id` IS NULL AND state IN ("complete", "processing", "canceled" , "pending_payment")
ORDER BY o.`created_at` DESC

Can someone please help where i'm wrong?

PS: using magento 1.9

1

remove ',' before FROM statement

1

You had some syntax errors :-) But also a logic error: sales_flat_creditmemo_grid doesn't have a parent_item_id.

Here is your corrected statement (anyway you have to now, if the columns and conditions are what you need - I'm not sure that everything makes sense in that statement):

SELECT o.increment_id as 'Credit Memo #', 
CONCAT(YEAR(o.created_at), '/', LEFT(MONTHNAME(o.created_at),3), '/', DAY(o.created_at), ' ', TIME(o.created_at)) AS 'Created At',
i.order_increment_id as 'Order number', 
CONCAT(YEAR(i.order_created_at), '/', LEFT(MONTHNAME(i.order_created_at),3), '/', DAY(i.order_created_at), ' ', TIME(i.order_created_at)) AS 'Order Date', 
i.billing_name AS 'Bill to Name', 
j.sku AS SKU, j.name as Description, 
j.qty AS QTY, 
j.price_incl_tax AS 'Item Value(incl)', 
o.creditmemo_status AS Status 
FROM sales_flat_creditmemo as o 
LEFT JOIN sales_flat_creditmemo_grid AS i ON i.entity_id = o.entity_id 
LEFT JOIN sales_flat_creditmemo_item AS j ON j.entity_id = o.entity_id 
WHERE date( o.created_at) >= ADDDATE(LAST_DAY(ADDDATE(now(), INTERVAL -2 MONTH)), INTERVAL 1 DAY) 
AND date(o.created_at) <= LAST_DAY(ADDDATE(now(), INTERVAL -1 MONTH)) 
AND date(i.order_created_at) >= ADDDATE(LAST_DAY(ADDDATE(now(), INTERVAL -2 MONTH)), INTERVAL 1 DAY) 
AND date(i.order_created_at) <= LAST_DAY(ADDDATE(now(), INTERVAL -1 MONTH)) 
AND o.state IN ("complete", "processing", "canceled" , "pending_payment") 
ORDER BY o.created_at DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.