Naomi,

Very good, works like a charm! Thanks a lot!
Best regards,

Koen

>>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