Trying to mimic a SQL correlated subquery in Report Studio

[skamara1]

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

[kattaviz]

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

[tupac_rd]

you might have to allow Cross Product.

[blom0344]

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..