retrieve fields from multiple tables in forms builder

Join this tables in one select, also select project number and group by it in this select.

select a.filed1, b.field2, c.filed3, c.proj#
from table1 a,
table2 b,
table3 c
where a.keyfiled1 = b.keyfiled2
and c.keyfiled3 = b.keyfiled4
group by  c.proj#;

are you telling me to create this in forms builder itself and if so where do i enter it.

Select Data Block
Click Create button
When prompted select "build new data block manually"
Rename the new data block
Right click to access the Property Palette
Under "Query Data Source Type" select "FROM clause query"
Under "Query Data Source Name" enter your query
   for example: select a.col1, b.col2, c.col3 from a, b, c where a.col2 = b.col2 and a.col2 = c.col2 group by ....
Under "Query Data Source Columns" type the name of the columns you're selecting and specify the datatype and constraints
   
    for example: select col1, col2, col3 from( select a.col1, b.col2, c.col3 from a, b, c where a.col2 = b.col2 and a.col2 = c.col2 group by ....)
 
    You'll type col1, col2 ,col3

Under your Data Block, create the three items col1, col2, col3 ( you can use aliases )
Go to the Property Palette of the items and update "Column name"  specifying the column name in the database  ( make sure one of the column is marked as Primary Key )
Create your form based on your new block and you should be good to go.

Hope this helps!

in your example query under "Query Data Source Name"select a.col1, b.col2, c.col3 from a, b, c where a.col2 = b.col2 and a.col2 = c.col2 group by ....
are these actual database column names (i did ),
also a group by won't work unless you're using a group function,
then Under "Query Data Source Columns" i typed the name of the columns(from the select statement) and specified the datatype and constraints,
i created the items and the database column names for the items in the property palette.
I then created the form ran it and getting frm-40505 error and when i checked the error it has ORA-00911: invalid character but i can't find an invalid character.

my sql statement does work in sql*plus

I decided to just create a view and create the form based on it, but i would still like to know how to do what you previously advised me to as a learning experience.

I did everything you suggested, ran it and got an error: unable to perform query and then when i checked the error it's showing  ORA-00911: invalid character but i can't find one. i ran the query in sql*plus and it works fine.

aside from that my other issue is:

i can't group by the project# because one of the columns i need from another table has multiple entries per project#. what i'm trying to do is list the different projects(6) and calculate the total hours worked (16 entries) on each project. the calculation part is giving me trouble.

You are pasting or typing a special character:
ORA-00911: invalid character
Cause: You tried to tried to execute an SQL statement that included a special character.  
Action: The options to resolve this Oracle error are:
This error occurs when you try to use a special character in an SQL statement.  If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations.
This error may occur if you've pasted your SQL into your editor from another program.  Sometimes there are non-printable characters that may be present.  In this case, you should try retyping your SQL statement and then re-execute it.
This error occurs when a special character is used in a WHERE clause and the value is not enclosed in single quotations.
For example, if you had the following SQL statement:

SELECT * FROM suppliers
WHERE supplier_name = ?;

You would receive the following error message:

ORA-00911: invalid character

You could correct this error by enclosing the ? in single quotations as follows:.

SELECT * FROM suppliers
WHERE supplier_name = '?';


Please post the query you're either typing or pasting in "Query Data Source Name" ( I forgot to mention that your query cannot contain the semicolon ( ; ) at the end. If it's the case, remove it.)

<< can't group by the project# because one of the columns i need from another table has multiple entries per project#. what i'm trying to do is list the different projects(6) and calculate the total hours worked (16 entries) on each project. the calculation part is giving me trouble. >>

I don't see the problem here

Select a.project#, sum(b.hours_worked)
from tab1 a, tab2 b, tab3 c
where a.project# = b.project#
and c.colx = a.colx
and c.coly = '?'
group by a.project#

 

i did sum(work_hours) and getting ORA-00904: "WORK_HOURS": invalid identifier when i run it

to get it to work i followed your instructions to the t. it works fine until i try using the sum function, is it possible you can't do group functions under Query Data Source Name.

You can use the sum function in Query Data Source Name, all you have to do is assign an alias to it:


Select a.project# AS PN, sum(b.WORK_HOURS) AS TOTAL
from tab1 a, tab2 b, tab3 c
where a.project# = b.project#
and c.colx = a.colx
and c.coly = '?'
group by a.project#

Then, you'll use PN "same datatype as project#" and TOTAL "varchar2 or char" as your column names under Query Data Source Columns ( do not use project# and work_hours ), so Form Builder -not you - will create the following query:

Select PN, TOTAL from(
Select a.project# AS PN, sum(b.WORK_HOURS) AS TOTAL
from tab1 a, tab2 b, tab3 c
where a.project# = b.project#
and c.colx = a.colx
and c.coly = '?'
group by a.project#)

Then, use PN & TOTAL as the identifiers to create the two items under your block and specify the datatypes exactly as I told you above. ( Make sure PN is selected as Primary Key )

Let's try again!

Did you make Form Builder to work with joins?

i'm getting a no data found error.

Try to run query in SQLPlus.

it work fine in SQLPlus

i don't know what you're referrig to when you asked me if I made Form Builder to work with joins? i'm just learning.

does it return any data in SQLPlus?

yes, it returns the correct output in SQLPlus.

it works fine in forms builder without SUM in the sql.

I found the problem, there are two columns that are null.

Thank you so very much for all of your assistance.

this is the way i got around the null fields, SUM(nvl(work_hours,0)) Hours