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 ..
dBforums
>
Complex Sum
06-23-09, 03:36
Complex Sum
Linear Mode
