Dear Team,
I’m getting error while pivoting the Purchase invoice data. Kindly suggest the correct syntax for pivot table.
select tabPurchase Invoice
.name
as “Invoice No::100”, tabPurchase Invoice
.posting_date
as “P Date:Date:100”, tabPurchase Invoice
.supplier
as “Supplier::150”, tabSupplier
.pan_no
as “PAN No::100”, tabSupplier
.tax_id
as “TIN No::100”, tabPurchase Invoice
.address_display
as “Address::150”, tabPurchase Invoice
.credit_to
as “Acc Type::100”, tabPurchase Invoice
.mode_of_payment
as “Mode::100”, tabPurchase Invoice
.bill_no
as “Bill No::100”, tabPurchase Invoice
.bill_date
as “Bill Date:Date:100”, tabPurchase Invoice
.reference_id
as “PO No::100”, tabPurchase Invoice
.currency
as “Currency::100”, tabPurchase Invoice
.base_net_total
as “Net Total”, tabPurchase Taxes and Charges
.tax_amount
as “Tax Amount::120”, tabPurchase Taxes and Charges
.account_head
as “Acc Head::120”, tabPurchase Invoice
.base_grand_total
as “Grand Total::100”, tabPurchase Invoice
.outstanding_amount
as “Outstanding::100” from tabPurchase Invoice
, tabSupplier
, tabPurchase Taxes and Charges
where tabPurchase Invoice
.supplier
= tabSupplier
.name
and tabPurchase Invoice
.name
= tabPurchase Taxes and Charges
.parent
and tabPurchase Invoice
.docstatus = 1 order by tabPurchase Invoice
.posting_date
desc pivot tabPurchase Taxes and Charges
.tax_amount
by tabPurchase Taxes and Charges
.account_head
in (select tabPurchase Taxes and Charges
.account_head
from tabPurchase Taxes and Charges
)