Heading Description

Insted inner query apply joins and in select statement take the fields you want from alias name of table

Hello,

SELECT
    A."Type",
    A."SeriesName",
    A."DocNum",
    A."DocEntry",
    A."CardCode",
    A."CardName",
    A."DocDate",
    A."SecCode",
    A."SecDesc",
    A."TaxbleAmt",
    A."TaxRate",
    A."WTAmt",
    A."PAN NO",
    "Branch",
    CAST((SELECT "Location" FROM OLCT WHERE "Code" = A."Location") AS NVARCHAR(255)) AS "Location"
FROM (
    SELECT 
        'AP Invoice' AS "Type",
        T4."SeriesName",
        T0."DocNum",
        (SELECT "BPLName" FROM OBPL WHERE "BPLId" = T0."BPLId") AS "Branch",
        (SELECT DISTINCT("LocCode") FROM PCH1 WHERE "DocEntry" = T0."DocEntry") AS "Location"
    FROM 
        OPCH T0
    LEFT JOIN 
        PCH5 T1 ON T0."DocEntry" = T1."AbsEntry"
    LEFT JOIN 
        NNM1 T4 ON T4."Series" = T0."Series"
    WHERE 
        T1."Rate" > 0
) A;

Hi Jignesh

  Instead of displayig Location it is displaying the name of field in Table

Thanks

Hello Ramco,

First of all write your query in readable format it will be easier for you to identify issue and easy to understand for other developer as well,

SELECT
    A."Type",
    A."SeriesName",
    A."DocNum",
    A."DocEntry",
    A."CardCode",
    A."CardName",
    A."DocDate",
    A."SecCode",
    A."SecDesc",
    A."TaxbleAmt",
    A."TaxRate",
    A."WTAmt",
    A."PAN NO",
    "Branch",
    (SELECT "Location" FROM OLCT WHERE "Code" = A."Location") AS "Location"
FROM (
    SELECT 
        'AP Invoice' AS "Type",
        T4."SeriesName",
        T0."DocNum",
        (SELECT "BPLName" FROM OBPL WHERE "BPLId" = T0."BPLId") AS "Branch",
        (SELECT DISTINCT("LocCode") FROM PCH1 WHERE "DocEntry" = T0."DocEntry") AS "Location"
    FROM 
        OPCH T0
    LEFT JOIN 
        PCH5 T1 ON T0."DocEntry" = T1."AbsEntry"
    LEFT JOIN 
        NNM1 T4 ON T4."Series" = T0."Series"
    WHERE 
        T1."Rate" > 0
) A;