Tasos,

This is a muddle of bad logic and bad syntax. You are forcing your query
to work too hard and you are risking wrong results. Follow Pavel's advice
about using EXISTS() for your existential test.

Here are some of your errors:

Select * from V_CASES
where V_CASES_GRID.case_id
-- in ayou cannot refer to a different table in the where clause because
you have no join here.

in
(
select distinct(V_CASES.case_ID) from T_cases
where T_CASES.case_id>0

-- you have no correlation here and also you have some unfathomable ambiguities

/* ------------here follow the dynamically created criteria */

-- you don't show these "dynamically created criteria" but one cannot help
supposing that they are also either or both syntactically wrong or ambiguous

and T_CASES.case_id between 19 and 30
/* ---------------------------------------- end of criteria */
order by V_CASES.case_id
)

-- again, no join in sight, so you can't refer to a different table.

Try to proceed using the following as a model:

Select vc.* from V_CASES vc
join V_CASES_GRID vcg
on vcg.case_id = vc.case_id
where EXISTS (
SELECT 1 FROM T_cases tc
where tc.case_id = vcg.case_id
/* and tc.case_id > 0 NOT NEEDED */
and
/* ------------here follow the dynamically created criteria */
and tc.case_id between 19 and 30
/* ---------------------------------------- end of criteria */
)
order by vc.case_id

./hb








At 02:42 AM 21/02/2005 +0200, you wrote:

>First of all,
>Pavel, thanks for your reply
>
>[>>]Did you forget WHERE clause here?
>"and case_id>0" had to be "where case_id>0"
>
>[>>]Why do you need internal SELECT at all? Try
> select * from T_CASES
> where case_id>0
> and case_id between :minID and :maxID /* your parameters */
>Since case_id is PK, there is no need to select distinct values.
>
>In the real program the query is
>------------------------------- QUERY ----------------------------
>Select * from V_CASES
>where V_CASES_GRID.case_id in
>(
>select distinct(V_CASES.case_ID) from T_cases
>where T_CASES.case_id>0
>/* ------------here follow the dynamically created criteria */
>and T_CASES.case_id between 19 and 30
>/* ---------------------------------------- end of criteria */
>order by V_CASES.case_id
>)
>---------------------------------------------------------------------
>Where V_CASES is a view which joins T_CASES with enough secondary tables
>according the selected criteria fields in the GUI interface.
>My thought was to find the needed rows of T_CASES (using the subquery) and
>ONLY for them select the revelant rows of V_CASES which has many more fields
>(because of joined secondary tables). But - unfortunately - what happens is
>that all the T_CASES tables is readed twice :-(
>
>The distinct is used because in some specific criteria can be returned more
>than one row (in the view) for each row in the table.
>
>Regards
>
>Tasos Kyriakos
>Athens, Greece
>tkyr@...
>
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>