Boris,
Very good! Works like a charm! Thanks a lot!
Best regards,
Koen
P.S. Will I see you in the SWFconference?
>>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 Client (ID i, cName c(10))
>INSERT INTO Client values(1,"john")
>INSERT INTO Client values(2,"pete")
>INSERT INTO Client values(3,"eva")
>
>
>SELECT Tbl1.cType,;
> curOrder.iClient,;
> NVL(curOrder.nAmount, 00000.00) AS Ammount,;
> Tbl1.cName ;
>FROM (SELECT Client.cName,;
> curOrdertype.cType,;
> Client.Id,;
> curOrdertype.Id AS OrdType;
> FROM Client, curOrdertype) Tbl1;
>LEFT JOIN curOrder ON curOrder.iClient = Tbl1.ID AND curOrder.iOrderType = Tbl1.OrdType;
>ORDER BY Tbl1.cName, Tbl1.OrdType
>
