Error in load script when using Load *

[curiousfellow]

I get an expression error when loading from resident table

Short description of the script :

Table_temp1:

Load *

Resident othertable1;

Join(Table_temp1)

Load *

500 as new_field

Resident other_table2;

So far so good

The next option runs fine :

Table_temp2:

Load

sum(if((Table_temp1.field1=7 and  Table_temp1.field2=8) or (Table_temp1.field1=7 and  Table_temp1.field2=19)),Table_temp1.field3,0) as newfield

Resident Table_temp1;

Next one does not, the error message is "Invalid expression":

Table_temp2:

Load *,

sum(if((Table_temp1.field1=7 and  Table_temp1.field2=8) or (Table_temp1.field1=7 and  Table_temp1.field2=19)),Table_temp1.field3,0) as newfield

Resident Table_temp1;

The only difference is "Load *"

The above script is a simplified example of the real script, my problem is simple : why can't I use Load *

[Chanty4u]

did u try with

Load*,

[swuehl · Accepted solution]

When using aggregations in your LOAD, you need to add all fields that are not embedded into an aggregation function to the GROUP BY clause field list.

[maniram23]

Hi,

you can try this like.

Table_temp1:

Load *

Resident othertable1;

drop table othertable1;

Join(Table_temp1)

Load *,

'500' as new_field

Resident other_table2;

drop table other_table2;

So far so good

The next option runs fine :

Table_temp2:

Load

sum(if((Table_temp1.field1=7 and  Table_temp1.field2=8) or (Table_temp1.field1=7 and Table_temp1.field2=19)),Table_temp1.field3,0) as newfield

Resident Table_temp1;

I think you are created one flag filed of"500".

the flag always should be in ( ' ' ) single codes.

Regards.

[curiousfellow]

Thank you swuehl , solved.