subquery with prompt 1

[138006]

I need to create a report in MSTr which will contain a subquery with a prompt inside the subquery. I thoufht of using Advanced FIlter option where I wrote ApplyComparison . But that subquery in that filter is fine when there is no prompt or atmost value prompts. But my prompt will be elemnt list prompt. How to do it?

This is what I need to do:

A.a IN (SELECT B.a
FROM
A,
B,
C,
D
WHERE
( .........
AND B.b IN PROMPT('Select b')
AND D.d IN PROMPT('Select d') )


These are not value prompts. How to achieve this?

Thanks,

 

[nlim]

138006, I don't think there is a "generic" way to make MSTR generate the SQL you want. Like I posted before, I would not try to make MSTR generate the SQL I want.

What is your report requirement? Maybe you don't need the subquery, maybe MSTR can generate SQL that gets the same answers you need...

 

[entaroadun]

You don't need an ApplyComparison to force the subquery. Just use a Report Filter.

Create a report that returns just the B.a attribute and the element prompts in the filter.

Use this report as a filter in your main report.

 

[138006]

Hi,
Thanks for the help. Another query:

This is what I want--

select distinct T1.col1
from T1
where T1.col1 in (select distinct col1 from T2 where Prompt(T2.col1))

As you can see, there will be a prompt on col1 of T2. I want to pass the user selected values as a filter to col1 of T1.

How to do it?
I tried with Applycomparison --

ApplyComparison ("#0 in (select col1 from T2 where #1)", [T1.col1]@ID, ?[Prompt1])

However this is what I get--

select distinct T1.col1
from T1
where ((T1.col1)
in (select T1.col1
from T1, T2
where T1.col1 in (select col1 from T2 where T2.col1 in ('xxxxx'))))

Can you please let me know why this came ? And how to get the previous SQL which I want?

Thanks and regards

 

[entaroadun]

Again, use a report filter.

You don't need the DISTINCT keyword in the subquery; you get the same results anyways. It's actually slower, since the RDBMS has to materialize the DISTINCT list; without DISTINCT, the RDBMS may be smart enough to do an index scan directly, if the field is indexed.

The DISTINCT keyword in the outer SQL pass should be there automatically, as long as the field you are querying is not the logical key for that logical table.

Oh, BTW, I'm assuming that col1 is an attribute...

 

[138006]

The report filter option doesn't work. The reason is the attribute definitions are different. What I did is create a report which has an attribute A1 (T1.col1) and a prompt on A1.

In another report I put A2(T2.col1) and the first report as filter. How will it work since A1 and A2 are from different tables.

This is the crux of the problem. I already wrote that thing in the previous message -- "As you can see, there will be a prompt on col1 of T2. I want to pass the user selected values as a filter to col1 of T1."

Any help?
Thanks

 

[entaroadun]

MSTR is generating the cartesian join because the col1 on T1 and col1 on T2 are unrelated. It's heading off the possibility of a SQL error.

In the SQL that's being generated:

select distinct T1.col1
from T1
where ((T1.col1)
in (select T1.col1
from T1, T2 (1)
where T1.col1 in (select col1 from T2 (2) where T2.col1 in ('xxxxx'))))


Does T2 (1) have the same alias as T2 (2)?