Oracle Query problem

From: Rolf <rolf_at_executive-decisions.co.za>
Date: 23 Nov 2001 00:53:04 -0800
Message-ID: <890aa0e4.0111230053.4cae50e7@posting.google.com>

We a writing some software that need to use some form of generic query that works in both Oracle and SQL. In our software the following query gets generated:

SELECT

	table1.persal AS Parcel_Number, 
	table1.surname AS Surname, 
	table1.initials AS Initials, 
	table2.description AS Race, 
	table3.eng_long_desc AS Ethnic_Group, 
	table4.eng_long_desc AS Home_Language 
FROM 	
	table1, table2, table3, table4
WHERE 
	table1.race =* table2.code AND 
	table1.ethnic_group =* table3.code AND 
	table1.home_lang =* table4.code AND 
	table1.initials = 'd'

This query works fine with some parsing in SQL but I get no way to make this work in oracle even if I try to rewrite it. The number of rows returned vary incredible. SQL parses the query as follows:

SELECT gen_details.persal AS Persal_Number, gen_details.surname AS Surname, gen_details.initials AS Initials, race.description AS Race,

                 dec_019.eng_long_desc AS Ethnic_Group,

dec_050.eng_long_desc AS Home_Language

FROM        gen_details RIGHT OUTER JOIN
                 race ON gen_details.race = race.code RIGHT OUTER JOIN
                 dec_019 ON gen_details.ethnic_group = dec_019.code
RIGHT OUTER JOIN
                 dec_050 ON gen_details.home_lang = dec_050.code

WHERE      (gen_details.initials = 'd')

or:

SELECT

	gen_details.persal AS Persal_Number, 
	gen_details.surname AS Surname, 
	gen_details.initials AS Initials, 
	race.description AS Race, 
	dec_019.eng_long_desc AS Ethnic_Group, 
	dec_050.eng_long_desc AS Home_Language
FROM 
	race
	INNER JOIN gen_details ON gen_details.race = race.code
	INNER JOIN dec_019 ON dec_019.code = gen_details.ethnic_group
	INNER JOIN dec_050 ON dec_050.code = gen_details.home_lang
WHERE 
	((gen_details.initials = 'd'))

Received on Fri Nov 23 2001 - 02:53:04 CST