Understanding Load with SQL

[#1]

Hi, i'm trying to understand the Load functionality but i cant make it work with my SQL statement.

I want first to Load some SQL statement and then use this Resident table to filter some value.

I have this:

ODBC CONNECT TO [DB] (XUserId is CHXfAKVGTZNMTbUF, XPassword is KXVLKHFMSLdIXXJGQJMCDRC);

A:

LOAD *;

SQL SELECT external_system_id from nsu_external_systems  ;

ODBC CONNECT TO [DB2] (XUserId is RTJJKKVGTBNETYYB, XPassword is PWVHVBVOBbceGXJGQJMCTSA);

B:

LOAD *;

SQL SELECT description, description_clean, responsible, external_system_id from T53421.nsu_system_interfaces_errors;

H:

NoConcatenate

LOAD *

Resident A

Where external_system_id < 5;

This doesnt work, it says "Field external_system_id not found" or something like that.

I dont get it

Can someone help me please

Thanks!

[pat_agen]

hi,

the field may have been sent back in uppercase. So you would need to write

     where EXTERNAL_SYSTEM_ID < 5

.

[#3]

Oh! Pat you are a genious

Ok, that fixed the error but is it brings me all the A table. Is like the Where clause isnt working

Any idea?

Thanks!

[pat_agen]

start by taking out your load and sql call to table B (you can always bring this back in afterwards)

your field EXTERNAL_SYSTEM_ID is present in both A and B and I'd get that out of the way to work out why your 3rd call isn't giving you your expected results.

by the way if you really don't want the EXTERNAL_SYSTEM_ID which are gretaer than 5 you should put your where clause in your sql and just not bring them into qv.

[#5]

Pat i know that i can filter my statement directly on the SQL statement.

I put this example to make some tests, but i dont understand why it brings me all records and not only those which are <5.

Thanks for your help, i will continue testing it

[#6]

Another question:

How can i do a "Like" statement on a resident table B base on a value on a resident table A?

Something like this...

C:

LOAD DESCRIPTION as "DESCRIPTION_2"

Resident A,B

Where FIELD_FROM_A LIKE '%FIELD_FROM_B%';

Thanks!

[nagaiank]

What is the datatype of the field EXTERNAL_SYSTEM_ID in the SQL Server table? Is it varchar or numeric? Depending on this, you may modify your WHERE clause.