Erreur lorsque j'ai ajouter max et sum dans le script ci dessous:
VL:
LOAD
CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],
INVENTORY_ITEM_ID as [VenteLigne. Article ID],
DESCRIPTION as [VenteLigne. Description Article],
if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],
(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],
UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],
max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],
UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],
if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],
INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],
,
EXTENDED_AMOUNT as [VenteLigne. Montant],
ORG_ID as [VenteLigne. SociétéID]
FROM
[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]
(qvd)
Where
ORG_ID='155'
//or ORG_ID='158'
and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')
and LINE_TYPE='LINE'
and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')
Hi Brahim, if you're using aggregation opetaions like Sum or Max you need to add to the 'group by' clause every field that is not aggregated (ORG_ID, EXTENDED_AMOUNT...)
UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],
UNIT_STANDARD_PRICE isn't in a aggregation function, can you put this field in the group by to check if this is the origin of the problem? Knowing that we can isolate the problem and work in the solution.
Also, you sure of this line?: max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)...
The Max value of UNIT_STANDARD_PRICE substracted by the sum of every value in UNIT_SELLING_PRICE?
if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],
(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],
UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],
max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],
UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],
if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],
INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],
EXTENDED_AMOUNT as [VenteLigne. Montant],
ORG_ID as [VenteLigne. SociétéID]
FROM
[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]
(qvd)
Where
ORG_ID='155'
//or ORG_ID='158'
and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')
and LINE_TYPE='LINE'
and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')
UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut]
Hi Brahim, in group by should be only field names, not expressions:
LOAD .....
FROM
[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]
(qvd)
Where
ORG_ID='155'
//or ORG_ID='158'
and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')
and LINE_TYPE='LINE'
and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')
group by CUSTOMER_TRX_ID,
INVENTORY_ITEM_ID,
DESCRIPTION,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
ORG_ID,
EXTENDED_AMOUNT,
QUANTITY_CREDITED,
QUANTITY_INVOICED,
UNIT_STANDARD_PRICE,
UNIT_SELLING_PRICE;
Again, as you're using aggregated and not aggregated UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE this don't make sense, but at least you can know the cause of the error.
