INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Select from subquery

Select from subquery

(OP)
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

RE: Select from subquery

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
 

RE: Select from subquery

(OP)
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.   

RE: Select from subquery

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close