Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
SQL Select union with empty rows
Message
 
 
À
27/07/2010 09:11:43
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Versions des environnements
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Divers
Thread ID:
01474117
Message ID:
01474122
Vues:
90
This message has been marked as a message which has helped to the initial question of the thread.
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?
>
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform