Re: General SQL statement question

On 16 May 2005 07:37:21 -0700, dutilr_at_netscape.net wrote:

>You'll have to excuse me, but I'm a little light on my SQL skills. Can
>anyone tell me if the following SQL query should work or not? It has 4
>files, but the first file isn't linked to any of the other three files
>in the WHERE clause. I don't get it. Am I just missing something?
>Please let me know if you have any thoughts or suggestions.
>
>insert into cs02p10w
>SELECT DISTINCT a.csnwpl,d.opcatn,d.oppric
>FROM cs01p10w a, opprltp d,mspmj200 e, cs03p100 g
>WHERE d.opplst=&PL
> and d.opcatn=e.prdno
> and integer('0'||e.uprgrp)=g.cspgrp
> and d.oppric<>0
> and (substr(e.ubusut,1,1) between 'A' and 'H' or e.ubusut like 'M%' )
> and e.ubusut||e.ufamly<>'M O ' with NC

It might work, but the performance will be abysmal. Without the distinct you will be getting a cartesian product of a, and the join of the other three tables. If you have two sets, and the number of elements of the first set is a and the number of the second set is b, the cartesian product will consist of a x b elements. The distinct is just a trick to make the result set unique again. Apart from that, AFAIK, there is no integer function in Oracle, and even if there would be, it would preclude the use of any index on that column.
In short: this statement is horrible.

--
Sybrand Bakker, Senior Oracle DBA