Filter a query subject - Embbeded filter

[raj_aries]

Hi All,

I have two tables A, B and am I need to filter table B to derive unique values. Hence, I have used an embedded filter on that query item so that the data is filtered. Now, I am doing a left outer on table A & table B. Since I am using embedded filter, the filter is being applied in the join instead of where clause. Is there a way where I can force the join to be applied on the whole query instead on that particular query subject.

This is what am getting

select A.*,B.* from
table a left join b on b.column=a.column and state='IL'

Expected should be

select A.*,B.* from
table a left join b on b.column=a.column
where
state='IL'


I think can I do an inner join instead of outer to avoid this scenario. Please suggest if my approach is correct or not

[MFGF]

Hi All,

I have two tables A, B and am I need to filter table B to derive unique values. Hence, I have used an embedded filter on that query item so that the data is filtered. Now, I am doing a left outer on table A & table B. Since I am using embedded filter, the filter is being applied in the join instead of where clause. Is there a way where I can force the join to be applied on the whole query instead on that particular query subject.

This is what am getting

select A.*,B.* from
table a left join b on b.column=a.column and state='IL'

Expected should be

select A.*,B.* from
table a left join b on b.column=a.column
where
state='IL'


I think can I do an inner join instead of outer to avoid this scenario. Please suggest if my approach is correct or not

What it's doind sounds correct to me - you have only defined your filter on one of the two query subjects so the generated query includes the check as part of the join.

To filter the result, try creating a model query subject containing all the items from Query Subject A and Query Subject B, and add your embedded filter to this.

Regards,

MF.

[raj_aries81]

Thanks MFGF, so that makes the new QS to behave as a view :). Is that correct

Thanks
Raj

[rsaripa]

It depends on whn and where the filter and join are defined.

Lets say if the join is defined in Business Layer & you put the filter in Presentation Layer, then you should see the filter in where clause.

If you define the filter in the same layer  where the joins are defined( business layer), then the filter will work as a sub-query.

[raj_aries81]

It depends on whn and where the filter and join are defined.

Lets say if the join is defined in Business Layer & you put the filter in Presentation Layer, then you should see the filter in where clause.

If you define the filter in the same layer  where the joins are defined( business layer), then the filter will work as a sub-query.

Ohh I didn't knew that..Thanks for the information. Let me try to do a quick check with two tables ...Thanks :)

-Raj