Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Need advice on SQL
Message
 
À
17/09/2006 12:19:49
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Database:
Visual FoxPro
Divers
Thread ID:
01154440
Message ID:
01154488
Vues:
12
Michel,

I would suggest to stop trying to do the whole job with one query. Keep your original query to get the dat with memos with an added filed for firstvideo number like "..., 0 as FirstVideo ...". Make that query READWRITE. Then do a scan loop and fire my suggestion to simply get the first video number for each program. Something like this;
SELECT Program.Numero, Program.Active, Program.Title, Program.Notes_E, Program.Notes_F, ;
       Program.NoMaster, Master.FirstName, Master.LastName, MasterType.Title_E AS MasterType_E, ;
       MasterType.Title_F AS MasterType_F, Video.Url, Video.Numero AS PrimaryKey, INT(0) AS FirstVideo ;
  FROM Program ;
  JOIN Master ON Program.NoMaster=Master.Numero ;
  JOIN MasterType ON Master.NoMasterT=MasterType.Numero ;
  JOIN Video ON Program.Numero=Video.NoProgram ;
  JOIN InvoiceI ON Video.Numero=InvoiceI.NoVideo ;
  JOIN Invoice ON InvoiceI.NoInvoice=Invoice.Numero ;
 WHERE Program.Active AND Invoice.AddUser=1 ;
 ORDER BY Program.Title ;
  INTO CURSOR VidsSold READWRITE


SELECT 0
SELECT Program.Numero AS Numero, MIN(Program.Numero) as FirstVideo ;
  FROM Master ;
  JOIN Program ON Program.NoMaster=Master.Numero ;
 GROUP BY Master.Numero INTO CURSOR FirstVids

SELECT VidsSold
SCAN
   SELECT FirstVids
   LOCATE FOR VidsSold.Numero = FirstVids.Numero
   REPLACE VidsSold.FirstVideo WITH FirstVids.FirstVideo
ENDSCAN

* Now your VidsSold cursor has all of your data (with memos) plus a field named 
* FirstVideo that holds the first video for the given series
The only question here is now many programs are there? If you are dealing with 10's of thousands of programs then this may be too slow, but with hunderds to thousands of programs this approach will be planty fast enough and it is simple with queries that are easily understood and maintained.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform