pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedabout subselect
3+ messages / 2 participants
[nested] [flat]
* about subselect
@ 2005-08-12 03:38 Louise Catherine <r1c4n@yahoo.com>
2005-08-12 04:03 ` Re: about subselect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Louise Catherine @ 2005-08-12 03:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: about subselect
2005-08-12 03:38 about subselect Louise Catherine <r1c4n@yahoo.com>
@ 2005-08-12 04:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-12 04:17 ` Re: about subselect Louise Catherine <r1c4n@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-08-12 04:03 UTC (permalink / raw)
To: Louise Catherine <r1c4n@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: about subselect
2005-08-12 03:38 about subselect Louise Catherine <r1c4n@yahoo.com>
2005-08-12 04:03 ` Re: about subselect Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-12 04:17 ` Louise Catherine <r1c4n@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Louise Catherine @ 2005-08-12 04:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-12 04:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-12 03:38 about subselect Louise Catherine <r1c4n@yahoo.com>
2005-08-12 04:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-12 04:17 ` Louise Catherine <r1c4n@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox