Обсуждение: about subselect

Hi,
I make a subquery test at postgresql
7.3.3 and postgresql 8.0.3.
I found that postgresql 8.0.3 can't do this,
while at postgresql 7.0.3 it works quite well :
create table temp_hasil2(
nojob numeric,
nik numeric,
tanggal numeric,
kdpt numeric,
kdljr numeric
);

create table ap012(
nojob numeric,
tglavd numeric,
ptcd numeric
);

create table am382(
nojob numeric,
nik numeric,
tglavd numeric,
lkrjacd numeric
);

Update Temp_hasil2 Set KdPT = A.PTCD,
KdLjr = A.LKRJACD
From (Select A38.NIK,A01.ptcd,A38.Lkrjacd     From AM382 A38          inner join          ap012 A01 on A38.nojob =
A01.nojob    Where A38.tglavd =           (select max(B38.tglavd) from am382 B38           where A38.nik = B38.nik
    and B38.tglavd <= Temp_hasil2.Tanggal)           And A01.tglavd =           (select max(B01.tglavd) from ap012 B01
        where A01.nojob = B01.nojob           and B01.tglavd <= A38.tglavd)    )A
 
Where Temp_hasil2.NIK = A.NIK;

If I run this query at Postgresql 8.0.3 :
Error: ERROR: subquery in FROM may
not refer to other relations of same query level.

Can someone help me solve this?,
because I've a plan to migrate database
from postgresql 7.3.3 to postgresql 8.0.3,
and I have a lot of case like one above
at my postgresql 7.3.3 databases.

Thanks,
Louise Catherine
NB: Sorry for my poor English

    
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 

Louise Catherine <r1c4n@yahoo.com> writes:
> I make a subquery test at postgresql
> 7.3.3 and postgresql 8.0.3.
> I found that postgresql 8.0.3 can't do this,
> while at postgresql 7.0.3 it works quite well :

7.3 says this:
NOTICE:  Adding missing FROM-clause entry in subquery for table "temp_hasil2"

which means that the query is invalid according to the SQL spec.  Later
versions are just being tougher about enforcing the spec.  If you are
happy with 7.3's interpretation of what the query means, try adding

> Update Temp_hasil2 Set KdPT = A.PTCD,
> KdLjr = A.LKRJACD
> From (Select A38.NIK,A01.ptcd,A38.Lkrjacd
>       From AM382 A38
>            inner join
>            ap012 A01 on A38.nojob = A01.nojob
>       Where A38.tglavd =
>             (select max(B38.tglavd) from am382 B38, Temp_hasil2
^^^^^^^^^^^^^
>             where A38.nik = B38.nik
>             and B38.tglavd <= Temp_hasil2.Tanggal)
>             And A01.tglavd =
>             (select max(B01.tglavd) from ap012 B01
>             where A01.nojob = B01.nojob
>             and B01.tglavd <= A38.tglavd)
>      )A
> Where Temp_hasil2.NIK = A.NIK;
        regards, tom lane

I guess there's no other way,except adding the table
name one by one.

Thank you very much,
Louise Catherine.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com