Solved

help me please - SQL syntax problem...newbie

Posted on 2003-12-08
18
1,097 Views
Last Modified: 2012-05-04
this should be a simple one guys...

I have a table for room service called guest_trans.  I have a canvas with a tabular data block (guest_trans) with four fields from that table (room_num, description, rate, trans_date)....simple enough.  It displays 5 records with a scrollbar.  I did all this with the wizard.  Now, I created another data block on the same canvas (room_num) with one text field (room_num) and one command button.

I want when you enter a room_num in the text field and hit the command...it display all the room service charges for that room number.  here is my code that is giving me fits.

--get room_service info
if :room_num.room_num is not null then
      select room_num, description, rate, trans_date
      into :guest_trans.room_num, :guest_trans.description, :guest_trans.rate, :guest_trans.trans_date
      from guest_trans
      where room_num = :room_num.room_num;      
else
      rollback;
end if;

thanks
rbg
0
Comment
Question by:rbgerman
  • 10
  • 8
18 Comments
 
LVL 2

Accepted Solution

by:
AlbertYou earned 50 total points
ID: 9901796

Hi rbgerman:

You don't need SELECT and FETCH the record by yourself.
The base table block will do that for you.

Here is the code for your reference:

1. 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;

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


Hope this can help you.

Albert.
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9901868
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
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9901943
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
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902083

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.
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902128
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
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902222

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.
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902305
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
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902363
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
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902364

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902413

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.
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902422
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...
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902445

Hi rbg:

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

Albert.
   
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902467
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
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902492
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????
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9902596


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.

0
 
LVL 1

Author Comment

by:rbgerman
ID: 9902700
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




0
 
LVL 1

Author Comment

by:rbgerman
ID: 9913830
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
0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 9917550

Thanks and good luck,

Albert.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now