Re: Stored Procedures

Well, in oracle to select from 2 or more tables you have to use a join. e.g.

say you've got 2 table, emp and salary
you want to select all the columns from emp and salary where the salary_id on the employee table matches the salary_id on the salary table. i.e.

Select *
from emp, salary
where emp.salary_id = salary.salary_id

so let's use example

select *
 from test_table1, test_table2
where test_table1.Id = test_table2.id
and test_table1.id = test_parameter

Try not to use '*'. Try specifying the column names that you need. This will avoid conflicts when you have duplicate column names in your tables i.e.

select test_table1.column1, test_table2.dummy_column  from test_table1, test_table2
where test_table1.Id = test_table2.id
and test_table1.id = test_parameter

"Mike Watson" <contact_at_mikewatson.co.uk> wrote in message news:781ddb68.0108200221.7bd8ab03_at_posting.google.com...
> Hi,
>
>
> Could anyone please give me a hand with connecting to Oracle stored
> procedures from Crystal Reports.
>
>
> Basically, I have managed connecting to a simple stored procedure that
reads
> the data from one single table using a parameter. But how can one connect
to
> a stored procedure that uses more than one table?
>
>
> This is a sample I have found that works fine for connecting to one single
> table.
>
>
>
> # Creating the package
>
>
> CREATE OR REPLACE PACKAGE Test_Package
> AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE
> END Test_Package;
> /
>
>
>
>
> # Creating the stored procedure
>
>
> CREATE OR REPLACE PROCEDURE Test_Procedure (
> Test_Cursor IN OUT Test_Package.Test_Type,
> Test_Parameter IN Test_Table.ID%TYPE)
> AS
> BEGIN
> OPEN TEST_Cursor FOR
> SELECT *
> FROM Test_Table
> WHERE Test_Table.ID = Test_Parameter;
> END Test_Procedure;
> /
>
>
> However, I can't say I have fully understood the above code but it
performs
> without any problems. Can anyone shed some light on using Crystal Reports
> with an Oracle stored procedure reading of more than one table please?
>
>
> I am on Oracle 8.1.7, Crystal Reports 8.5 developer, use ODBC with
Crystal's
> driver (CR Oracle8 v3.6), running Win 2000.
>
> Can all posts to this go via the newsgroups rather than my email address
please.
>
> Many thanks!
> Mike
Received on Mon Aug 20 2001 - 16:22:13 CDT