Hello,

Thank you for your attention.

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 ..