Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Sql to PL/Sql sub-query
MChristy wrote:
> I have two questions based on the Sql statement below:
>
> 1 SELECT TRMJC.LERETA19589.PARCEL,TRMJC.LERETA19589.PROPID,mpropertynumber
> 2 FROM trmjc.lereta19589 LEFT JOIN
> 3 (SELECT sa.t_property.mpropertynumber,sa.t_property.mpropertyid
> 4 FROM sa.t_property
> 5 WHERE sa.t_property.mendtaxyear= 999999999
> 6 and sa.t_property.meffstatus = 'A')
>
> 7 ON trmjc.lereta19589.parcel=mpropertynumber
> 8 WHERE mpropertynumber IS NULL
>
> 1.) Why on line 7 when I preface the field mpropertynumber with the
> table owner and the table i.e. sa.t_property.mpropertynumber I receive
> the following error (Invalid Column Name)? Otherwise, if I do not do
> this the SQL statement works fine.
Because that is not the table name? (It's an in-line view, so it is no longer part of the t_property table.)
>
> 2.)How would you convert the above SQl syntax to PL/SQL syntax i.e.
> using the operator (+) to accomplish the left join.
something like this?
SELECT TRMJC.LERETA19589.PARCEL,TRMJC.LERETA19589.PROPID,mpropertynumber
FROM trmjc.lereta19589,
(SELECT sa.t_property.mpropertynumber,sa.t_property.mpropertyid
FROM sa.t_property
WHERE sa.t_property.mendtaxyear= 999999999
and sa.t_property.meffstatus = 'A') propertyview
where trmjc.lereta19589.parcel= propertyview.mpropertynumber(+)
and propertyview.mpropertynumber IS NULL;
Hmm. Why do you put the mpropertynumber in the SELECT clause and then only pick the NULL ones??
>
> I'am new to SQl and I'am currently using PL/SQL Developer to write and
> execute my code.
>
> Thanks,
>
> Mchristy
HTH.
-- Ed Prochak running http://www.faqs.org/faqs/running-faq/ netiquette http://www.psg.com/emily.html -- "Two roads diverged in a wood and I I took the one less travelled by and that has made all the difference." robert frostReceived on Mon Jun 16 2003 - 23:09:05 CDT
![]() |
![]() |