Hello,

The aim is to calculate a total price

I have a table with id_prod PURCHASE product =
ty_doc = document type
nu_doc = document number
nucdli = code of document linked
px_com = price of the order

We have :

Code:
```CREATE TABLE tbl_ligneachat (id_prod char(1), ty_doc char(3), nu_doc int , nucdli int NULL, px_com int NULL)
INSERT INTO tbl_ligneachat VALUES ('A','BE',13604,NULL,4)
INSERT INTO tbl_ligneachat VALUES ('B','BE',13604,4)
INSERT INTO tbl_ligneachat VALUES ('C','CDA',13604,NULL,3)
INSERT INTO tbl_ligneachat VALUES ('D','CDA',13604,NULL,3)
INSERT INTO tbl_ligneachat VALUES ('E','CDA',13604,NULL,2)
INSERT INTO tbl_ligneachat VALUES ('E','CDC',13661,13604,3)
INSERT INTO tbl_ligneachat VALUES ('B','CDC',13361,13604,2)
INSERT INTO tbl_ligneachat VALUES ('E','CDT',13360,13604,1)
INSERT INTO tbl_ligneachat VALUES ('E','FA',444,NULL,1)
INSERT INTO tbl_ligneachat VALUES ('E','BE','555',NULL,1)```
And :

id_prod____ty_doc___ nu_doc___ nucdli___px_com
A___________ BE ____ 13604_____________ 4
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
E_______ ___ CDA ___ 13604 ____________ 2
E ______ ___ CDC ___ 13661 ___ 13604 ___ 3
B ______ ___ CDC ___ 13361 ___ 13604 ___ 2
E ______ ___ CDT ___ 13360 ___ 13604 ___ 1

My problem is :

I want to view in the CDA, the px_com accumulated when "nucdli" = "nu_doc"
And, the ligne with the nucdli corresponding must deasappear!

Finally we must obtain :

id_prod____ty_doc___ nu_doc___ nucdli___px_com
E___________ CDA ____13604 _____________6
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
B ______ ___ CDC ___ 13361 ___ 13604 ____ 2

I get 4 as value because ty_doc is "BE". So the value is the same.

We concatenate the nu_doc values with nucdli value only when ty_doc are CDA with CDD,CDC or CDT

nucdli exist only for ty_doc CDD,CDC,CDT.

We report the sum on ligne which have a CDA

My solution is :

Code:
```SELECT
k.id_produit,
k.id_fournisseur,
k.ty_commande,
k.ty_document,
k.nu_document,
k.px_commande + coalesce(k3.px_commande,0) AS px_commande
FROM
tbl_ligneachat k
LEFT OUTER JOIN (
SELECT
k2.id_produit,
k2.id_fournisseur,
k2.qt_cmde_ini,
k2.ty_document,
k2.nu_document,
k2.px_commande,
k2.nucdli

FROM tbl_ligneachat K2) AS K3
ON  K3.nucdli IS NOT NULL AND k3.nucdli=k.nu_document
WHERE K.nucdli IS NULL
ORDER BY id_produit```

This query returns me all the lines.

However, this does not calcul lines that have a nucdli = nu_document

----> RECAP :

As a first step, the request must includes the "nucdli"
(N° orders related) and "nu_document" (N° order)which have the same identifiers (2 columns in a table with the same identifier).

In a second step, it is supposed to add the "px_commande" (the cost of orders) correspondant.

Then, I must lose only lines with the nucdli correspondant.

I hope I was clear enough ..