>Hi,
>
>I need to construct a cursor with all possible solutions (no not a carthesian) :
>
>

>CREATE CURSOR curOrder (id i ,iClient i, nAmount n(8,2), iOrderType i ) 
>INSERT INTO curOrder VALUES( 1, 1, 100.00, 1)
>INSERT INTO curOrder VALUES( 2, 2, 110.00, 1)
>INSERT INTO curOrder VALUES( 3, 2, 120.00, 2)
>
>
>CREATE CURSOR curOrdertype (ID i, cType c(10))
>INSERT INTO curOrdertype values(1,"import")
>INSERT into curOrdertype VALUES(2,"export")
>
>CREATE CURSOR Client (ID i, cName c(10))
>INSERT INTO Client values(1,"john")
>INSERT INTO Client values(2,"pete")
>INSERT INTO Client values(3,"eva")
>

>
>My target is:
>
>John, 100.00, import
>John, 0.00, export
>Pete, 110.00, import
>Pete, 120.00, export
>Eva, 0.00, import
>Eva, 0.00, export
>
>The basic statement :
>

>SELECT curOrdertype.cType, curOrder.iClient, curOrder.nAmount, curClient.cName ;
>FROM curOrdertype ;
>FULL JOIN curOrder ON curOrder.iOrdertype = curOrdertype.ID ;
>FULL JOIN curClient ON curClient.ID = curOrder.iClient
>

>will not show the 0.00 order amount lines, I dont seem to be able to construct a correct union , anyone with a good idea?
>
>Thanks,
>
>Koen

It will show NULL in case of no match, which you can convert to 0 using VFP NVL function. In SQL Server use COALESCE or ISNULL.

You also do need a cartesian product here for your request.

CREATE CURSOR curOrder (id i ,iClient i, nAmount n(8,2), iOrderType i ) 
INSERT INTO curOrder VALUES( 1, 1, 100.00, 1)
INSERT INTO curOrder VALUES( 2, 2, 110.00, 1)
INSERT INTO curOrder VALUES( 3, 2, 120.00, 2)


CREATE CURSOR curOrdertype (ID i, cType c(10))
INSERT INTO curOrdertype values(1,"import")
INSERT into curOrdertype VALUES(2,"export")

CREATE CURSOR curClient (ID i, cName c(10))
INSERT INTO curClient values(1,"john")
INSERT INTO curClient values(2,"pete")
INSERT INTO curClient values(3,"eva")

SELECT T.cName, T.cType, NVL(curOrder.nAmount,0) as nAmount ;
FROM (select curClient.*, curOrderType.cType, curOrderType.ID as iOrderType FROM curClient, curOrdertype) T ;
LEFT JOIN curOrder ON T.ID = curOrder.iClient AND T.iOrderType =curOrder.iOrdertype  ;
Order by T.cName