[SOLVED] Help with query report

ERPNext Reports
1

hey guys,

I’m trying make a custom query report, but I’m running into errors. Can anyone see where I’m going wrong? Writing SQL Queries is new to me, but I’m seeing it’s very important

SELECT
    `tabPurchase Receipt`.`name` as "Purchase Receipt:Link/Delivery Note:120",
    `tabPurchase Receipt`.`supplier` as "Supplier:Link/Customer:120",
    `tabPurchase Receipt Item`.`item_code` as "Item:Link/Item:100",
    `tabPurchase Receipt Item`.`quality_inspection` as "Quality Inspection:Link/Quality Inspection:120",
    `tabQuality Inspection`.`docstatus` - "Status:70"
    
FROM 
    `tabPurchase Receipt` JOIN `tabPurchase Receipt Item`,
    LEFT JOIN `tabQuality Inspection` ON (
        `tabQuality Inspection`.item_code = `tabPurchase Receipt Item`.item_code 
        AND `tabQuality Inspection`.purchase_receipt_no = `tabPurchase Receipt`.`name`)
WHERE  
ORDER BY
    `tabPurchase Receipt`.`name` desc
2

Try this:

SELECT
    `tabPurchase Receipt`.`name` as "Purchase Receipt:Link/Purchase Receipt:120",
    `tabPurchase Receipt`.`supplier` as "Supplier:Link/Supplier:120",
    `tabPurchase Receipt Item`.`item_code` as "Item:Link/Item:100",
    `tabPurchase Receipt Item`.`qa_no` as "Quality Inspection:Link/Quality Inspection:120",
    `tabQuality Inspection`.`docstatus` as "Status::70"
FROM 
    `tabPurchase Receipt` JOIN `tabPurchase Receipt Item` 
    LEFT JOIN `tabQuality Inspection` ON (
        `tabQuality Inspection`.item_code = `tabPurchase Receipt Item`.item_code 
        AND `tabQuality Inspection`.purchase_receipt_no = `tabPurchase Receipt`.`name`)
WHERE `tabPurchase Receipt`.`name` = `tabPurchase Receipt Item`.`parent`
ORDER BY
    `tabPurchase Receipt`.`name` desc
3 Likes
3

@cpurbaugh,

You are using where statement without passing any condition in it.
You must have to pass at least one condition in where clause to use it.

Thank You

1 Like
4

@nabinhait Thanks, that works perfectly!

@Divyesh_Amreliya I see what you are saying, I’ll keep that in mind!

Thanks to both of you!

1 Like