Re: Sql to PL/Sql sub-query

Ed Prochak <edprochak_at_adelphia.net> wrote in message news:<3EEE98CA.9020705_at_adelphia.net>...
> 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

Hi Ed,

Thanks for the quick reply you were a big help. Your PL/SQL statement executed flawlessly. I rewrote the same query in PL/Sql as you did prior to my e-mail but could not get it to work. The difference was you have a comma after the FROM clause. Why is it needed there?  I would not have thought to put a comma there. Usually there is not a comma after the FROM clause unless you have more than one table, unless its because of the nested select statement (sub-query). Am I right? Also, what is an in-line view? I don't understand ("It's an in-line view, so it is no longer part of the t_property table"). mpropertnumber is a field in the t_Property table and sa is the owner.

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;

Sorry for such basic questions. Also by the way I don't need the mpropertynumber in the SELECT clause. Error on my part you were right. Again your help was much appreciated.

Mike.... Received on Wed Jun 18 2003 - 10:32:57 CDT