help me please - SQL syntax problem...newbie

hey albert ----

ok...let me more in depth because my problem is a little bigger than what i let on.  I have a canvas with guest info on it.  You hit get guest bill and it takes info from that canvas and places it in my guest_bill data block by

--insert values in fields
select fname, lname, checkin_num, room_num
into :checkin1.fname, :checkin1.lname, :guest_bill.checkin_num, :guest_bill.room_num
from checkin
where checkin_num = :checkin.checkin_num;

that works.

my problem is I am trying to create a master-detail page on the guest_bill canvas.  I have a tabular data block called guest_trans1 that should display the room service items for that particular room.

I created my tabular fields manually.  When I go into the Data Block Wizard for my new data block (guest_trans1) on the guest_bill canvas, I go to master/detail tab.  Then, I hit create realtionship with the Auto-join still checked.  My data block is not there???  I uncheck the Auto_Join field and click create relationship.  I tried both by REF and by join and my Data Block (guest_bill) is not there....why is that?  I know I have a data block called that??

rbg

now the data block is there it is just not working.  

guest_bill (master block) has two relations:

GUEST_BILL_CHECKIN ->
Detail Data Block: Checkin1
Join Condition: CHECKIN1.CHECKIN_NUM = GUEST_BILL.CHECKIN_NUM

and

GUEST_BILL_GUEST_TRANS2 ->
Detail Data Block: Guest_trans2
Join Condition: GUEST_TRANS2.ROOM_NUM = GUEST_BILL.ROOM_NUM

i know that the info for checkin1 loads because a command button sends it over by means of:

--insert values in fields
select fname, lname, checkin_num, room_num
into :checkin1.fname, :checkin1.lname, :guest_bill.checkin_num, :guest_bill.room_num
from checkin
where checkin_num = :checkin.checkin_num;

this loads everything i need loaded except the tabular room service fields.  once the above code is executed..the fields are populated (including the room_num field).  This room_num fields should populate the guest_trans2 for that fields.

help me out please...i will raise the points

rbg

Hi  rbgerman:

1. For your first question: My data block is not there ?
You have to create a foreign key constraint between the master and the detail table,
or else the Oracle FormsBuilder can not figure out the join column(s) for that relationship.
In case you do not have foreign constraints there, you can still create the relation objects manually.

2. I'm still wondering why do you SELECT the data by yourself.
Just create base table blocks, and then create relation objects between the MASTER and the DETAIL blocks.
The Oracle Forms build-in functions will do everything for you.

Albert.

i am trying to get it to do that.  it through the persons name in the required fields along with their room number all onto the master_block (guest_bill).  Then I have a detail block (guest_trans2) that is tabular and should fill with the info that is acquired from the room_num.  It is not populating????

How can I make sure my master block is correct?  

cluesless here...this is my only problem

rbg

Hi  rbgerman:

I can not understand what you said "How can I make sure my master block is correct ?".
Can you explain it better ?

Albert.

in my SQL -> in my guest_bill table, i do not reference room_num as a foreign key...i did not think that was necessary but maybe it is to make the master-detail form work correctly.  Let me give it to you one more time.

Master form: guest_bill...all fields are filled automatically when you go to this fields.  they are gathered from another form.  the key field that is filled is room_num.  Now, my detail block (guest_trans2) includes room_num, description, rate, and trans_date.  it should fill all transaction for that room_num automatically, right?  It is not an auto-join because the guest_bill data block was not available...whyis that?

rbg

hey albert,

you said earlier:

2. Set the "WHERE Clause" property of the block GUEST_TRANS -
room_num = :room_num.room_num

i went into my DATA BLOCK wizard to do that and realized I had used a table....how do I change it to alter the SQL????  i warned you i was a newbie

that might do the trick

rbg

Hi rbg:

As what I have mentioned above, you can create a relation object manually in case you have no foreign key constraints between the master and the detail tables.
Given that you have created your base table blocks GUEST_BILL and GUEST_TRANS2 as the master and the detail block,
follow the steps to create a relation object manually.

1. In FormsBuilder, open your form.
2. Open the node "Data Blocks"
3. Open the Block "GUEST_BILL".
4. Click on the node "Relations"
5. Click on the Create button of the toolbar on the left hand side of the BormsBuilder.
    The relation object dialog window appears.
6. ENTER or SELECT the detail block name GUEST_TRANS2.
7. Enter the join condition of the two blocks.
8. In most cases, you can optionally CHECK the "Prevent masterless operation" property,
    Which will prevent the users to do any actions on the detail block in case that there is no record in the master block.
9. Click on the OK button, then the relation object will be created.

After you have created the relation object, the Master-Detail operations will run well.
Just query on the master block and all its detail blocks will be populated automatically according to their join conditions.

Albert.

Hi rbg:

I suggest you to do the query operations by the forms default way.
The relation object will set the Where clause in the detail block according to its join key field(s)
and then the detail block will be populated automatically.
You do not need the command button and/or the SELECT statements any more.

When running the form, just navigate to the master block and press the default function key "Execute-Query"
( "Ctrl"+"F11" in a 3-tired environment  or "F8" in a client/server environment)


Albert.

hi albert,

i am gonna increase the points to 100.  i did what you said exceptit gives me an error:

FRM-30416: Relation's master block is a control block.
Relation: ROOM_NUM_GUEST_TRANS
Block: ROOM_NUM
Form: HELP_FORM
FRM-30085: Unable to adjust form for output.

rbg...

Hi rbg:

Can you list all your blocks in the form and explain what you want they to show for you ?

Albert.
   

ok....what i have done to reduce confusion is start a new project with one canvas and 2 data blocks.  the master block (room_num) has a text field and a command button.  the detail block(guest_trans) is the tabular fields with room_num, description, rate, trans_date.  I enter a room number, hit the command, the details fill the tabular field.  I followed your steps but I am getting the error above.  i added a realtionship on the room_num block (master).  then joined it by :

guest_trans.room_num = room_num.room_num

then the error

rbg

i may have something here:

i am redoing it again...by the book...

when i go to create relationship..it says no master data block available.  now, is this a problem.  my table in my PL/SQL is created like:

CREATE TABLE guest_trans
(trans_id NUMBER (6) PRIMARY KEY,
charge_id NUMBER (3) REFERENCES serv_chrg(charge_id),
trans_date DATE NOT NULL,
rate NUMBER(5,2),
description VARCHAR2(20),
room_num NUMBER (3) REFERENCES room_type(room_num),
emp_num NUMBER (4) REFERENCES employee(emp_num));

why would ther not be a master block available????

OK, let's get start with your new project.

In this case, you have two options to go on,
and in either case, there is no need of relation object

(1) Use a control block and a database data block.
1. A control block is a block which is created based on no database tables.
      Your ROOM_NUM block is acting as a control block.
2. A database data block is created based on a database table(or view).
    All the corresponding table operations(INSERT/UPDATE/DELETE/LOCK) are handled automatically by the forms itself.
    Your GUEST_TRANS block is acting as a database data block.
    Please check the "Database Data Block","Query Data Source Name" block properties to confirm it.
3. Set the where clause of the GUEST_TRANS block as I have mentioned above:

 In the WHEN-BUTTON-PRESSED Trigger of the command button -

if :room_num.room_num is NULL then
      return;
end if;
go_block('guest_trans');
clear_block(NO_VALIDATE);
execute_query;

Set the "WHERE Clause" property of the block GUEST_TRANS -
room_num = :room_num.room_num

(2) Use a database data block.

In this case, you need only a database data block created by the Data Block Wizard.
To query data records, just press the ENTER-QUERY function key , enter your search condition, and then press the EXECUTE-QUERY function key.

Albert.

i got it working with a master-detail on the small test project.  i really want it to work on the other project b/c I do not want to redo my entire form which is huge.  

i am still not getting the other to work:

this is what i did:
on room_num data block:
changed database data block to No

on guest_trans block:
had database data block to Yes
Enforce Primary Key: No
Query Allowed: Yes
Query Data Source Type: Table
Query Data Source Name: Guest_Trans
Include Ref Item: No
Where clause: room_num = :room_num.room_num

ran it...typed in 106...hit the command button...nothing

????

rbg

hey albert,

thanks for taking the time to try and help.  I am still not getting it to work.  I was reading in my book that I did not need a mater-detail layout.  I awarded the points, but I am going to start on new post on the matter.

thanks

rbg

Thanks and good luck,

Albert.