Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

craig322 (MIS) (OP)
21 Aug 01 14:58
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 (MIS)
29 Aug 01 3:30
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 (MIS) (OP)
30 Aug 01 7:32
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.   

Helpful Member!  ADB1 (Programmer)
30 Aug 01 9:50
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.

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!

Back To Forum

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