Select from subquery 1

[craig322]

Does anyone know how to do use a subquery in cognos without actually typing in the sql.

For example if I wanted a report to show me the output of this sql (without actually typing the sql).

The reason I don't want to enter the sql is that I still want the flexibility of using prompts.

Here is a simplified example of what I mean.

SELECT field_a
FROM table_a
WHERE field_b = (SELECT MAX(field_c)
FROM table_c)

Thanks

Craig

 

[PaulBMW]

Craig,

The way to do this is to create two queries within Impromptu, then apply a filter...... when the filter is being applied select field_b in 'Dataset' this will allow you to locate your sub-query (IMR).

I hope this helps ???

Paul

 

[craig322]

I understand what you are saying to do, but I can't see a way to point to another query using the Filter tab.

Can you give me a little more help.

 

[ADB1]

Firstly write the sub query in Impromptu (SELECT MAX(field_c)FROM table_c).

Then construct your main query, apply a filter, select the column you wish to filter, in this case field_b. Select IN and then select DATASET, a dialog box then appears, in this window, find your sub query, and in the relevant box, select the column on whcih you wish to filter, in this case, MAX(field_c).

Cheers.