3 Replies Latest reply: May 28, 2013 2:54 PM by LEONARDO GIULIANETTI

Problem Inner Join

Hello Guys,

I'm having a problem in the code below.

I'm loading a customer table and they have multiple update dates. I need to load the older, but my code is not working.

The function "Max", is not working in my code. She keeps bringing all dates.

AuxiliarCliente:

[@40:49]  as Cliente,
Max(Mid(FileName(),17,8)) as [Data de Modificação]
FROM
Inbox\Clientes\INBOX_CLAGCT*.*
(fix, codepage is 1252)
Where ([@9:9]='D')
group by [@40:49];

Cliente:
Load Cliente&[Data de Modificação] as ChaveCliente2
Resident AuxiliarCliente;

DROP Table AuxiliarCliente;

@56:59&@40:49&Mid(FileName(),17,8) as ChaveCliente2,
If(Left(@40:49,1)<>'0',Mid(@40:49,1,10),
If(Left(@40:49,2)<>'0',Mid(@40:49,2,10),
If(Left(@40:49,3)<>'0',Mid(@40:49,3,10),
If(Left(@40:49,4)<>'0',Mid(@40:49,4,10),
If(Left(@40:49,5)<>'0',Mid(@40:49,5,10),
If(Left(@40:49,6)<>'0',Mid(@40:49,6,10),
If(Left(@40:49,7)<>'0',Mid(@40:49,7,10),
If(Left(@40:49,8)<>'0',Mid(@40:49,8,10),
If(Left(@40:49,9)<>'0',Mid(@40:49,9,10), Mid(@40:49,10,10)))))))))) as Cliente,
@56:59  as Empresa,
@60:63  as [Grupo de Contas],
@64:78  as Tratamento,
@79:113  as [Nome Cliente],
@219:253  as Endereco,
@299:308  as CEP,
@309:353  as Bairro,
@354:355  as Pais,
@357:358  as [UF Cliente],
@790:790 as [Tipo Alteração],
Mid(FileName(),23,2)&'/'&Mid(FileName(),21,2)&'/'&Mid(FileName(),17,4) as [Data de Modificação]
FROM
Inbox\Clientes\Inbox_CL*.*
(fix, codepage is 1252)
WHERE([@354:356]= 'BR');

• Re: Problem Inner Join

I think,

If

The function "Max", is not working in your code.

And If you find the max of dates then you write as

max(date(Mid(FileName(),17,8)))

Regards

vishwaranjan

• Re: Problem Inner Join

The max function is done for each loaded file separately. Try this:

Temp:

[@40:49]  as Cliente,
date#(Mid(FileName(),17,8),'YYYYMMDD') as [Data de Modificação]
FROM
Inbox\Clientes\INBOX_CLAGCT*.*
(fix, codepage is 1252)
Where ([@9:9]='D');

AuxiliarCliente: