Home » Developer & Programmer » Forms » Urgent->Form Datablock thru How to query the records from different tables? (merged)
Urgent->Form Datablock thru How to query the records from different tables? (merged) [message #188517] Sat, 19 August 2006 00:26 Go to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Hi All,

How to fetch the data from diffrent database tables?

Below i gave the Sample Structure
Database 1 -> X Database 2-> Y
Table - A Table - B

I need to fetch the records from both A and B tables.
How to link the Database 1(X) and Database 2(Y)


thanks in advance
Amul
Urgent->Form Datablock thru How to query the records from different tables? [message #188520 is a reply to message #188517] Sat, 19 August 2006 00:36 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Hi All,

Urgent->Form Datablock thru How to query the records from different tables?Below i've mentioned the structure

Base Block Employee_tab
Another tables are depart,country,currency

i would like to show the all fields from Employee and take
one field from department,country and currency

emp_code 101
emp_name rrr
emp_salary 10000
emp_Dept Finance
Emp_cntry India
Currency Rupeee

Like that i've to show in form.
Is there possible to inherit the others tables columns with base employeee blocks?

i'm going to use post-query.Before that i would like to
know any wizard based facility is there or not?

i dont need master-details relations...


pls anybody know the solution,let me know immediately.....


Thanks n advance
RS




Re: Urgent ->How to fetch the data from diffrent database tables? [message #188534 is a reply to message #188517] Sat, 19 August 2006 02:49 Go to previous messageGo to next message
rana.irfan@arzootex.com
Messages: 15
Registered: June 2006
Location: Faisalabad.
Junior Member

Dear,
you create database link like that

create database link dbacc.us.oracle.com
connect to arzooacc identified by acc
using '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.205)
(PORT = 1521)))(CONNECT_DATA = (SID = dbacc)))'

Note :-
HOST Ur Server IP
SID Ur Database SID

and then you select table like

Select a.z,b.y
From Table_Name1@dbacc.us.oracle.com a ,Table_Name2@dbacc.us.oracle.com b
where a.z=b.z

I think this solve Your problem


Regards,

Rana Irfan
+923006689650
Re: Urgent->Form Datablock thru How to query the records from different tables? [message #188854 is a reply to message #188520] Tue, 22 August 2006 02:39 Go to previous messageGo to next message
jowahl
Messages: 82
Registered: May 2006
Member
there is no wizard to do this, you have to set this manually:

1) create non-databased items for the fields you only want to display in POST-QUERY.

2) create a POST-QUERY trigger on block level with a code like this:
BEGIN
    -- get department:
    BEGIN
        SELECT <field_name>
        FROM   <department_table>
        INTO   :<FORMS_BLOCKNAME>.<FORMS_ITEMNAME>
        WHERE  id = <your_conditions>;
    -- optionally you may define a exception to get no errors
    -- if no department is found:
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
             -- do nothing:
             NULL;
    END;
    -- get country:
    BEGIN
        SELECT <field_name>
        FROM   <country_table>
        INTO   :<FORMS_BLOCKNAME>.<FORMS_ITEMNAME>
        WHERE  id = <your_conditions>;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
             -- do nothing:
             NULL;
    END;
    -- get other data: 
    -- ...
    -- ...
    -- ...
END;


hope this helps ...

Re: Urgent ->How to fetch the data from diffrent database tables? [message #191568 is a reply to message #188517] Thu, 07 September 2006 02:39 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Use the Post-Query trigger to populate non-database fields.

David
Previous Topic: wizard based facility available in fetching the records from more than one table with Base dataBlock
Next Topic: How to Run forms in Explorer
Goto Forum:
  


Current Time: Fri Sep 20 12:46:17 CDT 2024