Results where item not in subquery?

rbuser

Hi,

Is it possible with report builder to return records where a certain field
is NOT in the results of a subquery? For example (using an entirely
artificial query!):

SELECT * FROM EMPLOYEES WHERE ID NOT IN (SELECT * FROM STAFF WHERE NAME =
'SMITH')

We do not allow our users to edit SQL manually, so this has to be possible
just using the RAP components.

Thanks,

Steve Branley

nicocizik

Hi Steve,

The latest version of ReportBuilder allows SubSelect statements in the Where
clause. You can simply set the operator (in DADE) to "Not In List" and add
the subselect to the Value of the search condition.

--
Regards,

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

rbuser

Sorry for being thick, but do you mean on the 'Search' tab you enter the
required subselect statement? If so, how would you go about using parameters
within that query? For example:

SELECT * FROM EMPLOYEES WHERE ID NOT IN (SELECT * FROM STAFF WHERE NAME =
@NAME)

and @NAME comes from a user autosearch?

Is that possible?

Thanks,

Steve

nardmoseley

To accomplish that you need to manually edit the SQL and use the :ParamName
notation

example:

SELECT * FROM EMPLOYEES WHERE ID NOT IN (SELECT * FROM STAFF WHERE NAME =
:ParamName)


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com