Join sql tables in Foxpro9

[Niki_S]

I have two tables in sql server and I need to join them in my foxpro form button. When I click the button I need to join these two tables.

stra="Select WG.cIntimNum, WG.cGarmentName, FI.cIntimNo, FI.cGMTName From MIS.dbo.vFinalInvGrossWgt WG where dXFactory >= ?thisform.txtFromDate.Value AND dXFactory <= ?thisform.txtToDate.Value and AND cSggNo IS NOT NULL and cGrnNumber IS NOT NULL JOIN MIS.dbo.vInvFinal FI ON WG.cIntimNum= FI.cIntimNo AND WG.cGarmentName= FI.cGMTName MIS.dbo.vInvFinal group by cIntimNo "
SQLEXEC(hndOps,stra,'SGG_GRN')

What should I do to join these two tables.
Thank you

 

[Chris Miller]

You only get one result table from SQLExec, the join already happen in SQL Server.
If that's not what you want then make two queries and NOT join them, instead later join them in VFP or SET RELATION or use two grids with 1:many settings.

But again this is a question that has so little to do with VFP, the only thing which relates to VFP is that you use MSQSQL through SQLEXEC, which btw is one of a family of functions called SQL Passtrough. From that name you can deduct that VFP only passes the commands to MSSQL (or another backend sql server), if you don't get the wanted result or no result, then look into trying the query with SQL Server management studio or go back to what I already pointed out: SQL Error handling in VFP: thread184-1583706.

[highlight #8AE234]JOIN MIS.dbo.vInvFinal FI ON WG.cIntimNum= FI.cIntimNo AND WG.cGarmentName= FI.cGMTName[/highlight] [highlight #FF7777]MIS.dbo.vInvFinal[/highlight] [highlight #8AE234]group by cIntimNo[/highlight]

You'll get an error in your query, because you donÄt specifay what to do with the second occurrance of MIS.dbo.vInvFinal. Maybe as you wrote this query you were interrupted and now have the table twice, then remove it, maybe you want to join it twice, then you need a second JOIN condition. But as it is it's invalid SQL, as simple as that.

And since errors which happen within MSSQL Server don't cause VFP error message, you have to actuvely see whther the esult cursor is created, whehter the numeric result of SQLEXEC isn't negative, and if it is negative use AERROR() to get the error information. (see the referred thread).

It's always helpful to see a query in multiple lines formatted for readability to spot such errors, if you're not doing error handling, at least. So use TEXT...ENDTEXT as was already shown to you multiple times by me and bborrissov and I think also from Mike.

Chriss

 

[Chris Miller]

I get no syntax error from your code.

What happens if you do this query in Sql server Management Studio (SSMS)?

SELECT vInvFinal.cSggNo, vInvFinal.cInvNo, vInvFinal.cProdFty , vInvFinal.cGMTName, vInvFinal.cHTSCode , 
 SUM(vInvFinal.nInvQty) as nInvQty,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNom
 vFinalInvGrossWgt.cIntimNum, vFinalInvGrossWgt.cGarmentName, vFinalInvGrossWgt.TotGross, cBOICWHCode
FROM (MIS.dbo.vFinalInvGrossWgt inner join MIS.dbo.vInvFinal on vFinalInvGrossWgt.cIntimNum= vInvFinal.cIntimNo and vFinalInvGrossWgt.cGarmentName=vInvFinal.cGMTName)
WHERE vInvFinal.dXFactory >= ?thisform.txtFromDate.Value AND vInvFinal.dXFactory <= ?thisform.txtToDate.Value AND vInvFinal.cSggNo IS NOT NULL and vInvFinal.cGrnNumber IS NOT NULL
group by vInvFinal.cSggNo,vInvFinal.cInvNo,vInvFinal.cProdFty,vInvFinal.cGMTName,vInvFinal.cHTSCode,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNo

I guess there's a hard to spot error in there. If you don't put all code into one line but format it, the SQL error message will be more helpful, as it points out the line of error, but if the whole query is in one line it's always an error in line1. Then "near '.' of course isn't helpful.

As I said, why don't you format your code in VFP already with TEXT..ENDTEXT. You've been shown multiple times and it's not just making code better readable, it also makes SQL errors more helpful with their line number hint.

TEXT TO stra NOSHOW
SELECT vInvFinal.cSggNo, 
vInvFinal.cInvNo, 
vInvFinal.cProdFty , 
vInvFinal.cGMTName, 
vInvFinal.cHTSCode , 
 SUM(vInvFinal.nInvQty) as nInvQty,vInvFinal.dXFactory,
vInvFinal.cGrnNumber,vInvFinal.cIntimNom
 vFinalInvGrossWgt.cIntimNum, 
vFinalInvGrossWgt.cGarmentName, 
vFinalInvGrossWgt.TotGross, 
cBOICWHCode
FROM (MIS.dbo.vFinalInvGrossWgt 
inner join MIS.dbo.vInvFinal on vFinalInvGrossWgt.cIntimNum= vInvFinal.cIntimNo and vFinalInvGrossWgt.cGarmentName=vInvFinal.cGMTName)
WHERE vInvFinal.dXFactory >= ?thisform.txtFromDate.Value 
AND vInvFinal.dXFactory <= ?thisform.txtToDate.Value 
AND vInvFinal.cSggNo IS NOT NULL and vInvFinal.cGrnNumber IS NOT NULL
group by vInvFinal.cSggNo,vInvFinal.cInvNo,vInvFinal.cProdFty,vInvFinal.cGMTName,vInvFinal.cHTSCode,vInvFinal.dXFactory,vInvFinal.cGrnNumber,vInvFinal.cIntimNo
ENDTEXT

If SQLEXEC(hndOps,stra,'SGG_GRN')<1
   Aerror(laError)
   Set Step On && now in the debugger look into the locals window and expand the display of the laError array.
Endif

Chriss