Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
SQL Fails with joins
Message
De
25/07/2000 11:28:42
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Divers
Thread ID:
00395785
Message ID:
00396611
Vues:
10
That's right, you'll get a lot of .NULL. fields when you do a FULL OUTER JOIN.
Which doesn't always make it useful.

So I gather that the result set you would want should look something like this?

(The first record is found left side only.
The second record is found right side only.
The third record is found both sides.)
SETTDATE   PORTFOLIO BANKNAME  NOFIND   FIND  TOTRECORDS  FINDRATE
------------------------------------------------------------------
{07/24/00} four      BankFour       1      0           1   100.000  
{07/24/00} one       BankOne        0      1           1     0.000 
{07/24/00} two       BankTwo        1      1           2    50.000 
To get this, you could do the following:
Select hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ;
	COUNT(ISNULL(newmid)) AS nofind, 0 as find ;
	FROM hitrate1 ;
	WHERE ISNULL(newmid)=.T. ;
	GROUP BY bankname, settdate ;
UNION ;
Select hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ;
	0 as nofind, COUNT(!ISNULL(newmid)) AS FIND ;
	FROM hitrate1 ;
	WHERE ISNULL(newmid)=.F. ;
	GROUP BY bankname, settdate ;
	INTO TABLE hitunion

Select bankname, portfolio, settdate, ;
	Sum(find) as find, Sum(nofind) as nofind ;
	from hitunion ;
	group by bankname, portfolio, settdate ;
	into table hitsums

Select *, (FIND+nofind) AS totrecords, ;
	(FIND/(FIND+nofind))*100 AS findrate ;
	FROM hitsums ;
	ORDER BY bankname, portfolio, settdate ;
	INTO TABLE hitrate4
HTH
Insanity: Doing the same thing over and over and expecting different results.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform