Use of "not exists" in an IQD

[doddy]

We often edit our IQD's to do things that the original IMR could not.

I have one now that includes in its WHERE clause a correlated subquery, a "not exists" condition that is correlated to 3 fields in the main query.

While it runs fine in a native Oracle 9 session, Transformer refuses to accept the IQD as a data source, giving the following errors ...

TR0118 Transformer can't read the database...etc
DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
DMS-E-SS_PARSER, An error was detected during processing of the SQL request.
DMS-E-SS_INCONS_LOGICAL, An inconsistent logical operation was specified.

We are using Oracle 9, Impromptu 7.1 and PP Transformer 7.0

Any advice would be wonderful.

regards,
doddy

 

[shotech]

Hi Doddy,

Why you don't use "is missing" in your impromptu report instead of "not exists" in your IQD?

Regards,

Shoteck

 

[bingojingo]

One method I've used for IQDs where building the query in Impromptu is too much pain is, rather than manually editing the IQD, to create an Oracle view which returns what I want and then create an IMR / IQD against that.

Slightly long-winded but gets round problems like yours which I've had with manually edited IQDs.

 

[doddy]

Many thanks for your responses.

Shotech, I think the "is missing" in IMR is to deal with null field values rather than correlated subqueries like mine. If you imagine I'm trying to count sales of item A, but not if an item B was sold on the same order. The subquery would collect item B sales only and be correlated to order number in the main query getting item A sales, so that the "not exists" condition removes rows with those order numbers from the main query data.

Bingojingo, that's exactly what I've done now. Our database is handled by contractors and I can't get my hands on it without due process, so it's even more of a pain to hand over stuff like this but there seemed to be no alternative. Thanks for your help.

I'm still interested (though it's academic now) whether IMR/IQD can deal with such correlated subqueries. If anyone knows this definitively, please share it.

regards,
doddy