Re: [SQL*Forms 3.0] select DISTINCT possible?
Date: 1996/11/06
Message-ID: <55qp4o$1k4_at_freenet-news.carleton.ca>#1/1
Frampton Steve R (3srf_at_qlink.queensu.ca) writes:
> Hello:
>
> I'm developing a three-block forms application for tracking
> employee absence information. I'm having difficulty with one
> of the blocks, which is being used to provide summary information
> on absences taken during the year.
>
> For this block, I want to query only the *distinct* absence
> codes taken by the employee, along with the number taken
> during the year.
>
> From SQL*Plus, I can accomplish this with:
>
> select distinct(absn_reason_code),
> sum(total_override_crdt_loss)
> from ec_employee_absn_summary
> group by absn_reason_code
> order by absn_reason_code;
>
> I thought I could just throw this into a PRE-QUERY (I've also
> tried it in a KEY-EXEQRY) trigger, but when I attempt to query
> the records, I get:
>
> "PRE-QUERY trigger raised unhandled exception TOO_MANY_ROWS"
>
> I assume such a thing *is* possible? Any advice would be
> greatly appreciated.
It doesn't work, of course, 'cause the SELECT returns as many rows as distinct codes. I propose you define the block as a multi-line, with ec_employee_absn_summary as the base table. The block has 2 fields: 1 is absn_reason_code as a base table field, another is total_override_crdt_loss as a non-base table field. In POST-QUERY trigger of this block, SELECT SUM(total_override_crdt_loss)
INTO <blockname>.total_override_crdt_loss FROM ec_employee_absn_summary WHERE absn_reason_code = :<blockname>.absn_reason_code;
Hope this helps.
> Thanks in advance.
>
> ---------------< LINUX: The choice of a GNU generation. >---------------
> Steve Frampton http://qlink.queensu.ca/~3srf
> Work: <frampton_at_mail.flarc.edu.on.ca> School: <3srf_at_qlink.queensu.ca>
> --< NOTICE! I do not accept unsolicited commercial e-mail messages! >--
Received on Wed Nov 06 1996 - 00:00:00 CET