topic Re: Concatenation field error on ODBC connection in Connectivity & Data Prep

Concatenation field error on ODBC connection

Fri, 05 Feb 2016 03:47:13 GMT

HI:

I'm confused because when I used an extraction of information coming from a SQL Server database using Excel for prototyping the following load script worked:

[DATOS]:

LOAD [UBICACION],

[FECHA FACTURA],

mid([FECHA FACTURA],1,4) as [AÑO],

mid([FECHA FACTURA],6,2) as [MES],

[AÑO]&[MES] AS [AÑOMES],

[C.P.] AS CP

FROM [lib://BI-SenseAutomotriz/Informacion.xlsx]

(ooxml, embedded labels, table is DATOS);

So [AÑO],[MES] and [AÑOMES] new fields where created with partial extraction from [FECHA FACTURA] field and concatenation worked fine.

But using ODBC connection to original database script don't work. It says field [AÑO] don't exist!.

Here is the script:

LIB CONNECT TO 'DATA';

LOAD UBICACION,

"FECHA FACTURA",

mid("FECHA FACTURA",1,4) as [AÑO],

mid("FECHA FACTURA",6,2) as [MES],

[AÑO]&[MES] AS [AÑOMES],

"C.P.";

SQL SELECT UBICACION,

"FECHA FACTURA",

"C.P."

FROM RENUNIAPP.dbo."Custom_Clientes_Ventas_Autos";

Field not found - <AÑO>

I've tryed to change fields name with no "Ñ" spanish letter.

Any clues?.

Re: Concatenation field error on ODBC connection

Gysbert_Wassenaar — Fri, 05 Feb 2016 17:30:07 GMT

You can create a field in a load statement and in that same load statement assume it already exists in the source.

Try this instead:

LIB CONNECT TO 'DATA';

LOAD *, [AÑO]&[MES] AS [AÑOMES];

LOAD UBICACION,

"FECHA FACTURA",

mid("FECHA FACTURA",1,4) as [AÑO],

mid("FECHA FACTURA",6,2) as [MES],

"C.P.";

SQL SELECT UBICACION,

"FECHA FACTURA",

"C.P."

FROM RENUNIAPP.dbo."Custom_Clientes_Ventas_Autos";

Re: Concatenation field error on ODBC connection

Mon, 08 Feb 2016 17:58:43 GMT

Thanks Gysbert:

One thing I'm noticing is that there is no a clear or logical explanation about how come this works some situations and others don't. Do you know, or someone else know a documentation where all the best practices are found?.

Thanks!.

Re: Concatenation field error on ODBC connection

Gysbert_Wassenaar — Mon, 08 Feb 2016 18:12:48 GMT

You can never use a value from a field before the field (and thus the value) exists. There are no exceptions.