Hello, my problem is "single-row subquery returns more than one row" In SQL delveloper is easy to deal with this but in ERP LN I don't know how to solve this problem. This is my query from BAAN LN
select tiapl220.quan from tiapl220 left join tiapl310 on tiapl310.sitm = tiapl220.emod where tiapl220.exdt>:ALL(select tiapl300.pcfd
from tiapl300 left join (select tiasc200.pvar from tiasc200 left join tiasc740 on tiasc740.clso=tiasc200.asso)aa on aa.pvar=tiapl300.cpva )

The problem is that BAAN LN not recognized reserved word ALL or I used it on wrong way. Please help!

First, I do not understand what is the use of LEFT JOIN here as you are not selecting any fields from right table.

Secondly, I don't think ALL is supported. (Need confirmation from others!!!)
So you can write the subquery that only produce one result (here the aggregate functions MAX tiapl300.pcfd ).

select tiapl220.quan
from tiapl220 left join tiapl310 on tiapl310.sitm = tiapl220.emod
where tiapl220.exdt>(select MAX(tiapl300.pcfd) from tiapl300 as set with 1 rows)

Again I am not clear what you trying to do with this query!

Hello! Thank you for answer. I will try to explain better. I need this
select tiapl220.quan
from baan.ttiapl220201 tiapl220
left join tiapl310 on tiapl310.sitm = tiapl220.emod
where tiapl310.cpva = 1540 + tiapl220.exdt > tiapl300.pcfd

Date tiapl300.pcfd i try to get from this query
tiapl300.pcfd
from tiapl300
left join tiasc200 on tiapl300.cpva = tiasc200.pvar
For one tiapl300.cpva there is many tiasc200.asso

I got a lot of dates tiapl300.pcfd so I think that I have to check them all tiapl220.exdt > tiapl300.pcfd

Hi,

Also, I don't think you can use "left join" in Baan. Either you can split the query into 2 parts or use refers to. Also, ALL is not supported. Instead, there is a "selectempty" used to identify if ALL is false.

Without the actual table structure and data required, its difficult to provide the logic. But for your reference, following code is a way its done in Baan.
e.g.

db.set.to.default(ttiapl220)
select tiapl220.*
from tiapl220
selectdo

db.set.to.default(ttiapl310)
select tiapl310.*
from tiapl310
where tiapl310.sitm = :tiapl220.emod
and tiapl310.pcfd >= tiapl220.exdt
as set with 1 rows
selectdo
|* Do things if ALL is false
selectempty
|* Do things if ALL is true
endselect
endselect

Also, to know about Left Join in Baan, follow this (http://www.baanboard.com/baanboard/showthread.php?t=27556)