The exists function syntax

[#1]

There are two tables Main Data and Aircraft Types. Their only common field is %Aircraft type Id, which means "type of aircraft". Main data does not contain all the types listed in aircraft types. We want to load only the types which exist in both tables.

This is how it can be done in the script editor -

[Aircraft Types]:

LOAD AC_TYPEID as [%Aircraft Type ID],

     AC_GROUP as [%Aircraft Group Type],

--------more fields here.

FROM

[..\Data Files\CSVs\Aircraft_Base_File.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

Where Exists([%Aircraft Type ID], AC_TYPEID);

My book says that WHERE EXISTS is actually checking if the %Aircraft Type ID's in Aircraft types exist in main data or not. I go to Main Data tab and see a field %Aircraft Type ID. So, I know that we are comparing [%Aircraft Type ID] of Main with AC_TYPEID of Types.

This syntax is unintuitive. Do I have to search the script all the time to find out which table a field belongs to ?

I was hoping for syntax like this -

Where Exists([Main Data].[%Aircraft Type ID], [Aircraft Types].[AC_TYPEID]);

OR even this -

Where [Aircraft Types].[AC_TYPEID]

EXISTS IN([Main Data].[%Aircraft Type ID]);

[swuehl]

You can prefix the field names with their table names, if you want and if that's what you are asking for here.

Please have a look at the QUALIFY statement in the help, which makes such things easier (but key fields can't be prefixed like this anyway).

[its_anandrjs]

Update Sorry i forget some think now updated please check now.

I believe you are not selecting correct field see the small example for the Where Exists

Table1:

Load * Inline

[

A

1

2

3

4

5

6

];

NoConcatenate

Table2:

Load * Inline

[

AA

1

2

3

4

5

7

]Where  Exists(A,AA);

In this code you are get only Table1 Data and also if it is available in the Table2 also with field name AA then you get 1,2,3,4,5 which is common in both the tables.

Regards