Nesting Select Statements 
Author Message
 Nesting Select Statements

Does anyone have an example/syntax of a nested select using a Visual Foxpro
Driver...

If not, does anyone know if Visual Foxpro supports nested selects????

Thank you in advance...
Rod Riley
Canquest Communication (Canada) Inc.



Sat, 08 Mar 2003 03:00:00 GMT  
 Nesting Select Statements
What do you mean by nested select? a subquery?
Like this?

SELECT table1.* ;
  FROM table1 ;
  WHERE cfield1 IN ;
     (SELECT cfield1 FROM table2) ;
  INTO CURSOR cutmp


Quote:
> Does anyone have an example/syntax of a nested select using a Visual
Foxpro
> Driver...

> If not, does anyone know if Visual Foxpro supports nested selects????

> Thank you in advance...
> Rod Riley
> Canquest Communication (Canada) Inc.



Sat, 08 Mar 2003 03:00:00 GMT  
 Nesting Select Statements

Select c.CustomerID,
            c.CustomerName,
            (Select Sum(Cost) From Invoices inv Where inv.CustomerID =
c.CustomerID) As TotalInvoiceCost
  From Customer c
  Into table cursor ctemp

Quote:

>What do you mean by nested select? a subquery?
>Like this?

>SELECT table1.* ;
>  FROM table1 ;
>  WHERE cfield1 IN ;
>     (SELECT cfield1 FROM table2) ;
>  INTO CURSOR cutmp



>> Does anyone have an example/syntax of a nested select using a Visual
>Foxpro
>> Driver...

>> If not, does anyone know if Visual Foxpro supports nested selects????

>> Thank you in advance...
>> Rod Riley
>> Canquest Communication (Canada) Inc.



Sun, 09 Mar 2003 03:00:00 GMT  
 Nesting Select Statements

Actually, this the SQl statement I'm trying to run if vfp. It works great in
SQL Server ....

I get the error -- "Invalid use of Sunquery"

Select acct.    AccountID,
                        acct.FirstUse,
                        acct.Expiry,
                        acct.LastUse,
                        pb.PinValue,
                        acct.Balance,
                        acct.Usage,
                        (Select     Sum(rl.RechargeAmount)
                            From    RechargeLog rl
                            Where  acct.AccountID = rl.AccountID) As
RechargeAmount,
                        (Select     Sum(cl.CreditAmount)
                            From    Creditlog cl
                            Where  acct.AccountID = cl.AccountID) As
CreditAmount,
                        (Select     Sum(ats.Amount)
                            From    AccountTransactions ats
                            Where  acct.AccountID = ats.AccountID) As
TransactAmount
    From  Account acct Left Join Pinbatch pb  On acct.AccountID =
pb.AccountID
    Where LevelID = 3

Quote:

>Select c.CustomerID,
>            c.CustomerName,
>            (Select Sum(Cost) From Invoices inv Where inv.CustomerID =
>c.CustomerID) As TotalInvoiceCost
>  From Customer c
>  Into table cursor ctemp


>>What do you mean by nested select? a subquery?
>>Like this?

>>SELECT table1.* ;
>>  FROM table1 ;
>>  WHERE cfield1 IN ;
>>     (SELECT cfield1 FROM table2) ;
>>  INTO CURSOR cutmp



>>> Does anyone have an example/syntax of a nested select using a Visual
>>Foxpro
>>> Driver...

>>> If not, does anyone know if Visual Foxpro supports nested selects????

>>> Thank you in advance...
>>> Rod Riley
>>> Canquest Communication (Canada) Inc.



Sun, 09 Mar 2003 03:00:00 GMT  
 Nesting Select Statements


Quote:
> Select c.CustomerID,
>             c.CustomerName,
>             (Select Sum(Cost) From Invoices inv Where inv.CustomerID =
> c.CustomerID) As TotalInvoiceCost
>   From Customer c
>   Into table cursor ctemp

Those kind of queries are not supported in native VFP SQL. As SQLPassthrough
against other ODBC datasource is different. You'd get the same results by
separating the subquries and then join the results I suppose.

SELECT C.cust, NVL(SUM(Inv.cost), COUNT(inv.cust)) AS totalinvoices ;
    FROM C AS C LEFT JOIN inv AS inv ;
    ON C.cust=Inv.cust;
    GROUP BY inv.cust ;
    INTO CURSOR Q1
 SELECT C.customerid, C.customername , I.totalcost;
    FROM Customers C JOIN Q1 I IN I.customerid=C.customerid ,
    INTO TABLE Invoicetotals

or, wouldn't this give identical results:
SELECT C.custid, C.name, NVL(SUM(Inv.cost),00000) AS totalcost  ;
    FROM Customers C LEFT JOIN Invoices  AS Inv ;
    ON Inv.customerid = C.customerid GROUP BY C.cust
?
-Anders



Sun, 09 Mar 2003 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Still looking ==>> Nesting Select

2. Nested SQL Select Question

3. Theoretical Maximum Length on SELECT Statement

4. SQL - Select Statement and SUM

5. Select statement timeout

6. memo in select statement

7. Problem with SQL SELECT Statement

8. Problem with SQL SELECT Statement

9. ASP select statement using Fox tables

10. length limitation of SELECT - SQL statement

11. properly use OR's in select statements

12. SELECT statement variables.

 

 
Powered by phpBB® Forum Software