OraFAQ Forum: Forms » Create Form Layout using columns from different tables

Home » Developer & Programmer » Forms » Create Form Layout using columns from different tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Form Layout using columns from different tables [message #131547]

Mon, 08 August 2005 04:05

aarontan78
Messages: 63
Registered: August 2005

Member

Hi,

I'm newbie in Oracle.
I have 3 tables which have properties something like below:

Table A
item_a(Primary Key)
item_b

Table B
item_c(Primary Key)
item_d(Primary Key)
item_e

Table C
item_c(Primary Key)
item_a(Foreign Key)
item_f

I would like to have a layout that display all rows like the below query:

SELECT item_b, item_e, item_f
FROM Table A, Table B, Table C
WHERE Table A.item_a = Table C.item_a
AND Table B.item_c = Table C.item_c

Would be much appreciated if someone can provide me step by step on how to create the above layout.

Thanks!

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131653 is a reply to message #131547]

Mon, 08 August 2005 18:11

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Which table is the controlling table? (Which has more fields in its primary key?) Which tables are the subordinate tables?

For me, table B with 2 items in the primary key, one of which is not used by another table is the 'pivot' (main / controlling) table.

You have a few choices (not in order)
- create a view and base your block on it,
- base your block on table B and use Post-Query to populate the table A and table C fields, then use pre- and post- insert, update, and delete triggers to maintain the other tables, or
- base your main block on table B, then create blocks on tables A and C, and create relationships on A for B and C.

I would suggest trying the last option first.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131657 is a reply to message #131653]

Mon, 08 August 2005 19:15

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Okay ... that last solution probably won't give you the visuals that you want from your application.

Set up the block_b as your first block, block_c as your second block, and block_a as your third block. There is a relationship on block_b to block_c, and a relationship on block_c to block_a.

Don't display any of the subordinate table fields. Add non-database items to main block (block_b) for the extra fields. Use Post-Query to populate them. Create a procedure that does the following:

procedure populate_others
begin
  go_block(table_c);
-- table_c.item_c will be populated by relationship
  :table_c.item_a := :table_b.item_a; -- non-database field
  :table_c.item_f := :table_b.item_f; -- non-database field
  go_block(table_a);
-- table_a.item_a will be populated by relationship
  :table_a.item_b := :table_b.item_b; -- non-database field
  go_block(table_b);
end;

then call this procedure from a trigger that supports navigation eg when-new-record-instance. Test for change of system.record_status and call the populate_others routine when it has a value of 'INSERT' or 'CHANGED'.

On second thoughts it may be easier to use a view.

Okay ... other people ... what is your opinion. Use a view or use separate blocks??

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131664 is a reply to message #131657]

Mon, 08 August 2005 20:29

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

Thanks for the answer.
For your information, I just need to display the records on the form when I execute the query.

I followed your step till 'Add non-database items to main block (block_b) for the extra fields'....

I'm not sure how to 'Use Post-Query to populate the non-database item' and 'call the procedure from a trigger'.

Can you provide me the above steps? Again, thanks for the help.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131669 is a reply to message #131664]

Mon, 08 August 2005 20:59

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Do you have to update any of the fields?

I think you need to get a couple of books off the net. Go to http://www.oracle.com/technology/documentation/forms.html. Find 'Guidelines for Building Applications' and 'Form Builder Reference'. Download them and search using Acrobat.

Quote:

Post-Query Trigger examples
Example
This example retrieves descriptions for code fields, for display in non-database items in the current block.

DECLARE
   CURSOR lookup_payplan IS
      SELECT Payplan_Desc
        FROM Payplan
       WHERE Payplan_Id = :Employee.Payplan_Id;

   CURSOR lookup_area IS
      SELECT Area_Name
        FROM Zip_Code
       WHERE Zip = :Employee.Zip;
BEGIN
/*
** Lookup the Payment Plan Description given the
** Payplan_Id in the Employee Record just fetched.
** Use Explicit Cursor for highest efficiency.
*/
   OPEN lookup_payplan;

   FETCH lookup_payplan
    INTO :Employee.Payplan_Desc_Nondb;

   CLOSE lookup_payplan;

/*
** Lookup Area Descript given the Zipcode in
** the Employee Record just fetched. Use Explicit
** Cursor for highest efficiency.
*/
   OPEN lookup_area;

   FETCH lookup_area
    INTO :Employee.Area_Desc_Nondb;

   CLOSE lookup_area;
END;

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131670 is a reply to message #131669]

Mon, 08 August 2005 21:11

aarontan78
Messages: 63
Registered: August 2005

Member

David,

I do not need to update any of the fields.

TQ.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131671 is a reply to message #131670]

Mon, 08 August 2005 21:14

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Okay ... use one block based on table_b, then get the other details through a query in the Post-Query trigger as per example.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131675 is a reply to message #131671]

Mon, 08 August 2005 22:15

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I would like the form to display item_e (from Table B) and item_b (from Table A)
I created Table B, A, and C accordingly together with their relationships respectively. In Table B data block, I added in item_c, item_e, and item_b(non-database). I created Post_query in Table B data block as follows:

DECLARE
CURSOR lookup_table IS
SELECT item_b
FROM Table_B
WHERE item_a = :Table_C.item_a
AND :Table_C.item_c = :Table_B.item_c;

BEGIN
OPEN lookup_table;

FETCH lookup_table
INTO :Table_B.item_b;

CLOSE lookup_table;

END;

Finally, I created the layout to display item_b and item_e.
When I executed the query, the item_e records were displayed whereas item_b didn't return any records; and I got this error: 'No navigable items in destination block'.

I'm not sure which part did I left out.
Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131677 is a reply to message #131675]

Mon, 08 August 2005 22:29

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Aaron,

My first suggestions were for an updateable form, for a display only form, as I said in my last post, you only need one block.

Delete the table_a and table_c blocks, and any relationships.

Quote:

Table A
item_a(Primary Key)
item_b

Table B
item_c(Primary Key)
item_d(Primary Key)
item_e

Table C
item_c(Primary Key)
item_a(Foreign Key)
item_f

In your block on table_b create non-database fields for item_a, item_b, and item_f, and populate them in your Post-Query.

DECLARE
   CURSOR lookup_table IS
      SELECT c.item_a,
             a.item_b,
             c.item_f
        FROM Table_A a,
             Table_C c
       WHERE a.item_a = c.item_a
         AND c.item_c = :Table_B.item_c;
BEGIN
   OPEN lookup_table;

   FETCH lookup_table
    INTO :Table_B.item_a,
         :Table_B.item_b,
         :Table_B.item_f;

   CLOSE lookup_table;
END;

Hope this helps.

David

[Updated on: Mon, 08 August 2005 22:30]

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131691 is a reply to message #131677]

Tue, 09 August 2005 00:57

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

Got it!
Thanks a lot !!!

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131735 is a reply to message #131677]

Tue, 09 August 2005 04:17

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I would like to add in a user parameter (item_z) to the form.
I add-in the bolded code to the previous POST-QUERY.

DECLARE
CURSOR lookup_table IS
SELECT c.item_a,
a.item_b,
c.item_f
FROM Table_A a,
Table_C c,
Table_B b
WHERE a.item_a = c.item_a
AND c.item_c = :Table_B.item_c
AND b.item_z = :Table_B.item_z;

BEGIN
OPEN lookup_table;

FETCH lookup_table
INTO :Table_B.item_a,
:Table_B.item_b,
:Table_B.item_f;

CLOSE lookup_table;
END;

However, when I executed the form, no record is displayed for item_b and item_f.
Your help is much appreciated.
Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131867 is a reply to message #131735]

Tue, 09 August 2005 18:39

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Aaron,

Did the Post-Query work using the code I supplied?

Where did 'item_z' come from?

You only want a single row to be returned by the Post-Query cursor so you don't need to add the same table (table_b) to your query as it is implicit in the query as it is the table on which the block is based.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131874 is a reply to message #131867]

Tue, 09 August 2005 19:59

aarontan78
Messages: 63
Registered: August 2005

Member

David,

The post-query that you gave me is working, thanks!
item_z is come from Table B.

Thanks!

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131881 is a reply to message #131867]

Tue, 09 August 2005 23:10

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I created Table_B.item_z (non-database) in Table B data block to serve as user input for query. In the Table B's properties, I inserted 'item_z = :GL06MAST.item_z' in the 'WHERE' section.
Is it the correct way?

Anothing thing is that if I want to query the items in Table B as well, do I need to put in the POST-QUERY select statement as well?

OR

I just created a database item in Table B data block?

Thanks...

Regards,
Aaron

[Updated on: Tue, 09 August 2005 23:17]

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131888 is a reply to message #131881]

Tue, 09 August 2005 23:49

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

In which field of table_b do you wish to search?

Can you provide some sample data (2 or 3 lines) and then indicate on which of the field's you want the user to search.

David

[Updated on: Tue, 09 August 2005 23:50]

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131904 is a reply to message #131888]

Wed, 10 August 2005 00:32

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

Thanks for the reply.
I would like to perform the following query:

Table A
item_a (PK)
item_b

Table B
item_c(PK)
item_d(PK)
item_e

Table C
item_c(PK)
item_a(FK)
item_f

SELECT item_b, item_e, item_f
FROM Table A, Table B, Table C
WHERE Table A.item_a = Table C.item_a
AND Table B.item_c = Table C.item_c
AND Table B.item_z = user input

in which, the user input item_z to make the query.

Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131917 is a reply to message #131904]

Wed, 10 August 2005 01:35

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You are missing the point. On which of the six fields on the screen do you wish to search? You have fields c, d, and e from table_b, plus, through the Post-Query, fields a, b, and f.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131922 is a reply to message #131917]

Wed, 10 August 2005 01:44

aarontan78
Messages: 63
Registered: August 2005

Member

David,

I want to display all the records from field b, e and f for a particular year (Table B.item_z)- in which I would like the user to key in the year (eg 2005) and click query to display records from field b, e, and f which is in year 2005 (eg).

I hope my explaination is not confusing.... Smile

Smile

Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131929 is a reply to message #131922]

Wed, 10 August 2005 02:04

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You don't search for your main block fields in your Post-Query.

Date ranges need to be handled differently because they are not a straight search.

If item_z is your date then you need to 'massage' your date to match the structure of your search criteria.

Create a non-database field on your table_b block, let's call it z_year. We populate it in the Post-Query by, now, adding table_b to the cursor.

DECLARE
   CURSOR lookup_table IS
      SELECT c.item_a,
             a.item_b,
             c.item_f,
             to_char(c.item_z,'YYYY')
        FROM Table_A a,
             Table_C c,
             Tabel_B b
       WHERE a.item_a = c.item_a
         AND c.item_c = c.item_c
         AND b.item_c = :Table_B.item_c
         AND b.item_d = :Table_B.item_d;
BEGIN
   OPEN lookup_table;

   FETCH lookup_table
    INTO :Table_B.item_a,
         :Table_B.item_b,
         :Table_B.item_f,
         :Table_B.z_year;

   CLOSE lookup_table;
END;

When you reference your base table in a Post-Query make sure that you fully utilize its primary key.

We then have to change the 'default_where' depending on whether we are searching by date range or not.
Pre-Query trigger

declare
   stmnt   varchar2 (200);
begin
   if :Table_B.z_year is not null then
      stmnt := 'to_date(item_z,''YYYY'') = :Table_B.z_year';
   end if;

   set_block_property ('Table_B', DEFAULT_WHERE, stmnt);
end;

Try that and see what happens.

David

[Updated on: Wed, 10 August 2005 02:05]

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #131964 is a reply to message #131929]

Wed, 10 August 2005 03:17

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I tried the above method and created the PRE-QUERY, however, I received this message when I execute the form:

FRM-40735:POST-QUERY trigger raised unhandled exception ORA-01481.

I tried to change to_char to to_number, AND/OR change the z_year's data type to char/number but still receive the same message.

Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132103 is a reply to message #131964]

Wed, 10 August 2005 18:33

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Please post the code you have in your Post-Query trigger.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132115 is a reply to message #132103]

Wed, 10 August 2005 19:49

aarontan78
Messages: 63
Registered: August 2005

Member

David,

Post-Query:

DECLARE
CURSOR lookup_table IS
SELECT c.item_a,
a.item_b,
c.item_f,
to_char(b.item_z,'YYYY')
FROM Table_A a,
Table_C c,
Tabel_B b
WHERE a.item_a = c.item_a
AND c.item_c = c.item_c
AND b.item_d = :Table_B.item_d;
BEGIN
OPEN lookup_table;

FETCH lookup_table
INTO :Table_B.item_a,
:Table_B.item_b,
:Table_B.item_f,
:Table_B.z_year;
CLOSE lookup_table;
END;

Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132118 is a reply to message #132115]

Wed, 10 August 2005 20:33

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

What's the data type for table_b.item_z? Please confirm that the two item_a fields and two item_c fields have the same data type.

David

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132120 is a reply to message #132118]

Wed, 10 August 2005 20:41

aarontan78
Messages: 63
Registered: August 2005

Member

David,

The data type for table_b.item_z is number.
I got the correct output if I don't insert:

to_char(c.item_z,'YYYY')
and
:Table_B.z_year;

into POST-QUERY

Thanks.

Regards,
Aaron

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132121 is a reply to message #132120]

Wed, 10 August 2005 20:53

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I made am incorrect assumption. I assumed the 'year' in item_z was a date type field. As item_z is a four digit number then you do not need to mention table_b in the Post-Query and you do not need the Pre-Query trigger. Just add item_z as a number to your block_b, define it on the canvas and the query will accept search values and then display it as part of the 'base' table on retrieval.

David

PS keep copies of your form before doing major deletions, just in case you want to go back and try a different approach

[Updated on: Wed, 10 August 2005 20:54]

Report message to a moderator

Re: Create Form Layout using columns from different tables [message #132362 is a reply to message #132121]

Fri, 12 August 2005 03:22

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I got the intended output.
Thanks a lot.

Regards,
Aaron

Report message to a moderator

Maintain user input's visibility [message #132615 is a reply to message #132121]

Mon, 15 August 2005 04:13

aarontan78
Messages: 63
Registered: August 2005

Member

Hi David,

I created a form which allowed user to enter input (eg. 2005) in a field to perform query.
I would like the user input (2005) to remain visible in the field after I execute query.
May I know how should I do that?
Thank you.

Regards,
Aaron

Report message to a moderator

Previous Topic:	how to make this type of tlist/poplist???
Next Topic:	Special and Pair Valuesets ...need help!!!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 00:24:23 CDT 2024