Problem with WHERE EXISTS() - simple? All the more annoying ...

[datanibbler]

Hi,

I am trying to do something very simple: Load an existing qvd (which I saved yesterday) and then concatenate today's data and store it away again.

I do that using a WHERE NOT EXISTS() clause on the field with the date.

The issue is, since both the date_field in my history_table and the date_field in today's table actually have the same name, I could write the EXISTS function with just one instead of two parameters, right?

<=> Well, that didn't seem to properly work. It never does with me. Strange. I actually prefer the two_parameter_method anyway, it is
        clearer and easier to understand for others.

=> So I use a RENAME FIELD statement, after loading the existing history_qvd, to give the date_field there a different name.

=> Then I want to concatenate today's data, so I have to give that same name as an alias to the date_field so that it will be properly
     concatenated, right?

    <=> In the WHERE clause, however, I can (must) still use the original field_name - thus I have two different field_names for my
            WHERE NOT EXISTS clause.

<=> Now I'm getting the error message "Field not found <[original fieldname]>"

Can anybody help me there?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I am just running the script up to the point where I have both tables, just before I concatenate the records from the one to the other, so I'll see what is the issue - I hope ...

[#2]

Have you tried insted of "WHERE NOT EXISTS (....)" WHERE LEN(Datum)=0;

[datanibbler · Accepted solution]

Hi all,

thanks for the helpful answers!

It seems both EXISTS() and NOT EXISTS() can be tricky and writing that function with just one parameter somehow seems to always go wrong. One has to do some renaming so that this function can be written with two parameters. I devised a workaround for this one and that seems to work fine since in this case I just want to append every day's data, so I only have to check whether the present day is already in the history_table or now.

By extracting in a RESIDENT a small list with only the dates already present in that table, I can quickly decide whether the present day is there or not and I don't need any further checks.

This is solved then.

Best regards,

DataNibbler