Erreur fonction Max dans le script

[#1]

   max(UNIT_STANDARD_PRICE)-UNIT_SELLING_PRICE

[Peter_Cammaert]

Cette expression se trouve dans un LOAD avec GROUP BY?

[maxgro]

for max and other aggregation in the script you need a group by for every not aggregated field (bold)

Tmp:

Load

Dim1,

Dim2,

Dim3,

max(Expression1)-5 as exp,

min(Expression2) as exp2

Resident

Transactions

group by

Dim1,

  Dim2,

  Dim3;

[#4]

meme pb:

VL:

LOAD //CUSTOMER_TRX_LINE_ID,

//     LAST_UPDATE_DATE,

//     LAST_UPDATED_BY,

//     CREATION_DATE,

//     CREATED_BY,

//     LAST_UPDATE_LOGIN,

     CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],

//     LINE_NUMBER,

//     SET_OF_BOOKS_ID,

//     REASON_CODE,

     INVENTORY_ITEM_ID as [VenteLigne. Article ID],

     DESCRIPTION as [VenteLigne. Description Article],

//     PREVIOUS_CUSTOMER_TRX_ID,

//     PREVIOUS_CUSTOMER_TRX_LINE_ID,

//     QUANTITY_ORDERED,

//     QUANTITY_CREDITED,

//     QUANTITY_INVOICED,

     if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],

    max(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],

     UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],

(UNIT_STANDARD_PRICE)-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],

//   SALES_ORDER,

//     SALES_ORDER_REVISION,

//     SALES_ORDER_LINE,

//     SALES_ORDER_DATE,

//     ACCOUNTING_RULE_ID,

//     ACCOUNTING_RULE_DURATION,

//     LINE_TYPE,

//     ATTRIBUTE_CATEGORY,

//     ATTRIBUTE1,

//     ATTRIBUTE2,

//     ATTRIBUTE3,

//     ATTRIBUTE4,

//     ATTRIBUTE5,

//     ATTRIBUTE6,

//     ATTRIBUTE7,

//     ATTRIBUTE8,

//     ATTRIBUTE9,

//     ATTRIBUTE10,

//     REQUEST_ID,

//     PROGRAM_APPLICATION_ID,

//     PROGRAM_ID,

//     PROGRAM_UPDATE_DATE,

//     RULE_START_DATE,

//     INITIAL_CUSTOMER_TRX_LINE_ID,

     if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],

     INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],

//     INTERFACE_LINE_ATTRIBUTE2,

//     INTERFACE_LINE_ATTRIBUTE3,

//     INTERFACE_LINE_ATTRIBUTE4,

//     INTERFACE_LINE_ATTRIBUTE5,

//     INTERFACE_LINE_ATTRIBUTE6,

//     INTERFACE_LINE_ATTRIBUTE7,

//     INTERFACE_LINE_ATTRIBUTE8,

//     SALES_ORDER_SOURCE,

//     TAXABLE_FLAG,

     EXTENDED_AMOUNT as [VenteLigne. Montant],

//     REVENUE_AMOUNT,

//     AUTORULE_COMPLETE_FLAG,

//     LINK_TO_CUST_TRX_LINE_ID,

//     ATTRIBUTE11,

//     ATTRIBUTE12,

//     ATTRIBUTE13,

//     ATTRIBUTE14,

//     ATTRIBUTE15,

//     TAX_PRECEDENCE,

//     TAX_RATE,

//     ITEM_EXCEPTION_RATE_ID,

//     TAX_EXEMPTION_ID,

//     MEMO_LINE_ID,

//     AUTORULE_DURATION_PROCESSED,

//     UOM_CODE,

//     DEFAULT_USSGL_TRANSACTION_CODE,

//     DEFAULT_USSGL_TRX_CODE_CONTEXT,

//     INTERFACE_LINE_ATTRIBUTE10,

//     INTERFACE_LINE_ATTRIBUTE11,

//     INTERFACE_LINE_ATTRIBUTE12,

//     INTERFACE_LINE_ATTRIBUTE13,

//     INTERFACE_LINE_ATTRIBUTE14,

//     INTERFACE_LINE_ATTRIBUTE15,

//     INTERFACE_LINE_ATTRIBUTE9,

//     VAT_TAX_ID,

//     AUTOTAX,

//     LAST_PERIOD_TO_CREDIT,

//     ITEM_CONTEXT,

//   TAX_EXEMPT_FLAG  as [VenteLigne. Exoneration],

//   TAX_EXEMPT_NUMBER as [VenteLigne. Num_Exoneration],

//     TAX_EXEMPT_REASON_CODE,

//     TAX_VENDOR_RETURN_CODE,

//     SALES_TAX_ID,

//     LOCATION_SEGMENT_ID,

//     MOVEMENT_ID,

     ORG_ID as [VenteLigne. SociétéID]

//     WH_UPDATE_DATE,

//     GLOBAL_ATTRIBUTE1,

//     GLOBAL_ATTRIBUTE2,

//     GLOBAL_ATTRIBUTE3,

//     GLOBAL_ATTRIBUTE4,

//     GLOBAL_ATTRIBUTE5,

//     GLOBAL_ATTRIBUTE6,

//     GLOBAL_ATTRIBUTE7,

//     GLOBAL_ATTRIBUTE8,

//     GLOBAL_ATTRIBUTE9,

//     GLOBAL_ATTRIBUTE10,

//     GLOBAL_ATTRIBUTE11,

//     GLOBAL_ATTRIBUTE12,

//     GLOBAL_ATTRIBUTE13,

//     GLOBAL_ATTRIBUTE14,

//     GLOBAL_ATTRIBUTE15,

//     GLOBAL_ATTRIBUTE16,

//     GLOBAL_ATTRIBUTE17,

//     GLOBAL_ATTRIBUTE18,

//     GLOBAL_ATTRIBUTE19,

//     GLOBAL_ATTRIBUTE20,

//     GLOBAL_ATTRIBUTE_CATEGORY,

//     GROSS_UNIT_SELLING_PRICE,

//     GROSS_EXTENDED_AMOUNT,

//     AMOUNT_INCLUDES_TAX_FLAG,

//     TAXABLE_AMOUNT,

//     WAREHOUSE_ID,

//     TRANSLATED_DESCRIPTION,

//     EXTENDED_ACCTD_AMOUNT,

//     BR_REF_CUSTOMER_TRX_ID,

//     BR_REF_PAYMENT_SCHEDULE_ID,

//     BR_ADJUSTMENT_ID,

//     MRC_EXTENDED_ACCTD_AMOUNT,

//     PAYMENT_SET_ID,

//     CONTRACT_LINE_ID,

//     SOURCE_DATA_KEY1,

//     SOURCE_DATA_KEY2,

//     SOURCE_DATA_KEY3,

//     SOURCE_DATA_KEY4,

//     SOURCE_DATA_KEY5,

//     INVOICED_LINE_ACCTG_LEVEL,

//     OVERRIDE_AUTO_ACCOUNTING_FLAG,

//     SHIP_TO_CUSTOMER_ID,

//     SHIP_TO_ADDRESS_ID,

//     SHIP_TO_SITE_USE_ID,

//     SHIP_TO_CONTACT_ID,

//     HISTORICAL_FLAG,

//     TAX_LINE_ID,

//     LINE_RECOVERABLE,

//     TAX_RECOVERABLE,

//     TAX_CLASSIFICATION_CODE,

//     AMOUNT_DUE_REMAINING,

//     ACCTD_AMOUNT_DUE_REMAINING,

//     AMOUNT_DUE_ORIGINAL,

//     ACCTD_AMOUNT_DUE_ORIGINAL,

//     CHRG_AMOUNT_REMAINING,

//     CHRG_ACCTD_AMOUNT_REMAINING,

//     FRT_ADJ_REMAINING,

//     FRT_ADJ_ACCTD_REMAINING,

//     FRT_ED_AMOUNT,

//     FRT_ED_ACCTD_AMOUNT,

//     FRT_UNED_AMOUNT,

//     FRT_UNED_ACCTD_AMOUNT,

//     DEFERRAL_EXCLUSION_FLAG,

//     RULE_END_DATE,

//     PAYMENT_TRXN_EXTENSION_ID,

//     INTEREST_LINE_ID

FROM

[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]

(qvd)

GWhere

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')

;

[Peter_Cammaert]

Max() n'est pas acceptée sans GROUP BY. Ajoutez une clause GROUP BY a la fin du LOAD.

[#6]

j'ai ajouter un group by est c'est la même problème.

[Peter_Cammaert]

Le script en haut ne contient pas de GROUP BY. Veuillez partager le dernier script svp.

[#8]

VL:

LOAD //CUSTOMER_TRX_LINE_ID,

//     LAST_UPDATE_DATE,

//     LAST_UPDATED_BY,

//     CREATION_DATE,

//     CREATED_BY,

//     LAST_UPDATE_LOGIN,

     CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],

//     LINE_NUMBER,

//     SET_OF_BOOKS_ID,

//     REASON_CODE,

     INVENTORY_ITEM_ID as [VenteLigne. Article ID],

     DESCRIPTION as [VenteLigne. Description Article],

//     PREVIOUS_CUSTOMER_TRX_ID,

//     PREVIOUS_CUSTOMER_TRX_LINE_ID,

//     QUANTITY_ORDERED,

//     QUANTITY_CREDITED,

//     QUANTITY_INVOICED,

     if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],

  max(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],

     UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],

(UNIT_STANDARD_PRICE)-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],

//   SALES_ORDER,

//     SALES_ORDER_REVISION,

//     SALES_ORDER_LINE,

//     SALES_ORDER_DATE,

//     ACCOUNTING_RULE_ID,

//     ACCOUNTING_RULE_DURATION,

//     LINE_TYPE,

//     ATTRIBUTE_CATEGORY,

//     ATTRIBUTE1,

//     ATTRIBUTE2,

//     ATTRIBUTE3,

//     ATTRIBUTE4,

//     ATTRIBUTE5,

//     ATTRIBUTE6,

//     ATTRIBUTE7,

//     ATTRIBUTE8,

//     ATTRIBUTE9,

//     ATTRIBUTE10,

//     REQUEST_ID,

//     PROGRAM_APPLICATION_ID,

//     PROGRAM_ID,

//     PROGRAM_UPDATE_DATE,

//     RULE_START_DATE,

//     INITIAL_CUSTOMER_TRX_LINE_ID,

     if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],

     INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],

//     INTERFACE_LINE_ATTRIBUTE2,

//     INTERFACE_LINE_ATTRIBUTE3,

//     INTERFACE_LINE_ATTRIBUTE4,

//     INTERFACE_LINE_ATTRIBUTE5,

//     INTERFACE_LINE_ATTRIBUTE6,

//     INTERFACE_LINE_ATTRIBUTE7,

//     INTERFACE_LINE_ATTRIBUTE8,

//     SALES_ORDER_SOURCE,

//     TAXABLE_FLAG,

     EXTENDED_AMOUNT as [VenteLigne. Montant],

//     REVENUE_AMOUNT,

//     AUTORULE_COMPLETE_FLAG,

//     LINK_TO_CUST_TRX_LINE_ID,

//     ATTRIBUTE11,

//     ATTRIBUTE12,

//     ATTRIBUTE13,

//     ATTRIBUTE14,

//     ATTRIBUTE15,

//     TAX_PRECEDENCE,

//     TAX_RATE,

//     ITEM_EXCEPTION_RATE_ID,

//     TAX_EXEMPTION_ID,

//     MEMO_LINE_ID,

//     AUTORULE_DURATION_PROCESSED,

//     UOM_CODE,

//     DEFAULT_USSGL_TRANSACTION_CODE,

//     DEFAULT_USSGL_TRX_CODE_CONTEXT,

//     INTERFACE_LINE_ATTRIBUTE10,

//     INTERFACE_LINE_ATTRIBUTE11,

//     INTERFACE_LINE_ATTRIBUTE12,

//     INTERFACE_LINE_ATTRIBUTE13,

//     INTERFACE_LINE_ATTRIBUTE14,

//     INTERFACE_LINE_ATTRIBUTE15,

//     INTERFACE_LINE_ATTRIBUTE9,

//     VAT_TAX_ID,

//     AUTOTAX,

//     LAST_PERIOD_TO_CREDIT,

//     ITEM_CONTEXT,

//   TAX_EXEMPT_FLAG  as [VenteLigne. Exoneration],

//   TAX_EXEMPT_NUMBER as [VenteLigne. Num_Exoneration],

//     TAX_EXEMPT_REASON_CODE,

//     TAX_VENDOR_RETURN_CODE,

//     SALES_TAX_ID,

//     LOCATION_SEGMENT_ID,

//     MOVEMENT_ID,

     ORG_ID as [VenteLigne. SociétéID]

//     WH_UPDATE_DATE,

//     GLOBAL_ATTRIBUTE1,

//     GLOBAL_ATTRIBUTE2,

//     GLOBAL_ATTRIBUTE3,

//     GLOBAL_ATTRIBUTE4,

//     GLOBAL_ATTRIBUTE5,

//     GLOBAL_ATTRIBUTE6,

//     GLOBAL_ATTRIBUTE7,

//     GLOBAL_ATTRIBUTE8,

//     GLOBAL_ATTRIBUTE9,

//     GLOBAL_ATTRIBUTE10,

//     GLOBAL_ATTRIBUTE11,

//     GLOBAL_ATTRIBUTE12,

//     GLOBAL_ATTRIBUTE13,

//     GLOBAL_ATTRIBUTE14,

//     GLOBAL_ATTRIBUTE15,

//     GLOBAL_ATTRIBUTE16,

//     GLOBAL_ATTRIBUTE17,

//     GLOBAL_ATTRIBUTE18,

//     GLOBAL_ATTRIBUTE19,

//     GLOBAL_ATTRIBUTE20,

//     GLOBAL_ATTRIBUTE_CATEGORY,

//     GROSS_UNIT_SELLING_PRICE,

//     GROSS_EXTENDED_AMOUNT,

//     AMOUNT_INCLUDES_TAX_FLAG,

//     TAXABLE_AMOUNT,

//     WAREHOUSE_ID,

//     TRANSLATED_DESCRIPTION,

//     EXTENDED_ACCTD_AMOUNT,

//     BR_REF_CUSTOMER_TRX_ID,

//     BR_REF_PAYMENT_SCHEDULE_ID,

//     BR_ADJUSTMENT_ID,

//     MRC_EXTENDED_ACCTD_AMOUNT,

//     PAYMENT_SET_ID,

//     CONTRACT_LINE_ID,

//     SOURCE_DATA_KEY1,

//     SOURCE_DATA_KEY2,

//     SOURCE_DATA_KEY3,

//     SOURCE_DATA_KEY4,

//     SOURCE_DATA_KEY5,

//     INVOICED_LINE_ACCTG_LEVEL,

//     OVERRIDE_AUTO_ACCOUNTING_FLAG,

//     SHIP_TO_CUSTOMER_ID,

//     SHIP_TO_ADDRESS_ID,

//     SHIP_TO_SITE_USE_ID,

//     SHIP_TO_CONTACT_ID,

//     HISTORICAL_FLAG,

//     TAX_LINE_ID,

//     LINE_RECOVERABLE,

//     TAX_RECOVERABLE,

//     TAX_CLASSIFICATION_CODE,

//     AMOUNT_DUE_REMAINING,

//     ACCTD_AMOUNT_DUE_REMAINING,

//     AMOUNT_DUE_ORIGINAL,

//     ACCTD_AMOUNT_DUE_ORIGINAL,

//     CHRG_AMOUNT_REMAINING,

//     CHRG_ACCTD_AMOUNT_REMAINING,

//     FRT_ADJ_REMAINING,

//     FRT_ADJ_ACCTD_REMAINING,

//     FRT_ED_AMOUNT,

//     FRT_ED_ACCTD_AMOUNT,

//     FRT_UNED_AMOUNT,

//     FRT_UNED_ACCTD_AMOUNT,

//     DEFERRAL_EXCLUSION_FLAG,

//     RULE_END_DATE,

//     PAYMENT_TRXN_EXTENSION_ID,

//     INTEREST_LINE_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')

group by  CUSTOMER_TRX_ID

;

[Peter_Cammaert · Accepted solution]

Il-y-a trop the champs qui n'apparaissent pas dans le GROUP BY. Mettez tous dans le GROUP BY, ou ajoutez une fonction d'agrégation dans chaque expression qui crée une nouvelle colonne.

Pour plus de détails, consultez le manuel Ch 24.1 Instructions de script et mots-clés - LOAD.

[maxgro]

start simple

does it works?

if yes add other aggregation min, max, ....

and/or other non aggregated fields (like customer, in the group by also)

if not, post the error; we don't have the qvd, we can just try to guess as your script work

VL:

LOAD

    CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],

    INVENTORY_ITEM_ID as [VenteLigne. Article ID],

    DESCRIPTION as [VenteLigne. Description Article],    

     max(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique]

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;

;