Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

retrieve fields from multiple tables in forms builder

I need to retrieve fields from three tables to create a form with one data block and display them grouped by project number. The fields have multiple data rows for each project number. Is there any way to do this?
0
Barbara69
Asked:
Barbara69
  • 14
  • 5
  • 4
1 Solution
 
boriskalavskyCommented:
Join this tables in one select, also select project number and group by it in this select.
0
 
boriskalavskyCommented:
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#;
0
 
Barbara69Author Commented:
are you telling me to create this in forms builder itself and if so where do i enter it.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
paquicubaCommented:
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!
0
 
Barbara69Author Commented:
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.
0
 
Barbara69Author Commented:
my sql statement does work in sql*plus
0
 
Barbara69Author Commented:
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.
0
 
paquicubaCommented:
<<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 ),>>

Yes, you must type or paste the query as you were using SQL*Plus

FOR EXAMPLE:
select a.project#, COUNT(b.col2)
from a, b, c
where a.col2 = b.col2
and a.col2 = c.col2
group by a.project#
 
<< also a group by won't work unless you're using a group function, >>

Sorry for confusing you with my query, I assumed you have your own. If you want to "group by" you must use a multiple-row function such as sum() or count() as the example above.

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

Here you'll complete the FROM clause query ( YOU DON'T HAVE TO TYPE ANYTHING, BUT THE COLUMN ALIASES )

Think of this:
select PROJECT#, COUNT FROM   -- this is why is called a FROM clause query
( select a.project# PN, COUNT(b.col2) COUNT  
from a, b, c
where a.col2 = b.col2
and a.col2 = c.col2
group by a.project#)

So, in the column names fileds you'll type:
1.   PN  -- Make it the same datatype as PROJECT#  
2.  COUNT -- Make it datatype CHAR
Go back to your block and create two items using the same names above "PN" & "COUNT", OPEN the proeperty palette and use the same names for Column Name under Database, make them the same datatype assigned above, and also select Yes for "PN" as Primary Key ( right below Column Name .)

Build a FORM based on your block using the items created above and run it.

Please post your query and steps you followed to achive the FROM clause query FORM.

Good luck!

0
 
Barbara69Author Commented:
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.

0
 
paquicubaCommented:
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#

 
0
 
Barbara69Author Commented:
i did sum(work_hours) and getting ORA-00904: "WORK_HOURS": invalid identifier when i run it
0
 
Barbara69Author Commented:
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.
0
 
paquicubaCommented:
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!


0
 
boriskalavskyCommented:
Did you make Form Builder to work with joins?
0
 
Barbara69Author Commented:
i'm getting a no data found error.
0
 
boriskalavskyCommented:
Try to run query in SQLPlus.
0
 
Barbara69Author Commented:
it work fine in SQLPlus
0
 
Barbara69Author Commented:
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.
0
 
boriskalavskyCommented:
does it return any data in SQLPlus?
0
 
Barbara69Author Commented:
yes, it returns the correct output in SQLPlus.
0
 
Barbara69Author Commented:
it works fine in forms builder without SUM in the sql.
0
 
Barbara69Author Commented:
I found the problem, there are two columns that are null.

Thank you so very much for all of your assistance.
0
 
Barbara69Author Commented:
this is the way i got around the null fields, SUM(nvl(work_hours,0)) Hours
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now