Invalid Subquery

[kingduck]

i am trying to excute a query

sele a.sup_no, (sele c.ord_qty from po_dtal c where c.po_no=b.po_no and c.invent_no=b.invent_no) as ord_qty, b.po_no,b.ord_unit,'         ' as Gstatus from grn_hdr a, grn_dtal b where a.grn_no=b.grn_no

it gives an error SQL:Invalid use of subquery
i excuted the same query in MSAccess it worked fine

what could be the problem

Thanks in advance

 

[marsh]

Try:
sele a.sup_no, c..ord_qty as ord_qty, b.po_no,b.ord_unit,' ' as Gstatus
from grn_hdr a join grn_dtal b
join po_dtal c
on c.po_no=b.po_no and c.invent_no=b.invent_no
on a.grn_no=b.grn_no

VFP is not fund of derived queries.....

 

[Stella740pl]

kingduck,

Not sure about MS Access, but in VFP you cannot use subquery
as a SELECTed expression (you can use it in WHERE clause, though). What you can do is issue you queries one by one or connect tables using JOIN clause.

Try something like that (I haven't tested it yet):

select a.sup_no, ;
c.ord_qty, ;
b.po_no,b.ord_unit, ;
' ' as Gstatus ;
from grn_hdr a ;
inner join grn_dtal b on a.grn_no=b.grn_no ;
inner join po_dtal c on c.invent_no=b.invent_no

 

[kingduck]

Thanks for your reply. I guess that an inner join would do the trick