Using a sql stmt with NOT IN(...)

rbuser

Is it possible to use a NOT IN in Report Builder, without entering the SQL
statement manually. How can this be done?

Example:

SELECT * FROM t1 where a NOT IN (SELECT a FROM t2);

---

Thanks,

Imran



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Imran,

Inside the search tab of the query designer after you add a field to the
bottom window, you will see a drop down box for the operator. If you select
the "Not In List" option, the NOT IN equivalent will be added to your SQL
statement.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

nicocizik

Hi,

Unfortunately the In List value in Dade does not support nested Select
statements. The value edit box is expecting a comma delimited list of
values of that field's datatype. If you need to add a Select statement to
the NOT IN operator, you will need to manually edit the SQL code inside the
SQL tab. Note that you will then be unable to link your dataviews unless
you do so in Delphi code. Thanks for the feedback, this is something that
we may enhance for a later release of ReportBuilder.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

>Inside the search tab of the query designer after you add a field to the

I tried adding the "a" field to the bottom of the search tab, with the
"Not In List" operator, and with value set to "SELECT a FROM t2" (without
the quotes).

It didn't work. I get "Invalid SQL statement". I checked the SQL
statement, and it was in fact invalid; the NOT IN(...) had nothing between
the parentheses.

I then tried surrounding the value (SELECT a FROM t2) part of the search
with single quote, double quotes, parentheses, parentheses with single
quotes... but nothing worked.

Please help.



--- posted by geoForum on http://delphi.newswhat.com