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 frost