Re: Oracle Query problem

On 23 Nov 2001 00:53:04 -0800, rolf_at_executive-decisions.co.za (Rolf) wrote:

>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'))

Please use Oracle syntax when coding for Oracle RDBMS

your statement should look like this:
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'

Regards
Andy Received on Fri Nov 23 2001 - 04:56:18 CST