SUM raises general script error

[#1]

Hi all,

I am trying to execute a load statement that´s concatenate a lot of tables then sum it´s columns in another one. Such like this

LET PED_STATUS_ABERTO_TOTAL = 1;

LET PED_STATUS_ABERTO_PARCIAL = 2;

LET PED_STATUS_LIQUIDADO = 4;

LET PED_STATUS_CANCELADO = 5;

PassiveisDeCancelamento:

NoConcatenate

LOAD *,

    0 AS CAN_QTDCAN,

    0 AS CAN_VLRCAN,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) as PAS_QTDPAS,

  if(PED_INTEGR = 'Integrado', (PED_QTDPED/PED_VLRCNV) * (PED_PREUNI*PED_VLRCNV), PED_QTDPED * PED_PREUNI)  as PAS_VLRPAS

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_ABERTO_PARCIAL)

  AND PED_QTDCAN > 0;

TotalmenteCancelados:

NoConcatenate

LOAD *,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) as CAN_QTDCAN,

  (if(PED_INTEGR = 'Integrado', (PED_QTDPED/PED_VLRCNV)*(PED_PREUNI*PED_VLRCNV), PED_QTDPED*PED_VLRCNV)) as CAN_VLRCAN,

  0 AS PAS_QTDPAS,

  0 AS PAS_VLRPAS

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_CANCELADO);

SaldoFaturadoVsPedido:

NoConcatenate

LOAD *,

    if(PED_INTEGR = 'Integrado', PED_QTDFAT/PED_VLRCNV, PED_QTDPED) AS FAT_QTDFAT,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) AS FAT_QTDPED,

  if(PED_INTEGR = 'Integrado', PED_PREUNI*PED_VLRCNV, PED_PREUNI) AS FAT_VLRUNI

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_LIQUIDADO);

CancelamentoDeLiquidados:

NoConcatenate

LOAD *,

  if(FAT_QTDPED-FAT_QTDFAT > 0, FAT_QTDPED-FAT_QTDFAT, 0) AS CAN_QTDCAN,

  if(FAT_QTDPED-FAT_QTDFAT > 0, FAT_QTDPED*FAT_VLRUNI, 0) AS CAN_VLRCAN,

  0 AS PAS_QTDPAS,

  0 AS PAS_VLRPAS

RESIDENT SaldoFaturadoVsPedido;

TCancelamentos:

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

RESIDENT CancelamentoDeLiquidados;

CONCATENATE

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

Resident TotalmenteCancelados;

CONCATENATE

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

RESIDENT PassiveisDeCancelamento;

//Debugging statement

call inspect ('TCancelamentos')

Cancelamentos:

LOAD

  CAN_PEDFIL,

  CAN_PEDCOD,

  CAN_PEDSEQ,

  CAN_PROCOD,

  SUM(CAN_QTDCAN) AS CAN_QTDCAN,

  SUM(CAN_VLRCAN) AS CAN_VLRCAN,

  SUM(PAS_QTDPAS) AS PAS_QTDPAS,

  SUM(PAS_VLRPAS) AS PAS_VLRPAS

RESIDENT TCancelamentos

GROUP BY

  CAN_PEDFIL,

  CAN_PEDCOD,

  CAN_PEDSEQ,

  CAN_PROCOD;

//Debugging statement

call inspect('Cancelamentos')

The debugging statement is a function to inspect data at resident tables... the fact is with SUM expressions at 'Cancelamentos' resident table the script raises an error. If I let only simple columns without SUM expression the script loads fine..

Does someone have one idea that what could I doing wrong?

Thanks in advance

[#2]

Yeap.

I really do not understand why some columns SUM properly and another one raises error, but setting the noConcatenate command all SUM expressions works correctly

[Anonymous]

It happens when we are using aggreate functions with the resident load and not using noconcatenate.

Therefore always try to use it, or just store the previous table in qvd and then drop it.

Now when you will load the final table from qvd, you can easily apply any aggreate functions.

I also had this problem previously, thats why I suggested you to use it.

Any way, it gud to get resolved the issue,

Have a nice day and Happy new year in advance.

[#4]

Thanks Nitin...

I wish a happy new year too