EXISTS()

[datanibbler]

Hi,

I have been here some times before with similar issues and I remember that this function is in all cases not as easy as the integrated help_file suggests. I remember for sure that it cannot be written with just one parameter as the help suggests. I think it only works if one or b oth of the key_fields in the tables are unique.

=> Well, that >or< is my issue. The scenario is this:

- In table_A (which I load earlier) that key_field (which I want to base the EXISTS() clause on) is unique.

<=> In table_B (which I load afterwards) I have a finer grain and that same key is not unique anymore.

=> Will that work if I have, in the LOAD for table_B, a clause >> WHERE EXISTS([keyfield_from_table_A], [keyfield_from_table_B]) <<?

Thanks a lot!

Best regards,

DataNibbler

[sunny_talwar]

As per my understanding, Uniqueness should not matter. Because you are trying to bring a list of keyfield_from_table_B which have a instance in keyfield_from_table_A. I have previously used this when both these fields were not unique (had duplicates) and it seemed to work, at least I hope it did

Best,

Sunny

[marcus_sommer]

Hi DataNibbler,

of course you could use exists() with only one parameter and table from the field isn't important - if you need to grab a field only from one table you need to duplicate this field or qualify them. The check will be always executed against the distinct field-values from your field.

- Marcus

[hic]

Maybe, Maybe not.

Lets say that you have

TableA:

Load ..., ID as Key From TableA;

TableB:

Load ..., ID as Key From TableB

    Where Exists(Key,ID);

Then a comparison will be made for each record: Does the current value of "ID" exist in the field "Key"? This will work fine - initially. But if you use "not Exists()", you will get into trouble: Soon the "Key" field contains also values from TableB, and a duplicate in TableB will hence not be loaded. The comparison is not made against "Key" in table A. It is made against all previously loaded values - from both A and B.

You probably should try:

TableA:

Load ..., ID as Key, ID as KeyA From TableA;

TableB:

Load ..., ID as Key From TableB

    Where Exists(KeyA,ID);

or you can try

TableA:

Load ..., ID as Key From TableA;

TableB:

Left Keep Load ..., ID as Key From TableB;

HIC

[datanibbler]

Hi Marcus,

that confuses me.

I guess that it will work then which is all I want for the moment.

Well, I guess I'll have to read up on that one once more and also read all the blogs and stuff on the issue.

Best regards,

DataNibbler

[giakoum]

Uniqueness matters a lot :

see this document for the solution. I think what you propose is ok :

The exists issue

[datanibbler]

So - it seems there exist() several opinions on what works and what doesn't regarding that function ... I will try.

[marcus_sommer]

Not really different opinions - we meant the same and have only described it in a different way. As usual hic had explained it an easier to understandable manner as me but there are more various options to use exists() so the ressource from giakoum is very useful.

- Marcus

[swuehl]

TableA:

Load ..., ID as Key From TableA;

TableB:

Load ..., ID as Key From TableB

     Where Exists(Key,ID);

Then a comparison will be made for each record: Does the current value of "ID" exist in the field "Key"? This will work fine - initially. But soon, the "Key" field contains also values from TableB, and a duplicate in TableB will hence not be loaded. The comparison is not made against "Key" in table A. It is made against all previously loaded values - from both A and B.

Henric, maybe it's too hot in here, but I don't understand your conclusion.

Why should a duplicate of table B not be loaded?

I believe your comment is relevant for a WHERE NOT EXISTS() clause, though.

[hic]

I agree with Marcus. I don't think we disagree on what works and what doesn't.

On the issue of one parameter: Of course the Exists() function works fine also with just one parameter.

On the issue of uniqueness: It is as I see it not a matter of uniqueness. It is just that many think that the function makes a comparison with a field in a previous table, when it in fact makes a comparison with all loaded values of this field; also the ones from the current table. Hence, the second value of a duplicate in the same table will make Exists() return TRUE.

HIC