Why is this SQL Query saying "Command not properly ended" when I try to execute it?

Question

     SELECT
"VENDER"."ALIAS",
"VENDERINVOICE"."INVOICENO", 
"VENDERINVOICE"."INVDATE", 
"VENDERINVOICE"."AMOUNT", 
"PO"."PONUMBER",
"VENDERINVOICE"."POSTDATE" AS "PAY DATE",
"VENDERINVOICE"."POSTDATE" AS "DISTRIBUTION",
"PO"."CALLSLIPNO",
case PO.SUBIND
        when 'Y' then 'SU'
        when 'N' then 'MA'
    end as SMCOST,
"PO"."JOBTYPEID",
"VENDERINVOICE"."NET_AMOUNT",
(("TAXCODE"."TAXRATE" + 5)*.01)*"VENDERINVOICE"."NET_AMOUNT" - "VENDERINVOICE"."SALES_TAX_AMOUNT" "ACCRUE AMMOUNT",
"PO"."TAXCODE"

 FROM   
    ((("STARPO"."VENDERPOITEM" "VENDERPOITEM" INNER JOIN "STARPO"."PO" "PO" ON "VENDERPOITEM"."PO_KEY"="PO"."PO_KEY") INNER JOIN "STARPO"."VENDERINVOICE" 

"VENDERINVOICE" ON "VENDERPOITEM"."VENDERINVOICE_KEY"="VENDERINVOICE"."VENDERINVOICE_KEY") INNER JOIN "STARPO"."VENDER" "VENDER" ON 

"VENDERINVOICE"."VENDER_KEY"="VENDER"."VENDER_KEY") INNER JOIN "STARPO"."TAXCODE" "TAXCODE" ON "PO"."TAXCODE"="TAXCODE"."TAXCODE"

    GROUP BY
 "VENDER"."ALIAS",
"VENDERINVOICE"."INVOICENO", 
 "VENDERINVOICE"."INVDATE", 
 "VENDERINVOICE"."AMOUNT", 
 "PO"."PONUMBER",
 "VENDERINVOICE"."POSTDATE" AS "PAY DATE",
 "VENDERINVOICE"."POSTDATE" AS "DISTRIBUTION"

 WHERE  
    "VENDERINVOICE"."INVDATE">= :START_DATE
     AND "VENDERINVOICE"."INVDATE" <= :END_DATE
     AND "PO"."PONUMBER" <> 100256

The syntax seems invalid with all these quotes around all table names and field names. — Jocelyn, Jul 15 '16 at 16:26
I know "to each their own" but doesn't all that uppercase just fry out your eyes by 2pm? — Drew, Jul 15 '16 at 16:36
What sgeddes said; and I pretty sure `AS` is not allowed in `GROUP BY` clauses. — Uueerdo, Jul 15 '16 at 16:47
@Drew Don't you have armored eyes after so much time on Stackoverflow? — Jocelyn, Jul 15 '16 at 16:55

score 0 · Answer 1 · answered Jul 15 '16 at 17:50

Based on some of the comments, and trying to format in a bit more standard way (I know there are many standards...).

Removed quotes
Moved WHERE to the correct place
Removed unnecessary parenthesis from JOINs (were they necessary?)
Moved AS to SELECT clause

Try this:

SELECT
VENDER.ALIAS,
VENDERINVOICE.INVOICENO, 
VENDERINVOICE.INVDATE, 
VENDERINVOICE.AMOUNT, 
PO.PONUMBER,
VENDERINVOICE.POSTDATE AS PAY DATE,
VENDERINVOICE.POSTDATE AS DISTRIBUTION,
PO.CALLSLIPNO,
case PO.SUBIND
        when 'Y' then 'SU'
        when 'N' then 'MA'
    end as SMCOST,
PO.JOBTYPEID,
VENDERINVOICE.NET_AMOUNT,
((TAXCODE.TAXRATE + 5)*.01)*VENDERINVOICE.NET_AMOUNT - VENDERINVOICE.SALES_TAX_AMOUNT ACCRUE AMMOUNT,
PO.TAXCODE

 FROM STARPO.VENDERPOITEM VENDERPOITEM 
 INNER JOIN STARPO.PO PO 
    ON VENDERPOITEM.PO_KEY = PO.PO_KEY
 INNER JOIN STARPO.VENDERINVOICE VENDERINVOICE 
    ON VENDERPOITEM.VENDERINVOICE_KEY = VENDERINVOICE.VENDERINVOICE_KEY
 INNER JOIN STARPO.VENDER VENDER 
    ON VENDERINVOICE.VENDER_KEY = VENDER.VENDER_KEY
 INNER JOIN STARPO.TAXCODE TAXCODE 
    ON PO.TAXCODE=TAXCODE.TAXCODE

 WHERE VENDERINVOICE.INVDATE >= :START_DATE
    AND VENDERINVOICE.INVDATE <= :END_DATE
    AND PO.PONUMBER <> 100256

 GROUP BY
     VENDER.ALIAS,
     VENDERINVOICE.INVOICENO, 
     VENDERINVOICE.INVDATE, 
     VENDERINVOICE.AMOUNT, 
     PO.PONUMBER,
     VENDERINVOICE.POSTDATE

Why is this SQL Query saying "Command not properly ended" when I try to execute it?

1 Answers1