Hi everyone!

According to progguide it is possible to use subqueries (derived tables) in FROM clause.
But in does not work. My code:

long rq_cnt
long rq_lines_cnt
select tdpur206.adep, tdpur206.aemn,
count(*) as rq_cnt,
sum(rq_lines.cnt) as rq_lines_cnt
from tdpur206
left join (select tdpur201.rqno, count(*) cnt
from tdpur201
group by tdpur201.rqno) rq_lines on tdpur206.rqno = rq_lines.rqno
group by tdpur206.adep, tdpur206.aemn
selectdo
message("rq_cnt=" & str$(rq_cnt) & " rq_lines_cnt=" & str$(rq_lines_cnt))
endselect
causes a compilation error "Error SQL: SQLState QP000: SQLStatement::doPrepare(): Unexpected exception (error 215)".
And even simle code:

long rq_lines_cnt
select rq_lines.cnt as rq_lines_cnt
from (select 1 as cnt from tdpur201) rq_lines
selectdo
message("rq_lines_cnt=" & str$(rq_lines_cnt))
endselect
causes the same error.

Infor support reported that "Internal revision showed that the compilation of the query on Porting Set 8.7a.02 was succesfull. Porting Set available at solution 22945298."
Recently I tested it on my server with Porting Set 8.7a.02 but this compilation error remained.
Can somebody else test these queries on Porting Set 8.7a.02? Or maybe it is working on other Porting Sets?...

Hi SerBel,

test this code. I couldn't test it, because we've an old Porting Set.


long rq_cnt
long rq_lines_cnt
long cnt

select tdpur206.adep, tdpur206.aemn, count(*) as rq_cnt, sum(rq_lines.cnt) as rq_lines_cnt
from tdpur206 left join (select tdpur201.rqno, count(*) as cnt
from tdpur201
group by tdpur201.rqno) rq_lines on tdpur206.rqno = rq_lines.rqno
group by tdpur206.adep, tdpur206.aemn
selectdo
message("rq_cnt=" & str$(rq_cnt) & " rq_lines_cnt=" & str$(rq_lines_cnt))

endselect


best regards
michael

The same error...
Declaring variable "cnt" did not help.
When we use a function (min, max, count...) or constant in subquery, we get this compilation error.
Infor support reported that they fixed this bug in Porting Set 8.7a.02, but it seems to me they didn't.
Please guys, check subqueries on your systems. May be this bug not in Porting Set but in version of InforLN for operating system or database. I have Windows 2008 and Oracle 11.1.0.7.0.

Is it so hard to test and post result here? Please, help me. I really need to know.

long rq_lines_cnt
select rq_lines.cnt as rq_lines_cnt
from (select 1 as cnt from tdpur201) rq_lines
selectdo
message("rq_lines_cnt=" & str$(rq_lines_cnt))
endselect

Not a LN person, never used it - the more I look at this the more I wonder about this code. From looking at the above code I would expect it to look something like this:

long rq_lines_cnt
select rq_lines as rq_lines_cnt
from (select 1 as cnt from tdpur201) as rq_lines
selectdo
message("rq_lines_cnt=" & str$(rq_lines_cnt))
endselect
I mean in the first code you are selecting rq_lines_cnt as rq_lines_cnt - does not make sense to me. I do not know ln or this format - so just a swag. You will have to wait for someone that uses this stuff in LN.