Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Derrick Bruce
  Where is Derrick Bruce?
 Charlestown
 Indiana - United States
 Derrick Bruce
 To: ven chin
  Where is ven chin?
 
 Singapore
 ven chin
 Tags
Subject: RE: invalid sub query
Thread ID: 228579 Message ID: 228690 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Wednesday, May 20, 2009 8:37:39 PM         
   


> Dear all,
>
> I would like to retrieve the desc of my origin, destination code joining with some other tables. My SQL as below but it kept prompting me invalid subquery. I tried to search from here and modified my query base on the suggestion but it still fail. Below are my 2 query which both also invalid.
>
> SELECT h.custcode+h.custcode+h.vslcode+h.voycode+h.cbound as BatchID, c.cust_cn, h.vslname, h.voycode, h.cbound, ; h.invdate, d.etapol, d.origin, d.pol, d.pod, d.deliveryp, ;
> d.blno, d.equiptype, d.qty, 'P' as freight, d.chrgcode, IIF(d.chrgccy = 'THC','CNY',d.chrgccy) as chrgccy, ; d.chrgrate, IIF(d.chrgccy = 'USD', d.amt, 0.00) as usdamt, IIF(d.chrgccy <> 'USD', d.amt, 0.00) as rmbamt ;
> FROM local_invh h INNER JOIN local_invd d ON h.invno = d.invno ;
> INNER JOIN (SELECT desc as origin FROM portcode) ON d.origin = portcode.portcode ;
> INNER JOIN (SELECT desc as dest FROM portcode) ON d.dest= portcode.portcode ;
> INNER JOIN customer c ON h.custcode = c.custcode ;
> WHERE h.invdate = lcInvDate ;
> ORDER BY BatchID, d.origin, d.pol, d.pod, d.deliveryp, d.blno ;
> INTO CURSOR ViewAttach
>
> SELECT h.custcode+h.custcode+h.vslcode+h.voycode+h.cbound as BatchID, c.cust_cn, h.vslname, h.voycode, h.cbound, ; h.invdate, d.etapol, d.origin, d.pol, d.pod, d.deliveryp, ;
> (SELECT desc FROM portcode WHERE d.origin = portcode.portcode) as origin, ;
> (SELECT desc as dest FROM portcode WHERE d.dest= portcode.portcode) as dest, ;
> d.blno, d.equiptype, d.qty, 'P' as freight, d.chrgcode, IIF(d.chrgccy = 'THC','CNY',d.chrgccy) as chrgccy, ; d.chrgrate, IIF(d.chrgccy = 'USD', d.amt, 0.00) as usdamt, IIF(d.chrgccy <> 'USD', d.amt, 0.00) as rmbamt ;
> FROM local_invh h INNER JOIN local_invd d ON h.invno = d.invno ;
> INNER JOIN customer c ON h.custcode = c.custcode ;
> WHERE h.invdate = lcInvDate ;
> ORDER BY BatchID, d.origin, d.pol, d.pod, d.deliveryp, d.blno ;
> INTO CURSOR ViewAttach
>
> Thanks.
>
> Regards,
> Venetia



I made the jump from 2.6 to 6 (very brief) to 9 - skipped those other ones. You'll probably have to do what Christian has suggested. Bring the data in in small chunks and then link them all together - I end up doing this quite a bit when I am hitting a SQL backend and don't want to scare someone watching the profiler.

D

ENTIRE THREAD

invalid sub query Posted by ven chin @ 5/20/2009 3:33:14 AM
RE: invalid sub query Posted by Christian Tabligan @ 5/20/2009 4:13:28 AM
RE: invalid sub query Posted by ven chin @ 5/20/2009 7:26:09 AM
RE: invalid sub query Posted by Christian Tabligan @ 5/20/2009 8:13:11 AM
RE: invalid sub query Posted by Derrick Bruce @ 5/20/2009 6:01:59 AM
RE: invalid sub query Posted by ven chin @ 5/20/2009 6:18:25 AM
RE: invalid sub query Posted by Derrick Bruce @ 5/20/2009 6:39:58 AM
RE: invalid sub query Posted by ven chin @ 5/20/2009 7:25:26 AM
RE: invalid sub query Posted by Anders Altberg @ 5/20/2009 10:57:36 AM
RE: invalid sub query Posted by Derrick Bruce @ 5/20/2009 8:37:39 PM