Re: [SQL*Forms 3.0] select DISTINCT possible?

From: Manh-Hoa Le <at770_at_FreeNet.Carleton.CA>
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

Original text of this message