Insert Data into temp Table

Question

Hi

Below data i want to be inserted into Temp table.

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

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');

Thanks

Jignesh Kumar · Answer

You can use Select colum1 columemn2 into #TempTable from ActualTableName 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 INTO TempResultTable 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 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'); To select records from temporary table. select * from TempResultTable

Jayraj Chhaya · Answer

Hello Ramco Ramco To insert the specified data into a temporary table you can utilize the following SQL statement. This query combines results from the OJDT and OPCH tables using a UNION ALL operation ensuring that the data is filtered according to the provided conditions. Here’s how you can structure your SQL command: INSERT INTO TempTable (Vendor Code Vendor Name BPLName Document No TaxDate Document Type Debit Amount Credit Amount) 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 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'); This command effectively gathers the required data and inserts it into the TempTable  ensuring that all conditions are met for accurate data representation.

Jaimin Shethiya · Answer

Hello Ramco Please use below query SELECT * INTO #tmp FROM ( 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 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'); ) AS A SELECT * FROM #tmp; DROP TABLE #tmp; Thanks