Hi,
I have a SQL query that I'm trying to mimic in Report Studio.
in the where clause, I have a correlated subquery that looks like this:
select * from note
WHERE NOTE.LINE in (select distinct max(note2.line)
from HNO_NOTE_TEXT note2
where note2.note_id = note.note_id)
I can't seem to find a way to recreate this in Report Studio.
Any ideas?
-Sam
Hi,
I thnik you can do this by using query refrrence.
1. Create the sub query first.
2. Create a new query.
3. Drag the sub query to the right of query created in step2.
4. Now you can filter using the values from sub query.
HTH
you might have to allow Cross Product.
Quote from: skamara1 on 17 May 2010 11:47:40 AM
Hi,
I have a SQL query that I'm trying to mimic in Report Studio.
in the where clause, I have a correlated subquery that looks like this:
select * from note
WHERE NOTE.LINE in (select distinct max(note2.line)
from HNO_NOTE_TEXT note2
where note2.note_id = note.note_id)
I can't seem to find a way to recreate this in Report Studio.
Any ideas?
-Sam
I think this type of correlated subquery can be replaced using OLAP type function rank. You rank all lines for a given Id. the highest value based on line is receiving rank of 1. You can then filter in a summary filter on this rank item..