Re: Forms 4.5 block design problem

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/12/08
Message-ID: <19971208191400.OAA22109_at_ladder02.news.aol.com>#1/1

>From: "Neal Gran" <neal_at_anisoft.com>
>Subject: Forms 4.5 block design problem
>
>I have run into a design problem. I have a table of items which contain a
>field X which is not unique. I want to create a block from that table
>which has 1 entry for each of the unique values in the table. I need to
>have a field in the block which is a flag which can be true or false. Then
>when I hit a button I will execute a procedure on each record in the table
>whose value in field X matches the record in the block with the same value,
>and whose flag is set to true.
>
>I guess the problem I have is how to set up that flag field and be able to
>indicate which records I want to execute on.
>
>

Hi

Here's an example of how to do the
above. I'm sure it's not the only way
but it will work.

EXAMPLE TABLE
SQL> desc test

 Name                            Null?    Type
 ------------------------------- -------- ----
 TEST_VALUE                               NUMBER

DATA SQL> Select test_value from test;

TEST_VALUE

---

         1
         1
         1
         1
         2
         3
         3
         3
         3
         3
         3
         3
         4
        10
        10
        10
        10
        10
        10
        10
        10
        10
        10

QUERY TO ELIMINATE DUPLICATES
(the first use of the table does
 not have an alias quite deliberately.
 We want to use this SQL in a form later  and we can't use an alias in the basetable  query)

SQL> l
  1 SELECT test_value
  2 FROM test
  3 WHERE NOT EXISTS

  4     (SELECT 1
  5        FROM test a
  6       WHERE a.test_value = test.test_value
  7*        AND a.rowid < test.rowid)

SQL> / TEST_VALUE

---

         1
         2
         3
         4
        10

NOW WE WANT TO FLAG THOSE ROWS WHICH HAVE DUPLICATES. We can write a stored function to do this

(note use of datatype VARCHAR for ROWID
 forms 4.5 does NOT support ROWID as
 a datatype)

create or replace
FUNCTION test_duplicate(p_rowid VARCHAR2

                       ,p_value NUMBER)

RETURN varchar2
is
 CURSOR check_cur IS
  SELECT 'YES'
    FROM test
   WHERE test_value = p_value
     AND rowid <> p_rowid;
  Return_Value VARCHAR2(3) := 'NO';
 BEGIN
   OPEN check_cur;
    FETCH check_cur
     INTO return_value;
    CLOSE check_cur;
   RETURN return_value;
END; QUERY USING FUNCTION   1 SELECT test_value
  2 ,test_duplicate(rowid,test_value) duplicate   3 FROM test
  4 WHERE NOT EXISTS

  5     (SELECT 1
  6        FROM test a
  7       WHERE a.test_value = test.test_value
  8*        AND a.rowid > test.rowid)

SQL> / TEST_VALUE DUPLICATE
---------- ----------

         1 YES
         2 NO
         3 YES
        10 YES

BUT we can't use this directly in a form so

BLOCK TEST
 ITEM test_value NUMBER (basetable)  ITEM Duplicate_Flag CHAR(3) (non-basetable)

WHERE clause for block TEST

 NOT EXISTS
  (SELECT 1

     FROM test a
     WHERE a.test_value = test.test_value
       AND a.rowid < test.rowid)

NOW CREATE A POST QUERY TRIGGER BEGIN
  :test.duplicate_flag := test_duplicate(:test.rowid

                                        ,:test.test_value);

END; Hope this helps

Regards
John

---

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

---

Received on Mon Dec 08 1997 - 00:00:00 CET