Sergey,

And I always thougth a carthesian product was something like a curse!
Anyhow there is a little something in your coding which is not correct, but nevermind you brought me on the correct track and meanwhile also Naomi and Boris who also found the correct answer.

Thanks for your reply, was very helpfull.

Best regards,

Koen

>You do need Cartesian product (CROSS JOIN) for curOrdertype and curClient
>Try
>

>SELECT curClient.cName, CAST(NVL(curOrder.nAmount, 0.00) AS N(8,2)) AS Amount, curOrdertype.cType ;
>	FROM curOrdertype, curClient ;
>		LEFT JOIN curOrder ON curOrder.iOrdertype = curOrdertype.ID ;
>				AND curOrder.iClient = curClient.ID ;
>	ORDER BY curClient.ID, curOrdertype.ID 			
>

>
>

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

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