Re: ORACLE 7.3 using ODBC and Left Outer Joins...

Michael,

Thanks for the repsonse! I did figure out what it was. The ODBC driver requires parenthases around the different parts of the Where clause for it to be processed correctly. Also I got rid of the alias of the two table names in the join. The combination of the two made the statement work.

As far as the -1 and NULL is concerned, our software ignores entries that have a -1 in them. However when running this query against a MS-Access database, it was not returning the columns that were NULL until I added the addition to the statement. STRANGE! I too thought it was redundant, but the program has to run on any ODBC compliant database. Access needs, but it's redundant for ORACLE.

Thanks again,

    -Steve

Michael Myers wrote in message <37098F9C.2DEB464E_at_nospam.blazenet.net>...
>> SELECT S.SECTIONNUM, S.UPSECTION, S.PIECE, S.SECT_TYPE, S.CONDUCTOR AS
>> SectionConductor, S.PHASE, S.MAPNAME, C.CONDUCTOR AS ConductorConductor
FROM
>> {oj SECTIONS S LEFT OUTER JOIN CONDUCTO C ON S.CONDUCTOR = C.CONDUCTOR}
>> WHERE S.SECTIONNUM <> '-1' OR S.SECTIONNUM IS Null ORDER BY S.SECTIONNUM,
>> S.PIECE
>>
>> SQLSTATE: S1000
>> Native Error Code: 1719
>> Driver Message:[Oracle]ODBC Oracle Driver][Oracle OCI][ORA-01719: outer
join
>> operator (+) not allows in operand of OR or IN.
>
>
>The relevant part of this is ORA-01719: outer join operator (+) ...
>
>A column which may be populated by an outer join cannot be used in an OR
>or IN statement. So the hangup is: WHERE S.SECTIONNUM <> '-1' OR
>S.SECTIONNUM IS Null
>
>This WHERE clause does not make much sense anyway, because 'OR
>S.SECTIONNUM IS Null' is redundant. NULL does not equal '-1'. So if you
>just say WHERE S.SECTIONNUM <> '-1', then you get the rows where
>SECTIONNUM is NULL as well.
Received on Tue Apr 06 1999 - 12:28:05 CDT