Topic
2 replies Latest Post - ‏2010-11-07T22:21:22Z by SystemAdmin
BIenthusiastic
BIenthusiastic
1 Post
ACCEPTED ANSWER

Pinned topic Create a report based on a Query

‏2010-11-04T18:27:26Z |
Create a report based on a manual query.
In the manual query can a sub-query be written like shown below?

select a1, a2, a3 from table 1where a1 in ---> sub-Query
(
SELECT a1
FROM table1 --> say this as Main Query
GROUP BY a1
HAVING ( COUNT(a1) > 1 )
).

The report needs to display the list of a1 values within the list of a1 values from the Main Query

The list report is generated using writing the main query manually. Then I tried to hide the main list and then inserted a sub-report, with filter as a1 in mainquery.a1
This just returns only for one value of a1.
How can I achieve the above task?

Thanks
Updated on 2010-11-07T22:21:22Z at 2010-11-07T22:21:22Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    280 Posts
    ACCEPTED ANSWER

    Re: Create a report based on a Query

    ‏2010-11-07T22:19:29Z  in response to BIenthusiastic
    Your best option is to rewrite this to use a Join operation between the two queries. Query1 will retrieve a1, a2, and a3. Query2 will retrieve a1 with the appropriate filter on the count. Then the join will match Query1.a1 to Query2.a1 using an inner join.
    • SystemAdmin
      SystemAdmin
      280 Posts
      ACCEPTED ANSWER

      Re: Create a report based on a Query

      ‏2010-11-07T22:21:22Z  in response to SystemAdmin
      My apologies, I didn't realise that this was a Series 7 question. Disregard my previous message. If I have time I will fire up a dusty version of Impromptu to see if I can answer your question.