From Clause select -- group by

I need help!

The simple question is this: is it possible to restrict a query on a value that is not selected?

The far more complex explanation:
I have a dynamic list screen. The user inputs values to restrict the query by, then chooses the order in which to see the data (order by)

and what grouping the data will be in (group by). Depending on the group by chosen, the block will hide or display certain columns.

The hitch is that the user is allowed to restrict what the query brings back by entering criteria that pertains to columns that will not be displayed/selected.

Currently I'm doing something pretty complex to make this all work. I have a control block on the top of my screen that has all the fields that the user can input something to restrict the query. The user also has the
option to choose how the data will be ordered and what it will be grouped on.
Once a user enters those fields and hits Execute Query, the criteria is copied to an
invisible block (via the copy property of the block item) that has all possible fields on it and a query is executed. This will select all the line items that meet the user-entered criteria. In this initial query
I cannot simply select the fields that will be displayed because the user
may have entered criteria for a column that will not be selected in the form clause query
if this query only selects (and thus groups by) the columns that the user wishes
to see. Furthermore, if all possible columns are selected in one query, and therefore
the query results are grouped by all columns (other than the one summed), then the
results are not summed by the criteria entered by the user.

So, after this initial query runs, I populate the block that is displayed to the user by
performing a select based on SYSTEM.LAST_QUERY to select only the data that will be displayed, grouped the way the user specified and display it
in a third block.

Maybe this example will make my problem clearer:

If I have a table (Salary) of this structure:

   emp dept title salary

   Joe   14     Cler    30,000
   Ann   14     Mgmt    50,000
   John  15     Cler    28,000
   Sally 16     Mgmt    60,000
   Mark  16     Mgmt    55,000

and I want to know how much money is being spent on Management salaries grouped by dept
(i.e. I put "Mgmt" as criteria and dept as my grouped by option in my control block),
I would theoretically run this query:

    select dept, sum(salary)
    from salary
    where title = 'Mgmt'
    group by dept;

This is the nested query that Oracle will create, using the from clause query:
  select dept, sum(salary)
  from (select dept, sum(salary) from salary)   where title = 'Mgmt';
And this will fail, since title is not a column that is in the subselect.

But this will return no rows because title is not being selected in the from clause query
and therefore cannot be used as a restriction in the outer where clause.

So, currently, I copy the value "Mgmt" to my invisible block that has the fields
emp, dept, title, and salary on it and then execute my query. This would return 3 lines:

   Ann   14 Mgmt 50,000
   Sally 16 Mgmt 60,000
   Mark  16 Mgmt 55,000

Then, I navigate to a third block (which is the block that actually is displayed to the user) and
execute-query, selecting only the information I want to see and basing it on SYSTEM.LAST_QUERY, i.e.

   select dept, sum(salary)
   from SYSTEM.LAST_QUERY ; (note: concatenation markings left out for readability)

This is set in the From Clause property of this third block.

...which would then return the two lines I want:

   14 50,000
   16 115,000

So in essence, I am performing two selects to get one screen of information.

This just seems far more complex than it needs to be, but I can't figure out
another way around it. If you understand what I trying to do, and have already
come across a solution, or a different way to accomplish the same thing,

I would really, really appreciate your help.

Thanks in advance,
Laura.

PS. Please also email me any responses - my newsgroup feed is pretty flakey. Received on Fri Sep 25 1998 - 22:44:30 CEST