Re: Forms 4.5: ORDER BY <column in detail_block>

Walter Zimmer <see_at_signature.for.email.de> wrote:

>Hi !
>
>I have a problem which has - I believe - no solution in Forms 4.5:
>
>I have two blocks, one master with computers and one detail with
>network interfaces. I need this because every computer can have
>several network interfaces. The detail table contains also the
>IP-addresses, and now the question arises:
>
>How can I query the master block, but ORDER BY according to the
>IP-address ?
>
>I cant use views, because then I can't update anyore.
>I tried WHERE id IN (<subquery>), but ORDER BY is not allowed
>in subqueries.
>So, the ORDER has to be in the master block, but how do I get
>the detail table in the SELECT part ? Can I modify the
>constructed query ?
>
>Any help would be appreciated,
>Walter
>

Hi Walter,

Assuming your master-detail-relation is something like

SQL> desc master

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER
 DESCRIPTION                     NOT NULL VARCHAR2(50)

SQL> desc detail
 Name                            Null?    Type
 ------------------------------- -------- ----
 MST_ID                          NOT NULL NUMBER
 ID                              NOT NULL NUMBER
 DESCRIPTION                     NOT NULL VARCHAR2(50)
 IP_ADR                          NOT NULL VARCHAR2(20)

you can write a stored function like

CREATE OR REPLACE FUNCTION order_val(
  P_ID IN NUMBER )
RETURN VARCHAR2 IS
  --
  --
  l_retval VARCHAR2(20);
BEGIN
  SELECT MIN(ip_adr)
  INTO
     l_retval
  FROM
     detail
  WHERE
     mst_id = p_id;
  RETURN(l_retval);
END order_val;

and put order_val(ID) as the ORDER_BY clause of the Master-Block

A better solution would be a Package where you can add PRAGMA RESTRICT_REFERENCES(order_val,WNDS,WNPS,RNPS);

HTH Thomas

--
Thomas Meier
eMail th.meier_at_usa.net