SUM within Loadscript = Invalid Expression

[Daryn]

Hello to all and thanks for taking the time to read/assist with this issue.

I have a sum that gives the right result in a table.

sum (([VGW03] / [BMSCH]) * [GMNGA]) /60

I want to create that value as a field I can use, so assumed I could do this in the load script.

When I try I get an 'invalid expression' load script error.

My load script is (have removed the full SQL address);

--------------------------------------------------------------------------------------------------------------

LIB CONNECT TO 'Microsoft_SQL_Server(uk_daryn.bray)';

LOAD
[MANDT],
[RUECK],
[RMZHL],
[VORNR],
Date(Date#([ERSDA], 'YYYYMMDD') ) AS [ERSDA],
[ERNAM],
Date(Date#([BUDAT], 'YYYYMMDD') ) AS [BUDAT],
[AFRU_TEXT],
[GMNGA],
[XMNGA],
[SMENG],
[ERZET],
[STOKZ],
[STZHL],
[AUFNR],
[PLGRP],
[DISPO],
[FEVOR],
Date(Date#([GLTRS], 'YYYYMMDD') ) AS [GLTRS],
Date(Date#([LTRMI], 'YYYYMMDD') ) AS [LTRMI],
[MATNR],
[DWERK],
[STATU],
[PLPO_TEXT],
[BMSCH],
[VGW02],
[VGW03],
[ARBID],
[ARBPL],
[KTEXT],
[MAKTX],
[PLANR],
[STPRS],

SUM (([VGW03] / [BMSCH]) * [GMNGA] /60) as [Line Hours],
// Lab (Min) / Base QTY * Confirmed QTY

if([LTRMI] > [GLTRS] ,'LATE',
if([LTRMI] < [GLTRS] ,'EARLY',
if([LTRMI] = [GLTRS] ,'ON TIME'))) as [Production Order Finish Status],

ApplyMap ('Map1', ([ARBPL]), '-Unkown-') as Category, 
ApplyMap ('Map2', ([ARBPL]), '-Unkown-') as Category2,
ApplyMap ('Map3', ([FEVOR]), '-Unkown-') as SchedulerDesc,
ApplyMap ('Map4', ([FEVOR]), '-Unkown-') as Channel,
ApplyMap ('Map5', ([FEVOR]), '-Unkown-') as Product_Family_1; 

[ZUK_CAPU]:
SELECT MANDT,
RUECK,
RMZHL,
VORNR,
ERSDA,
ERNAM,
BUDAT,
"AFRU_TEXT",
GMNGA,
XMNGA,
SMENG,
ERZET,
STOKZ,
STZHL,
AUFNR,
PLGRP,
DISPO,
FEVOR,
GLTRS,
LTRMI,
MATNR,
DWERK,
STATU,
"PLPO_TEXT",
BMSCH,
VGW02,
VGW03,
ARBID,
ARBPL,
KTEXT,
MAKTX,
PLANR,
STPRS
FROM "CO_DB_01".dbo."ZUK_CAPU" Where ([DWERK] ='UK01') AND (GMNGA <> 0);

--------------------------------------------------------------------------------------------------------------

The above script works okay without the line;

SUM (([VGW03] / [BMSCH]) * [GMNGA] /60) as [Line Hours],

It will also load with that line but without SUM in front of it (but the value of [Line Hours] is then not correct).

The error I get is below, but strangely does not appear to point at the SUM line?

--------------------------------------------------------------------------------------------------------------

Started loading data

Map1 << Sheet1 Lines fetched: 56 Map2 << Sheet1 Lines fetched: 56 Map3 << Sheet1 Lines fetched: 58 Map4 << Sheet1 Lines fetched: 58 Map5 << Sheet1 Lines fetched: 58 OTIF Finished Goods << OTIF Finished Goods Lines fetched: 70.021

Connected

ZUK_CAPU << QueryResult Script error: Invalid expression

The following error occurred:

Invalid expression

The error occurred here:

[ZUK_CAPU]: SELECT MANDT, RUECK, RMZHL, VORNR, ERSDA, ERNAM, BUDAT, "AFRU_TEXT", GMNGA, XMNGA, SMENG, ERZET, STOKZ, STZHL, AUFNR, PLGRP, DISPO, FEVOR, GLTRS, LTRMI, MATNR, DWERK, STATU, "PLPO_TEXT", BMSCH, VGW02, VGW03, ARBID, ARBPL, KTEXT, MAKTX, PLANR, STPRS

FROM "CO_DB_01".dbo."ZUK_CAPU" Where ([DWERK] ='UK01') AND (GMNGA <> 0)

The following error occurred:

Invalid expression

Data has not been loaded. Please correct the error and try loading again.

--------------------------------------------------------------------------------------------------------------

Any help or advice would be appreciated, thanks in advance.

Regards Daryn

ps.. I also noticed that the error doesn't show the last ';' but it is in the loadscript. Assume that is just the way it reports.

FROM "CO_DB_01".dbo."ZUK_CAPU" Where ([DWERK] ='UK01') AND (GMNGA <> 0);

[MayilVahanan · Accepted solution]

Hi

When you use aggregation function like sum, max, min, you need to use group by the remaining columns.. 

Hope it helps

[Daryn]

Hi, many thanks for your response.

I hadn't used group by before, but think I have worked it out?

[Line Hours]:
NoConcatenate
Load
[VGW03],
[BMSCH],
[GMNGA],

SUM (([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]
// Lab (Min) / Base QTY * Confirmed QTY

Resident [ZUK_CAPU]
Group by [VGW03], [BMSCH], [GMNGA];

Though my calculation still looks to differ to if I just use 

SUM (([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]

as a calculated measure in a table?

Any thoughts/ideas?

Again , thanks for your time and advice.

Regards Daryn

[jbhappysocks · Accepted solution]

Hi, you don't need to group this, just remove the SUM:

(([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]

You use group by when you want to aggregate something on one or more dimensions.

edit: missed this part: "It will also load with that line but without SUM in front of it (but the value of [Line Hours] is then not correct)."

Should work correct, what result do you get?