Invalid column name in Forms 6i

[Avaatar]

Hello all,
i am a beginner in Oracle PL/SQL and Forms, so please have patience with me
I am developing under Forms 6i, and an Oracle 8i database.
At the moment i am working on filling a record block dynamically depending on the users wishes.
I have the tables a and b(more will come later to that), and im using as datasource a FROM clausel in Forms to fill this block.
Table a looks like that:
id number
zip varchar2
region varchar2
city varchar2
title varchar2

Table b looks like that:
release date
a_id number (id of table a)

other unimportant fields..
When i now do a statement:
SELECT a.id, a.zip, a.region, a.city, a.title, b.release
FROM pt a, rs b
WHERE a.id = b.a_id
AND b.release =
(SELECT MAX(b.release) FROM RS WHERE a.id = b.a_id)

Forms is telling me:
ORA-00904: invalid column name

I already checked the right order of the fields in the record block, and the fields in the record block are the same like in the select statement. The only difference is that all fields in the record block are varchars2.
Does anyone have an idea, what the problem could be? Or maybe know some sources about Forms where i could learn more about Forms 6i?

Thank you for your help.

Helge.

 

[sem]

Could you explain the meaning of not referenced RS table in subquery? I suspect that you should replace b.release with RS.release and b.a_id with RS.a_id or choose another alias for RS in subquery.
As for the main question, I think that field names in query may differ from item names in block. BTW, can you execute that query from sql*plus at least?

Regards, Dima

http://seminihin.narod.ru

 

[Avaatar]

Hello,
thank you for the fast reply. The table RS in the subquery is the same like RS in the main query, so i forgot to use the alias there.
I solved the problem now, in all my trying different ways i had a clausel left in the "Where clausel" in Forms, so Forms mixed it up.
The statement like its shown above is running now, it works from Toad too, but is very slow(10 seconds) and it brings out 8 results(number of records in rs where rs.ptno = pt.ptno). Thats 7 too many But its a step ahead, thank you

Helge.

 

[sem]

So what does your final query look like?

Regards, Dima

http://seminihin.narod.ru

 

[Avaatar]

It is now:

SELECT DISTINCT a.ptno, a.pcode, a.geo2, a.site4, a.ptname, b.release
FROM pt a, rs b
WHERE a.ptno = b.ptno
AND b.release =
(SELECT MAX(b.release) FROM RS b WHERE a.ptno = b.ptno)

The interesting was, it took so long to execute this query, because Oracle perfomed a full table scan on rs in the subquery. When i inserted there the alias "b" too, it didnt do a full table scan anymore, and the distinct reduced the output to just 1 record, like it was wished.
Thanks for the help again, it gave me a toss in the right direction

Regards,

Helge