Still looking ==>> Nesting Select 
Author Message
 Still looking ==>> Nesting Select

Could anyone help me with the syntax for nesting selects in vfp......

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

Thanks in advance
Rod Riley
Canquest Communication (Canada) Inc



Sun, 09 Mar 2003 03:00:00 GMT  
 Still looking ==>> Nesting Select
Rod,

Subqueries can only be used in the Where clause.

Hence, you have to split the query
Have not gone too much into your code but I take it you will get the example

select    accountID, ;
            Sum(rl.RechargeAmount) As RechargeAmount ;
    From    RechargeLog ;
    into Cursor tmp1 ;
    Group by 1

sort of same for subquery 2
sort of same for subquery 3

then make your result without the subqueries

select .........
    from AccountTransactions, tmp1, tmp2, tmp3 ;
    where ( x1 == tmp1.y1 ) ;
    and    ( x2 == tmp2.y2 ) ;
    and    ( x3 == tmp3.y3 )

------------------

| Could anyone help me with the syntax for nesting selects in vfp......
|
| 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
|
| Thanks in advance
| Rod Riley
| Canquest Communication (Canada) Inc
|
|
|
|
|



Sun, 09 Mar 2003 03:00:00 GMT  
 Still looking ==>> Nesting Select

Rod,

Be sure to set the 0.0000 to be the right size to define your field.  SQL
determines the format of the field by the first one it sees.

SELECT acct.AccountID,
    Acct.FirstUse, Acct.Expiry, Acct.LastUse, ;
    Pb.PinValue, Acct.Balance, Acct.Usage,
    SUM(IIF(Acct.AccountID = Rl.AccountID, ;
    Rl.RechargeAmount, 0.0000)) AS RechargeAmount, ;
    SUM(IIF(Acct.AccountID = Rl.AccountID, ;
    Cl.CreditAmount, 0.0000)) AS CreditAmount, ;
    SUM(IIF(Acct.AccountID = Rl.AccountID, ;
    Ats.Amount, 0.0000)) AS TranscatAmount, ;
    FROM Account Acct ;
    LEFT JOIN Pinbatch Pb ;
    ON Acct.AccountId = Pb.AccountId ;
    WHERE LevelId = 3

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center


| Could anyone help me with the syntax for nesting selects in vfp......
|
| 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
|
| Thanks in advance
| Rod Riley
| Canquest Communication (Canada) Inc
|
|
|
|
|



Sun, 09 Mar 2003 03:00:00 GMT  
 Still looking ==>> Nesting Select

Cindy,

Yesterday evening( GMT+1 DSL == GMT+2), having looked at your solution I
wondered why I hadn't found it.
This morning I felt a bit unsure as to whether it would produce the correct
result.

I just finished testing and I must confess it works great !

So, I have learnt  another means of the 'group by'

greg
----------------------------------------------------

| Rod,
|
| Be sure to set the 0.0000 to be the right size to define your field.  SQL
| determines the format of the field by the first one it sees.
|
| SELECT acct.AccountID,
|     Acct.FirstUse, Acct.Expiry, Acct.LastUse, ;
|     Pb.PinValue, Acct.Balance, Acct.Usage,
|     SUM(IIF(Acct.AccountID = Rl.AccountID, ;
|     Rl.RechargeAmount, 0.0000)) AS RechargeAmount, ;
|     SUM(IIF(Acct.AccountID = Rl.AccountID, ;
|     Cl.CreditAmount, 0.0000)) AS CreditAmount, ;
|     SUM(IIF(Acct.AccountID = Rl.AccountID, ;
|     Ats.Amount, 0.0000)) AS TranscatAmount, ;
|     FROM Account Acct ;
|     LEFT JOIN Pinbatch Pb ;
|     ON Acct.AccountId = Pb.AccountId ;
|     WHERE LevelId = 3



Mon, 10 Mar 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. --------<<<<<<MONEY>>>>>>--------- id 4685

2. *** COPIED FROM: >>>foxusergroup/problems 1757 dgraya(1325)9Sep95 01:37m

3. *** COPIED FROM: >>>Usenet/comp.databases 25688 codeman@cix.co(514)31Jul95 16:28

4. *** COPIED FROM: >>>foxusergroup/problems 712 jack_mason(524)26Apr95 19:17m

5. >>> PUBLICATION QUALITY SOFTWARE MANUA

6. *** COPIED FROM: >>>foxusergroup/activities 458 mnapier(1001)5Nov96 14:14

7. *** COPIED FROM: >>>foxusergroup/activities 445 wonderbison(559)15Jul96 13:22

8. Select Text in textbox -> container -> container ->column ->grid

9. ANSWER ====>>>>> Database maintenance at user location.

10. QUESTION <<< How do I use PROPER to change each record?>>>>>>>>>>>>>>>>>>HELP PLEASE TIA !!!!!

11. >>>> Help needed: uploading bar-delimited("|") ascii file

12. <<>> COMPUTER SOFTWARE / HARDWARE <<>>

 

 
Powered by phpBB® Forum Software