Joins in sqlplus 1

[KenCunningham]

Hi,

I'm a relative newcomer to Oracle, having had most of my SQL experience using Ingres. I have the following script which would work as required using Ingres, but seems to blow up (ie retrieve far too many records) in Oracle. I reckon it's a function of how the different products handle joins, but would appreciate any advice as to how to fix it. The script is as follows:

spool date_refs
select c06_person_ref,c06_date_contact,c07_person_ref,c07_date_allo
cated,c07_date_unallocated,c09_person_ref,c09_date_input,c57
_person_ref,c57_date_due, c57_date_sent
from t06_referrals, t07_allocations, t09_memos,
t57_report_requests
where c06_person_ref = c07_person_ref
and c07_person_ref = c09_person_ref
and c09_person_ref = c57_person_ref
and c06_date_contact < '01-OCT-96'
and c07_date_allocated < '01-OCT-96'
and c07_date_unallocated < '01-OCT-96'
and c09_date_input < '01-OCT-96'
and c57_date_due < '01-OCT-96'
and c57_date_sent < '01-OCT-96'
/
spool off
/

Many thanks.

 

[sem]

Try to use distinct clause. It's a bit hard to give a better advice for you do not use aliases/table qualifiers and I do not know wich table a column belongs to. Your query may result in Cartesian product if no constraints/join conditions for some tables are provided.

 

[Kenrae]

Be careful about Date conversions. If c06_date_contact is a char field, comparison is made between chars, and if it's a date field, I don't know whether it converts date to char or char to date (since '01-OCT-96' is a char). Try using to_date('01-OCT-96').

 

[KenCunningham]

Thanks Sem. I thought it unecessary to use aliases because all of the tables and associated columns are distinguishable - ie the t09_memos table contains only columns beginning with c09, and similarly t06_referrals only contains columns beginning c06. I don't know if this will make a difference to your advice, but I'll follow up the distinct option. Thanks again.

 

[KenCunningham]

Thanks to Kenrae too. The date problem was one I raised yesterday, and have (hopefully!) resolved. Your advice is much appreciated.

 

[KenCunningham]

Sem, Kenrae, again thanks for your assistance. The distinct clause made a difference and I'mnow on the right track with this query. Cheers.