Home » Developer & Programmer » Forms » Basic forms doubt (Forms 9i, Windows XP )
Basic forms doubt [message #432180] Sun, 22 November 2009 14:58 Go to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Everyone,

I'm just starting out with oracle forms now for the first time and have a few doubts. The form that I'm working on is one all we beginners generally start with. Its a master-detail form where, when I select a department in the master part, all employees in that department should be displayed in the details part. But I'm having a few difficulties with the query. I know this is elementary but please help me out.

I have to select the department name from a table called hrp_reference which should be displayed as an LOV. This table has 2 columns called rf_type and rf_name. Here's my query for the LOV.

SELECT ALL rf_name
FROM hrp_reference
WHERE rf_type='department'

Hoping this is accurate.

Now the details part is where I'm finding the real trouble. The employee details block has four columns namely emp name,emp num, position and join date. Emp name and Emp num are taken from one table, whereas position and join date are taken from another table. So I'm not very sure how to go about writing the query for this.

I do know that 'On populate details' is the only main trigger that I'll be requiring and it has already been created. emp_name and emp_num are taken from a table called hrp_people. position and join_date are taken from a table called hrp_assignments. Please let me know on whether I'll have to link these 2 tables.

Please note that this form just displays the data on the screen. It does not enter data into any table. Its more like generating a report you could say. But I want it as a form, as in, when I select the department from the list and execute query the necessary details should be queried.

Any help will be highly appreciated.

P.S: Is it okay to attach the form and the tables here?

Regards,
Sreejith.S
Re: Basic forms doubt [message #432216 is a reply to message #432180] Mon, 23 November 2009 01:03 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you don't have to manipulate data in detail block, you might create basic master-detail form with items those two tables currently have.

In order to display additional information (such as position and join date), create those NON-database items and populate them in POST-QUERY trigger. It would be a simple SELECT statement:
select a.position, a.join_date
  into :detail.position, :detail.join_date
  from another_table a
  where a.some_column = :detail.some_other_column
    and ...


Or, you might create a VIEW (in SQL*Plus, using the CREATE VIEW statement) which would prepare all data you need in the form and then base detail block on that view.

As of attaching your form and tables, yes - it is acceptable. Some people download your code, test it and try to provide help. Nothing wrong about it.
Re: Basic forms doubt [message #432440 is a reply to message #432180] Tue, 24 November 2009 04:25 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,
I'm attaching my form here. Please go through it.It doesnt really query the way I want it to. I'm sure my join condition is wrong but I'm not sure what I should be replacing it with.

Here are the table details:

table name: hrp_per_all_people_f

columns:
employee_number full_name join date

table name2: hrp_per_all_assignments_f

columns:
department assignment_number

I'm not sure if its going to work using these two tables, but please let me know. I have another table from where we could fetch the department but I want to try it with this one first.

Thanx a lot for your earlier response.Highly appreciate it.

Regards,
Shri

  • Attachment: frm_emp.fmb
    (Size: 100.00KB, Downloaded 146 times)
Re: Basic forms doubt [message #432447 is a reply to message #432180] Tue, 24 November 2009 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your datablocks are the wrong way round. The master block should be before the detail block in the object navigator.

Your join is also wrong. Currently it is:
department.department=emp_details.employee_number


There is no way that is ever going to be true.
You need a department item in the detail block to be able to do the join.
Re: Basic forms doubt [message #432491 is a reply to message #432180] Tue, 24 November 2009 08:55 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

I apologize for my mistake. I attached the unsaved form by mistake. Currently the join condition is department.assignment_number=emp_details.employee_number

In case the form doesn't query this way using these tables, can I write a function to query the employee details based on the department name and just remove the relation all together??

Thanx for your response...

Regards,
Shri
Re: Basic forms doubt [message #432494 is a reply to message #432180] Tue, 24 November 2009 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless you've got a particularly unusual table structure I rather doubt your new join is right either. But then I don't know what assignment_number is.

Answer me this:
If you wanted to write a query in sqlplus to list all the employees for a particular department what would you write?
Re: Basic forms doubt [message #432565 is a reply to message #432180] Tue, 24 November 2009 23:30 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

Here's my query in sqlplus,

select unique department,full_name
from hrp_per_all_people_f,hrp_per_all_assignments_f
where employee_number=assignment_number
order by department

this displays all the departments and corresponding employees.

employee number and assignment number are the same. Just two different column names for two different tables.

Regards,
Shri
Re: Basic forms doubt [message #432616 is a reply to message #432180] Wed, 25 November 2009 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then your join is wrong. It specifies a single employee, you want all the employees for a given department. This is because you've got assignment_number in the master block, since it's unique to an employee you've already specified the employee to query in the master block.

I think we're missing a table here.
Can you confirm the following:

hrp_per_all_people_f lists all employees?
hrp_per_all_assignments_f links employees to departments?

Assuming I'm right is there another table that just lists the departments?


Re: Basic forms doubt [message #432630 is a reply to message #432180] Wed, 25 November 2009 05:56 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

Yes, you are right. There is another table but I have no clue how to link it with either of the above tables. And the problem with this table is that it doesn't just list the departments. Here's how the third table is.

Table name : hrp_reference

here's how the columns look like:

rf_type rf_description

holiday new year
holiday easter
.
.
.
holiday christmas
department admin
department circulation
.
.
.
department IT
.
.
.

so this is how it looks like. I really cant find any column that would link this to the either of the above tables. I have been asked to choose the department from this table.

Thanx for all your responses till now.

Regards,
Shri
Re: Basic forms doubt [message #432631 is a reply to message #432180] Wed, 25 November 2009 06:05 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

And yes,hrp_per_all_people_f lists all employees and the other table lists all departments and both tables can be linked by their employee and assignment numbers respectively. If we had to generate a report I'm sure this would query correctly.

hrp_per_all_assignments table looks like this:

columns:
department_number assignment_number

admin 001
admin 002
admin 008
.
.
IT 006
IT 017
.
.
and so on.....once again, assignment num is same as employee num

Regards,
Shri

[Updated on: Wed, 25 November 2009 06:06]

Report message to a moderator

Re: Basic forms doubt [message #432644 is a reply to message #432630] Wed, 25 November 2009 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
sreejith.s wrote on Wed, 25 November 2009 11:56
Hi cookiemonster,

Yes, you are right. There is another table but I have no clue how to link it with either of the above tables.


Well by the looks of it:
hrp_reference.rf_description = hrp_per_all_assignments.department_number

though if that's true department_number is misnamed since it doesn't actually hold numbers.

The classic departments employees setup goes something like this:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
 department_name VARCHAR2(30));
 
CREATE TABLE employees
(department_id NUMBER REFERENCES departments.department_id,
 employee_id   NUMBER PRIMARY KEY,
 employee_name VARCHAR2(30));


Then the link between them is:
departments.department_id = employees.department_id


Obviously that's not what you've got.

I think your master block needs to based on hrp_reference with a default where clause to just restrict it to deparments.
Your detail block probably needs to be based on a view that joins hrp_per_all_assignments and hrp_per_all_people and would look something like this:
CREATE VIEW employees_v AS
SELECT
ha.department_number,
ha.<other relevant columns>,
....
hp.full_name,
hp.<other relevant columns>,
....
FROM hrp_per_all_assignments ha, hrp_per_all_people hp
WHERE ha.assignment_number = hp.employee_number


Then you'd create a relationship in form of:
hrp_reference.rf_description = employees_v.department_number

Re: Basic forms doubt [message #433300 is a reply to message #432180] Tue, 01 December 2009 05:47 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

Sorry for the late response.

This query and join works absolutely fine. Thanks a lot for your help.

A minor glitch left to be resolved though. I had used the keyword unique when I earlier put in my query in sqlplus as shown above. As of now, I have multiple employee names in all departments while querying. Any suggestions on how I can apply unique to the form??

Thanks once again gor all your help. Highly appreciate it.

Regards,
Shri
Re: Basic forms doubt [message #433303 is a reply to message #433300] Tue, 01 December 2009 06:10 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Multiple employee names in all departments? What does that mean? Duplicates? If so, WHERE clause is missing *something* that would correctly join "employees" with "departments" they work in. DISTINCT does that, but - it's not the point, I guess.
Re: Basic forms doubt [message #433311 is a reply to message #432180] Tue, 01 December 2009 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Littlefoot says if you specify the link correctly you shouldn't be getting duplicates and won't need unique/distinct.
You need to describe in more detail the output you're getting along with the changes you made to the form.
I only gave a vague suggestion for what you need to do based on educated guesses of what your schema design is. Could be what I had in mind and what you did are rather different.
Re: Basic forms doubt [message #433396 is a reply to message #432180] Wed, 02 December 2009 02:17 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi All,

The form is running absolutely fine. I used unique while creating the view to avoid duplicates. Appreciate all the help that I have received here. Thanks a lot.

Regards,
Shri
Re: Basic forms doubt [message #433551 is a reply to message #432644] Wed, 02 December 2009 22:55 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

With the table structures mentioned above, is it possibleto create to separate data blocks and populate the employee details as per the department name? Though the form runs perfectly, I have been asked to try this without using views.

Regards,
Shri
Re: Basic forms doubt [message #433560 is a reply to message #433551] Thu, 03 December 2009 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
<OFFTOPIC>
What do end users see? A form which is working properly.
Do they know anything about tables or views? Doesn't really matter if they do.
Do they expect you to do THEIR job? Probably not. Why do they care about YOUR job, then?
</OFFTOPIC>
Re: Basic forms doubt [message #433588 is a reply to message #432180] Thu, 03 December 2009 02:48 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

I exactly understand what you mean. Doesn't make sense to me as well. But I need to create this form the way my boss wants it, so its not really the end user that's on my mind right now. Like I mentioned earlier, I'm a beginner.

I went through all your posts on some other topic and I think I know for a fact now that using a post query trigger that runs a cursor is a more complicated approach to coding than creating views. But then, that's exactly what I've been asked to do. Sad

Apparently, it seems that views slow down the form but then again mentioning the indexes in the query gets it running super fast. But still... So, I guess I'll have to go the cursor way now.

Regards,
Shri
Re: Basic forms doubt [message #433602 is a reply to message #432180] Thu, 03 December 2009 04:44 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's nothing particularly complicated about using cursors in post-query. If you're having issues show us what you've done and we'll sort you out.

I would say that basing the block on the view should, if done right, run faster than using post-query and cursors.
Re: Basic forms doubt [message #433619 is a reply to message #433602] Thu, 03 December 2009 05:24 Go to previous message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi cookiemonster,

I will work on the form and attach my query and the form here in case I have problems. Thanks for your help. I dint know what views were before your coaching sessions.

Regards,
Shri
Previous Topic: insert_allowed
Next Topic: How to Convert Currency value to Words
Goto Forum:
  


Current Time: Wed Dec 07 06:56:23 CST 2016

Total time taken to generate the page: 0.10168 seconds