Calculate differences between two columns of two different tables

Question

I want to calculate the difference between purchase order amount and purchase invoice amount. But I am not able to fetch the purchase invoice amount i.e. "pi.grand_total" and hence also not able to fetch the difference of "(pi.grand_total - po.grand_total)" . Please help.Below is my query. PO = Purchase Order PI = Purchase Invoice

 SELECT DISTINCT
    po.name AS "PO #:Link/Purchase Order:120",
    po.supplier AS "Supplier:Link/Supplier:120",
    po.Company AS "Company:Data:120",
    po.currency AS "Currency:Link/Currency:120",
    po.base_grand_total AS "Grand Total:Currency:120",
    po.status AS "Status:Data:120",
    po.per_received AS "Per Received:Data:120",
    CEILING(po.per_billed) AS "Per Billed:Data:120",
    po.delivery_date AS "Delivery Date:Date:120",
    pi.grand_total AS "Final PI Total:120",
    (pi.grand_total - po.grand_total) AS "Amount Difference:120"
    FROM
    "tabPurchase Order" as po
    LEFT JOIN "tabPurchase Invoice" as pi ON po.name = pi.parent
    WHERE

    po.per_received = 100
    AND
    CEILING(po.per_billed) < 100
    ORDER BY po.delivery_date ASC

What does this mean: "But I am not able to fetch" can you explain little bit more ? Also, what is your database ? — VBoka, Feb 26 '20 at 07:25
Please attach RDBMS tag to your question and also explain what are po.name and pi.parent in join condition ? — Pankaj_Dwivedi, Feb 26 '20 at 08:01
@Pankaj_Dwivedi po = Purchase order and pi = Purchase Invoice. Purchase order is one table and Purchase invoice is another table. — Ashutosh Halape, Feb 26 '20 at 08:06
@VBoka I am not getting any values for the column pi.grand_total. Can you help me?? — Ashutosh Halape, Feb 26 '20 at 08:14
@AshutoshHalape - No need for double question mark "??". If you are not getting any values for that column please provide the tables structures so we can check if this condition `LEFT JOIN "tabPurchase Invoice" as pi ON po.name = pi.parent` is ok. Before that remove all conditions from where clause and see if then you will get any results. — VBoka, Feb 26 '20 at 08:19
First confirm that this will return a value : `select pi.grand_total FROM "tabPurchase Order" as po LEFT JOIN "tabPurchase Invoice" as pi ON po.name = pi.parent` — VBoka, Feb 26 '20 at 08:24
Hi @Pankaj_Dwivedi why are you telling me this? I have just copy pasted OP's code and asked him a question based on that code ... Have you read my whole comment ? — VBoka, Feb 26 '20 at 08:26
From use of CEILING function i can guess either you are using SQL server or Postgree. Just remove your where clause and check if you are getting any data from your query. If you get the data add on condition in where clause ie. po.per_received = 100. And if you get data again then add one more filter in your AND clause to see. @Ashutosh — Pankaj_Dwivedi, Feb 26 '20 at 08:37
@VBoka I tried this : select pi.grand_total FROM "tabPurchase Order" as po LEFT JOIN "tabPurchase Invoice" as pi ON po.name = pi.parent I am not getting any values. — Ashutosh Halape, Feb 26 '20 at 10:29
@VBoka no errors. I am not understanding what's wrong with the query. Is my column name wrong or the table name is wrong. Got no idea — Ashutosh Halape, Feb 26 '20 at 11:03
Please can you look at the data in the column po.name and compare it to the data from the column pi.parent ? Is there any data that is the same ? — VBoka, Feb 26 '20 at 11:05

ismetguzelgun · Answer 1 · 2020-02-26T08:27:35.737

You are using LEFT JOIN. This means when your second table has no data which matches with your first table you will receive no data from second table but nonetheless your first table will return all of its data.

You probably should check your join condition. If you wanted to join these two tables the way you want then use NVL Function for pi.grand_total column. Because it is on the left join it could have a NULL value thus NULL minus po.grand_total will give you NULL. NVL function turns NULL values into intended values like NVL(pi.grand_total,0)

A good example how joins work

Calculate differences between two columns of two different tables

1 Answers1