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


> > 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
>
>
>
INNER JOIN (SELECT portcode,desc as origin FROM portcode) as e ON d.origin = e.portcode 

>
> Same with the other one - need to include your join condition in the select of the derived table.


Dear Derrick,

Thanks.

I tried but still unable.

System prompted syntax error of my query.

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.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 customer c ON h.custcode = c.custcode ;
INNER JOIN (SELECT portcode,desc as origin FROM portcode) as z ON d.origin = z.portcode ;
INNER JOIN (SELECT portcode,desc as desc FROM portcode) as e ON d.origin = e.portcode ;
WHERE &lcSQLCmd ;
ORDER BY BatchID, d.origin, d.pol, d.pod, d.deliveryp, d.blno;
INTO CURSOR ViewAttach

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