>>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
>
>
>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 curOrdertype.cType;
> , NVL(curOrder.nAmount,0) nAmount;
> , curClient.ID iClient;
> , curClient.cName ;
> FROM curClient ;
> JOIN curOrdertype ON .T.;
> LEFT JOIN curOrder ON curOrder.iOrdertype = curOrdertype.ID AND curClient.ID = curOrder.iClient;
> ORDER BY 3,4
>
Fabio,
Thank you a clever solution.
Regards,
Koen
