sqlsql-serverodbcvisual-foxpro

Command contains unrecognized phrase/keyword in visual fox pro9


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")

Solution

  • 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