Things have been a “bit hectic” around here so it took some time to get back to you.
When I attempted to compile I received the message, “Command contains unrecognized phrase/keyword". I finally got over that problem. As the query becomes more complex I will realize if there are limitations for what I have to accomplish.
Thank you for your response and suggestions. Part of the problem was the 255 character limit. VFP 6 does not have the Text/EndText function so I am trying Mike’s suggestion. Thank you for the error code - it lets me know when there is a problem. :)
I am trying to convince management to upgrade to VFP 8. That may help a bit.
>I'll try to split out the query into a TEXT...ENDTEXT block to avoid the 255 chars limit (if applicable).
>To checkout if this is an server-side error try using AERROR() function whenever SQLExec returns < 0...
>I have used SQL Pass Thru for years but using only one table. My requirement is to create complex SQL statements and I hope that SQL Pass Thru is up to the task. Is there a limit of some kind that I should be aware of? It seems that adding the AND statement causes a problem. It does not matter what I set the AND statement and field to either.
>
>>Visual FoxPro 6.0 and SQL Server 2000 are in use with Windows 2000.
>
>>Basic Examples:
>
>>This works:
>>SQLEXEC( tConnection, 'SELECT Tally.ReworkNumber, Tally.Problem,
>TallyDetail.Kanban, TallyDetail.PartNumber, Tally.PartName,
>Tally.SupplierName ;
> FROM Tally, TallyDetail ;
> WHERE Tally.ReworkNumber = TallyDetail.ReworkNumber ','cuWeekly')
>
>>This does not work:
>>Local ldEndDate
>ldEndDate = "09/22/2004"
>
>SQLEXEC(tConnection, 'SELECT Tally.ReworkNumber, Tally.Problem,
>TallyDetail.Kanban, TallyDetail.PartNumber, Tally.PartName,
>Tally.SupplierName ;
> FROM Tally, TallyDetail ;
> WHERE Tally.ReworkNumber = TallyDetail.ReworkNumber ;
> AND Tally.DateCreated = ?ldEndDate','cuWeekly')
>
>
>>Here is one of the queries I have to create and this is taken from Access:
>>SELECT DISTINCTROW [qryQuantityReworked_s].[ReworkNumber],
> [qryQuantityReworked_s].[Code], Sum([qryQuantityOK_s].[Monday]) AS OKMonday,
> Sum([qryQuantityReworked_s].[Monday]) AS ReworkedMonday,
>Sum([qryQuantityReturned_s].[Monday]) AS ReturnedMonday,
>Sum([qryQuantityOK_s].[Tuesday]) AS OKTuesday,
>Sum([qryQuantityOK_s].[Wednesday]) AS OKWednesday,
>Sum([qryQuantityOK_s].[Friday]) AS OKFriday,
>Sum([qryQuantityOK_s].[Saturday]) AS OKSaturday,
>Sum([qryQuantityReworked_s].[Tuesday]) AS ReworkedTuesday,
>Sum([qryQuantityReworked_s].[Wednesday]) AS ReworkedWednesday,
>Sum([qryQuantityReworked_s].[Friday]) AS ReworkedFriday,
>Sum([qryQuantityReturned_s].[Tuesday]) AS ReturnedTuesday,
>Sum([qryQuantityReturned_s].[Wednesday]) AS ReturnedWednesday,
>Sum([qryQuantityReturned_s].[Friday]) AS ReturnedFriday,
>Sum([qryQuantityReturned_s].[Saturday]) AS ReturnedSaturday,
>Sum([qryQuantityReworked_s].[Saturday]) AS ReworkedSaturday,
>[Rework].[SupplierName] AS Supplier, [Rework].[PartName],
>[Rework].[ReworkNumber], [LineCall].[QCEContact], [LineCall].[Packaging],
>[Rework].[Responsibility], Sum([qryQuantityOK_s].[Thursday]) AS OKThursday,
>Sum([qryQuantityReworked_s].[Thursday]) AS ReworkedThursday,
>Sum([qryQuantityReturned_s].[Thursday]) AS ReturnedThursday, [Rework].[Style]
> AS Model, [Rework].[RIDate] AS RIDate, [Rework].[Status] AS Status,
>[Rework].[DateClosed] AS ActualStopDate,
>[qryCumulativeTotal2_s].[SumOfQuantityReworked],
>[qryCumulativeTotal2_s].[SumOfQuantityReturned],
>[qryCumulativeTotal2_s].[SumOfTotalOK], [Rework].[ReworkLocation],
>[TallyDetail].[PartNumber] INTO tblWeeklyReport2_s
>FROM ((TallyDetail INNER JOIN Rework ON
>[TallyDetail].[ReworkNumber]=[Rework].[ReworkNumber]) INNER JOIN
>(((qryQuantityOK_s INNER JOIN qryQuantityReworked_s ON
>([qryQuantityOK_s].[Code]=[qryQuantityReworked_s].[Code]) AND
>([qryQuantityOK_s].[REWORKNumber]=[qryQuantityReworked_s].[ReworkNumber]))
>INNER JOIN qryQuantityReturned_s ON
>([qryQuantityReworked_s].[Code]=[qryQuantityReturned_s].[Code]) AND
>([qryQuantityReworked_s].[ReworkNumber]=[qryQuantityReturned_s].[ReworkNumber]))
> INNER JOIN qryCumulativeTotal2_s ON
>([qryQuantityReturned_s].[Code]=[qryCumulativeTotal2_s].[Kanban]) AND
>([qryQuantityReturned_s].[ReworkNumber]=[qryCumulativeTotal2_s].[ReworkNumber]))
> ON ([Rework].[ReworkNumber]=[qryQuantityOK_s].[REWORKNumber]) AND
>([TallyDetail].[ReworkNumber]=[qryCumulativeTotal2_s].[ReworkNumber])) INNER
>JOIN LineCall ON [TallyDetail].[ReworkNumber]=[LineCall].[ReworkNumber]
>GROUP BY [qryQuantityReworked_s].[ReworkNumber],
>[qryQuantityReworked_s].[Code], [Rework].[SupplierName], [Rework].[PartName],
> [Rework].[ReworkNumber], [LineCall].[QCEContact], [LineCall].[Packaging],
>[Rework].[Responsibility], [Rework].[Style], [Rework].[RIDate],
>[Rework].[Status], [Rework].[DateClosed],
>[qryCumulativeTotal2_s].[SumOfQuantityReworked],
>[qryCumulativeTotal2_s].[SumOfQuantityReturned],
>[qryCumulativeTotal2_s].[SumOfTotalOK], [Rework].[ReworkLocation],
>[TallyDetail].[PartNumber];
>
>
>>Should I punt?
>
>>Excuse the formatting. This formatting seems to have a mind of it's own! :)
>
>>Any suggestions will be appreciated.
>
>>Tom
