I am trying to join a 5 tables, using left outer join from ms sql server server using visual fox pro 9. but I am getting this error "Command contains unrecognized phrase/keyword."
Here is my code.
SQLEXEC(lnConn4,"SELECT [TIARA$Sales Invoice Header].No_,;
[TIARA$Sales Invoice Header].[Sell-to Customer No_],;
[TIARA$Sales Invoice Header].[Bill-to Name],;
[TIARA$Sales Invoice Header].[Bill-to Address],;
[TIARA$Sales Invoice Header].[Posting Date],;
[TIARA$Sales Invoice Header].[VAT Registration No_],;
[TIARA$Sales Invoice Header].[External Document No_],;
[TIARA$Sales Invoice Header].[Shortcut Dimension 1 Code],;
[TIARA$Sales Invoice Header].[Bill-to City],;
[TIARA$Sales Invoice Line].No_,;
[TIARA$Sales Invoice Line].Description,;
[TIARA$Sales Invoice Line].[Unit of Measure],;
[TIARA$Sales Invoice Line].Quantity,;
[TIARA$Sales Invoice Line].[Unit Price],;
[TIARA$Sales Invoice Line].[VAT %],;
[TIARA$Sales Invoice Line].[Line Discount %],;
[TIARA$Sales Invoice Line].[Amount Including VAT],;
[TIARA$Sales Invoice Line].[Invoice Price],;
[TIARA$Sales Invoice Line].Amount,;
TIARA$Item.[Manufacturer Code],;
TIARA$Customer.[Territory Code],;
[TIARA$Sales Shipment Header].Note,;
[TIARA$Sales Shipment Header].[Order No_],;
[TIARA$Sales Shipment Header].No_;
FROM [TIARA$Sales Invoice Header];
LEFT OUTER JOIN [TIARA$Sales Invoice Line];
ON [TIARA$Sales Invoice Header].No_ = [TIARA$Sales Invoice Line].[Document No_];
LEFT OUTER JOIN TIARA$Item;
ON [TIARA$Sales Invoice Line].No_ = TIARA$Item.No_;
LEFT OUTER JOIN TIARA$Customer;
ON [TIARA$Sales Invoice Line].[Sell-to Customer No_] = TIARA$Customer.No_;
LEFT OUTER JOIN [TIARA$Sales Shipment Header];
ON [TIARA$Sales Invoice Header].[Order No_] = [TIARA$Sales Shipment Header].[Order No_];
WHERE [TIARA$Sales Invoice Header].No_ = THISFORM.TEXT1.TEXT;
ORDER BY [TIARA$Sales Invoice Header].[Posting Date] DESC;
INTO TABLE c:\tiarasys\temp\invoice")
SQLEXEC(lnConn4,"Select [Posting Date],;
[Bill-to Name],;
[VAT Registration No_],;
[Bill-to City],;
No_,;
[Order No_],;
[Bill-to Address],;
[Shortcut Dimension 1 Code];
from [TIARA$Sales Invoice Header];
where No_ = ?inv;
order by [Posting Date] desc")
COPY TO C:\Tiarasys\Temp\invoice_header
SELECT 1
USE C:\Tiarasys\Temp\invoice_header EXCLUSIVE ALIAS invoice_header
orno = Order_no_
SQLEXEC(lnConn4, "Select [Unit of Measure],;
Quantity,;
No_,;
[Document No_],;
[Unit Price],;
[Sell-to Customer No_],;
Amount,;
[Amount Including VAT] from [TIARA$Sales Invoice Line];
where [Document No_] = ?inv")
COPY TO C:\Tiarasys\Temp\invoice_line
SELECT 2
USE C:\Tiarasys\Temp\invoice_line SHARED ALIAS invoice_line
sellno = Sell_to_cu
num = No_
SQLEXEC(lnConn4, "Select No_,;
[Manufacturer Code] from TIARA$Item;
where No_ = ?num")
COPY TO C:\Tiarasys\Temp\items
SELECT 3
USE C:\Tiarasys\Temp\items EXCLUSIVE ALIAS items
SQLEXEC(lnConn4, "Select No_,;
[Territory Code] from TIARA$Customer;
where No_ = ?sellno")
COPY TO C:\Tiarasys\Temp\customer
SELECT 4
USE C:\Tiarasys\Temp\customer EXCLUSIVE ALIAS customer
SQLEXEC(lnConn4, "Select No_,;
[Order No_],;
Note,;
[Sell-to Customer No_] from [TIARA$Sales Shipment Header];
where[Order No_] = ?orno")
COPY TO C:\Tiarasys\Temp\shipment_header
SELECT 5
USE C:\Tiarasys\Temp\shipment_header EXCLUSIVE ALIAS shipment_header