Reefounet
le 9 juin
à 21:28
|
Re : Erreur création article sous PostgreSQL
Pour répondre à l’erreur du script, il y a un conflit :
SELECT 0 AS Footer, ps.id, wh.id AS WarehouseId, wh.name AS WarehouseName, ps.qty_checked, ps.date_checked,
ps.qty_quoted, ps.qty_ordered, ps.qty_delivered, qty_checked-COALESCE(ps.qty_delivered,0) AS qty_remaining,
ps.updatedate FROM Warehouses wh LEFT JOIN ProductsStocks ps ON ps.warehouseid=wh.id AND ps.ProductId = $1
JOIN Products psp ON psp.Id = ps.productid
UNION SELECT 1,
pst.id, --CONFLIT
NULL AS WarehouseId,
'*' AS WarehouseName,
SUM(pst.qty_checked) AS qty_checked, --CONFLIT
NULL AS date_checked,
SUM(COALESCE(pst.qty_quoted,0)) AS qty_quoted, --CONFLIT
SUM(COALESCE(pst.qty_ordered,0)) AS qty_ordered, --CONFLIT
SUM(COALESCE(pst.qty_delivered,0)) AS qty_delivered, --CONFLIT
SUM(pst.qty_checked)-SUM(COALESCE(pst.qty_delivered,0)) AS qty_remaining, --CONFLIT
MAX(pst.updatedate) AS updatedate --CONFLIT
FROM ProductsStocks pst WHERE pst.productid = $1
ORDER BY 1,4
J’ai cherché le conflit mais je n’ai pas trouvé. Si on supprime pst.id, ça passe.
Si on supprime le premier SUM, c’est le second qui est en conflit, etc !
Le seul moyen que j’ai trouvé pour contourner cela est de faire : select sum(qty_checked) as qty_checked from ProductsStocks WHERE pst.productid = $1
le code devient alors
SELECT 0 AS Footer, ps.id, wh.id AS WarehouseId, wh.name AS WarehouseName, ps.qty_checked, ps.date_checked,
ps.qty_quoted, ps.qty_ordered, ps.qty_delivered, qty_checked-COALESCE(ps.qty_delivered,0) AS qty_remaining,
ps.updatedate FROM Warehouses wh LEFT JOIN ProductsStocks ps ON ps.warehouseid=wh.id AND ps.ProductId = $1
JOIN Products psp ON psp.Id = ps.productid
UNION SELECT 1,
pst.id,
NULL AS WarehouseId,
'*' AS WarehouseName,
(SELECT SUM(qty_checked) FROM ProductsStocks WHERE productid = $1) AS qty_checked,
NULL AS date_checked,
(SELECT SUM(COALESCE(qty_quoted,0)) FROM ProductsStocks WHERE productid = $1) AS qty_quoted,
(SELECT SUM(COALESCE(qty_ordered,0)) FROM ProductsStocks WHERE productid = $1) AS qty_ordered,
(SELECT SUM(COALESCE(qty_delivered,0)) FROM ProductsStocks WHERE productid = $1) AS qty_delivered,
(SELECT SUM(qty_checked)-SUM(COALESCE(qty_delivered,0)) FROM ProductsStocks WHERE productid = $1) AS qty_remaining,
(SELECT MAX(updatedate) FROM ProductsStocks WHERE productid = $1) AS updatedate
FROM ProductsStocks pst WHERE pst.productid = $1
ORDER BY 1,4
Sous PgAdmin, c’est OK ! |
