Sergey,
exactly! However if I 'extend' this little example with
insert into curOrdertype values(3,"nbrthree")
and
insert into curClient values(4,"George")
this will give me a 0.00 for all orders ;(
Regards,
Koen
>You do need Cartesian product 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?
>>
