Issue with "and" in a statment

[#1]

HI, t

he statiment if(peek... and)  it depend from OrderStatus ,but i cannot solve it ...please how it have to be?

thanks

ODBC

CONNECT TO AirArtecSu;
Alonte:
LOAD *,week(Date) as week;
Load *, if((OrderNumber = peek(OrderNumber)) and (OrderStatus='90'),0,1) as CountOrder;
Load *, if(match(AlsisScope, 'J' or 'G'), 'Yes', 'No') as PotentialIECom;
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B';

Inner Join

SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.oh;

Inner Join

SQL SELECT ftgnamn,ftgnr as AdministrativeEntity
FROM ArtecAirSU.dbo.fr;

[Gysbert_Wassenaar]

Temp:

SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B';

Inner Join

SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.oh;

Inner Join

SQL SELECT ftgnamn,ftgnr as AdministrativeEntity
FROM ArtecAirSU.dbo.fr;

Alonte:
LOAD *,week(Date) as week;
Load *, if((OrderNumber = peek(OrderNumber)) and (OrderStatus='90'),0,1) asCountOrder;
Load *, if(match(AlsisScope, 'J' or 'G'), 'Yes', 'No') as PotentialIECom

RESIDENT Temp;

Drop Table Temp;

[#3]

Hi thanks for the reply.

the program said unknow statement for "RESIDENT Temp;"

[oknotsen]

That can mean two things:

You forgot to add "Temp: " at the start of Gysbert Wassenaar's code.

Or

The table that is created has exactly the same structure of an already existing table.

If the later, add "NoConcatenate" between "Temp: " and "SQL".

[#5]

I have not forgot the statment.

This is the complate code, please what is wrong?

ODBC  CONNECT TO AirArtecSu;

Temp:
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B';

Inner Join

SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.oh;

Inner Join

SQL SELECT ftgnamn,ftgnr as AdministrativeEntity
FROM ArtecAirSU.dbo.fr;

Alonte:
LOAD *,week(Date) as Week;
Load *, if((OrderNumber = peek(OrderNumber)) and (OrderStatus='90'),0,1) as CountOrder;
Load *, if(match(AlsisScope, 'J') or match(AlsisScope, 'G') , 'Yes', 'No') as PotentialIECom;

RESIDENT Temp;

Drop Table Temp;

[Gysbert_Wassenaar · Accepted solution]

Remove the semicolon after PotentialECom:

Load *, if(match(AlsisScope, 'J') or match(AlsisScope, 'G') , 'Yes', 'No') as PotentialIECom

RESIDENT Temp;

[#7]

many thanks