Incorrect Syntax near Union

Hello Ramco,

You have not mentioned a proper alias name.

If you are trying to use union and union all at that time, you should provide the proper alias name so SQL is aware of the columns and, based on that, generate the result and show it in the result window.

Please use below query

SELECT 'a' AS "Vendor Code",
    'b' AS "Vendor Name",
    'b' AS "Branch Name",
    'd' AS "Document Number",
    'e' AS "Document Date",
    'Opening Balance' AS "Document Type",
    0 AS "Debit Amount",
    0 AS "Credit Amount"

Union all 

SELECT T3."ShortName" AS "Vendor Code",
(Select Max("CardName") from OCRD where "CardCode" = T3."ShortName") AS "Vendor Name",
T3."BPLName" AS "Branch Name", 
T2."BaseRef" AS "Document Number", 
T2."TaxDate" AS "Document Date",
(CASE WHEN T3."TransType"='204' THEN 'A/P Down Payment Invoice' WHEN T3."TransType"='46' THEN 'Outgoing Payment' WHEN T3."TransType"='18' THEN 'A/P Invoice'  ELSE ' 'END) AS "Doc. Type" AS "Document Type",
T3."Debit" AS "Debit Amount", 
T3."Credit" AS  "Credit Amount"
FROM "OJDT"  T2 INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" WHERE 
(T3."ShortName"=:BpCode)


UNION ALL

SELECT T2."CardCode" AS "Vendor Code",
T2."CardName" AS "Vendor Name",
T2."BPLName" AS "Branch Number",
T2."DocNum" AS "Document Number", 
T2."DocDate" AS "Document Date", 
'A/p Invoice', AS "Document Type",
0 AS "Debit Amount", 
(T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS  "Credit Amount"
FROM "OPCH" T2
WHERE T2."DocDate" >= TO_DATE(:FromDate) and T2."DocDate" <= TO_DATE(:ToDate)
AND (T2."CardCode"=:BpCode ) AND T2."CANCELED" not in ('Y','C')

Your alias name miss matches in all three select statements, Please check the same column name for all select statements

-- Placeholder row
SELECT 
    'a' AS "Vendor Code",
    'b' AS "Vendor Name",
    'b' AS "BPLName",
    'd' AS "Document No",
    'e' AS "TaxDate",
    'Opening Balance' AS "Document Type",
    0 AS "Debit Amount",
    0 AS "Credit Amount"

UNION ALL 

-- Transaction details from Journal Entries
SELECT 
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."BPLName",
    T2."BaseRef" AS "Document No",
    T2."TaxDate" As "TaxDate",
    CASE 
        WHEN T3."TransType" = '204' THEN 'A/P Down Payment Invoice'
        WHEN T3."TransType" = '46' THEN 'Outgoing Payment'
        WHEN T3."TransType" = '18' THEN 'A/P Invoice'
        ELSE ' '
    END AS "Document Type",
    T3."Debit" AS "Debit Amount",
    T3."Credit" AS "Credit Amount"
FROM 
    "OJDT" T2
INNER JOIN 
    "JDT1" T3 ON T2."TransId" = T3."TransId"
WHERE 
    T3."ShortName" = :BpCode

UNION ALL

-- A/P Invoices
SELECT 
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name",
    T2."BPLName" AS "BPLName",
    T2."DocNum" AS "Document No",
    T2."DocDate" AS "TaxDate",
    'A/P Invoice' AS "Document Type",
    0 AS "Debit Amount",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Credit Amount"
FROM 
    "OPCH" T2
WHERE 
    T2."DocDate" BETWEEN TO_DATE(:FromDate, 'YYYY-MM-DD') AND TO_DATE(:ToDate, 'YYYY-MM-DD')
    AND T2."CardCode" = :BpCode
    AND T2."CANCELED" NOT IN ('Y', 'C');

Hi Jignesh

  I have writtten below code but it is giving error Incorrect Syntax near Union

SELECT 'a' AS "Vendor Code",
    'b' AS "Vendor Name",
    'b' AS "Branch Name",
    'd' AS "Document Number",
    'e' AS "Document Date",
    'Opening Balance' AS "Document Type",
    0 AS "Debit Amount",
    0 AS "Credit Amount"

Union all 

SELECT T3."ShortName" AS "Vendor Code",(Select Max("CardName") from OCRD where "CardCode" = T3."ShortName") AS "Vendor Name",T3."BPLName", T2."BaseRef" AS "Document No", T2."TaxDate",
(CASE WHEN T3."TransType"='204' THEN 'A/P Down Payment Invoice' WHEN T3."TransType"='46' THEN 'Outgoing Payment' WHEN T3."TransType"='18' THEN 'A/P Invoice'  ELSE ' 'END) AS "Doc. Type",T3."Debit", T3."Credit" 
FROM "OJDT"  T2 INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" WHERE 
(T3."ShortName"=:BpCode)


UNION ALL

SELECT T2."CardCode" AS "Vendor Code",T2."CardName" AS "Vendor Name",T2."BPLName",T2."DocNum", T2."DocDate", 'A/p Invoice',0,(T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat")
FROM "OPCH" T2
WHERE T2."DocDate" >= TO_DATE(:FromDate) and T2."DocDate" <= TO_DATE(:ToDate)
AND (T2."CardCode"=:BpCode ) AND T2."CANCELED" not in ('Y','C')

Thanks

Hello Ramco,

You need to give n alias to the last two columns, and assign 0 as the default values

'a' AS "Vendor Code",
    'b' AS "Vendor Name",
    'b' AS "Branch Name",
    'd' AS "Document Number",
    'e' AS "Document Date",
    'Opening Balance' AS "Document Type",
    0 AS "Debit Amount",
    0 AS "Credit Amount"

Hi jignesh

  What is wrong in this below select

SELECT 'a' AS "Vendor Code",'b' AS "Vendor Name",'b','d', 'e', 'Opening Balance',v_opgDebit,v_opgCredit

Union All

Thanks

Then you can put some default values for that

Hi Jignesh

  First select statement data is not from any table

Thanks

Hello Ramco,

Please make your query more readable and consistent naming convension for columns will help to maintain quarey,

SELECT 
    'a' AS "Vendor Code",
    'b' AS "Vendor Name",
    'b' AS "Branch Name",
    'd' AS "Document Number",
    'e' AS "Document Date",
    'Opening Balance' AS "Document Type",
    v_opgDebit AS "Debit Amount",
    v_opgCredit AS "Credit Amount"
FROM DUMMY -- Replace DUMMY with a relevant source or table if necessary

UNION ALL

SELECT 
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name",
    T2."BPLName" AS "Branch Name",
    T2."DocNum" AS "Document Number",
    T2."DocDate" AS "Document Date",
    'A/P Invoice' AS "Document Type",
    0 AS "Debit Amount",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Credit Amount"
FROM "OPCH" T2
WHERE 
    T2."DocDate" >= TO_DATE(:FromDate) 
    AND T2."DocDate" <= TO_DATE(:ToDate)
    AND T2."CardCode" = :BpCode;