Join SQL tables in foxpro

[Niki_S]

I have 2 tables in sql and I need to join them in my foxpro.
Table 1

cFtyCD   cInvNo   cIntimNo   cGMTName    dXFactory                 cHTSCode    cSggNo     nInvQty    cGrnNumber
FMF      S0945    123        lADIES      2010-01-08 00:00:00.000   S145        SGG0945    43         S87
FMF      S0945    123        lADIES      2010-01-08 00:00:00.000   S145        SGG0945    50         S87
SGL      S0941    567        SKIRTS      2010-01-12 00:00:00.000   S87         SGG0941    25         S345
SGL      S0941    567        SKIRTS      2010-01-12 00:00:00.000   S87         SGG0941    100        S345
SGL      S0941    567        SKIRTS      2010-01-12 00:00:00.000   S87         SGG0941    150        S345

Table 2

cIntimNum   cGarmentName    TotGross
123         lADIES          145
567         SKIRTS          56

These are my tables in sql and I need to get my final result in foxpro as below.

cFtyCD   cInvNo   cIntimNo  cGMTName    dXFactory                cHTSCode    cSggNo     nInvQty    cGrnNumber        TotGross
FMF      S0945    123       lADIES      2010-01-08 00:00:00.000  S145        SGG0945    93         S87               145
SGL      S0941    567       SKIRTS      2010-01-12 00:00:00.000  S87         SGG0941    275        S345              56

I did my foxpro code as below , but there have syntax error.

stra="SELECT vInvFinal.cSggNo, vInvFinal.cInvNo, vInvFinal.cProdFty , vInvFinal.cGMTName, vInvFinal.cHTSCode , "
stra=stra+" SUM(vInvFinal.nInvQty) as nInvQty,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNo "
stra=stra+" vFinalInvGrossWgt.cIntimNum, vFinalInvGrossWgt.cGarmentName, vFinalInvGrossWgt.TotGross, cBOICWHCode"

stra=stra+ " FROM (MIS.dbo.vFinalInvGrossWgt inner join MIS.dbo.vInvFinal on vFinalInvGrossWgt.cIntimNum= vInvFinal.cIntimNo and vFinalInvGrossWgt.cGarmentName=vInvFinal.cGMTName) "


stra=stra+ " WHERE vInvFinal.dXFactory >= ?thisform.txtFromDate.Value AND vInvFinal.dXFactory <= ?thisform.txtToDate.Value AND vInvFinal.cSggNo IS NOT NULL and vInvFinal.cGrnNumber IS NOT NULL "
stra=stra+ " group by vInvFinal.cSggNo,vInvFinal.cInvNo,vInvFinal.cProdFty,vInvFinal.cGMTName,vInvFinal.cHTSCode,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNo "

SQLEXEC(hndOps,stra,'SGG_GRN')

Can someone please tell me what should I do for this?
Thank you

 

[pjw001]

I think you are missing a comma at the end of your second line.

It should be stra=stra+" SUM(vInvFinal.nInvQty) as nInvQty,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNo , "

If that doesn't solve it, is it possible to print the contents of stra?