Web Development

Ask something
Question Locked

LEFT JOIN in Oracle Forms

0 helpful
9 replies

by john.a.wills

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Using PL/SQL in a trigger I construct a data query source like this: x LEFT JOIN y ON x.pidm = y.pidm AND y.aidy = '0809'. Then I execute the query in the block with a WHERE on x.pidm. It works fine when there is something in y that fits, but yields nothing at all when there is no y with that pidm, even though there is something in x with that pidm. Forms seems to be treating LEFT JOIN as INNER JOIN, although I know by Message in the trigger code that I have constructed LEFT JOIN.

9 replies

by tony hopkinson

OUTER JOIN

Left or RIGHT sets which table will return nulls if there's no match.

INNER is the default.

by john.a.wills

I want the y to be null if no y.pidm matches the x.pidm. However, just to cover the bases, I have tried OUTER JOIN and still get a result as for INNER JOIN.

by tony hopkinson

If you want nulls the join has to be outer.

Check your data to make sure you have some x.pidm with no y.pidm.

Unless something weird is going on.

Select ? From X Join Y on (x.id = Y.ID)
Right outer join will give you all records in Y with nulls for X where there's no match

Left Outer Join, would give all records in X with nulls for Y where there's no match.

by john.a.wills

and I have the query working as I want in SQL*PLUS, but in Forms it does not work, the LEFT [OUTER] acts as an INNER. There must be a way around this error - and I am pretty sure the error is in Oracle Forms - and I would like to know what. I have been thinking about constructing an OUTER JOIN and an elaborate WHERE clause, both to be set in SQL/PL in a trigger.

by tony hopkinson

The only thing I know aboyut Oracle forms, is to avoid it at all costs, I'm afraid.

I find it hard to believe that they could make as basic an error as that though.

How about an inner join unioned with a where not exists query as a stop gap?

Another thing to try would be to start with a minimum of constraints and see if it's still not working may be the join is working but some other clause is cutting out the data you want.

PS you have verified that the data you are expecting exists. I know it sounds silly but we've all done it when lost amongst the trees.

by john.a.wills

Each SELECT within the UNION would need its own WHERE clause, and Oracle Forms provides only one WHERE clause per data block.
I have checked the data by putting the same query in SQL*PLUS, and the data are there.
I am thinking of having a large outer join (8 tables or more) and a very large WHERE clause to restrict, but that would be very unreadable and, I think, very inefficient. Still, correctness comes before efficiency. There again, the underlying problem might come back.

by john.a.wills

Some of the join-on variables had "copy from" entries in their property pallettes so Forms was adding qualifications to the WHERE clause. I suppose this is in a sense my fault, but I do think there might have been more help from Forms.

This discussion is closed to new replies.