How would I create a sales receipt from Oracle Apex SQL Workshop? I have to use data from SalesItem, Product and Transactions to do this

Question

I must print out a "receipt" from a sales transaction that already occurred. I want the total cost in that transaction using multiple select statements.

SELECT TRANSACTIONDATE, PRODUCT.PRODUCTID, (UNITPRICE * QUANTITY) AS TOTAL
FROM SALESITEM, PRODUCT, TRANSACTIONS
WHERE PRODUCT.PRODUCTID = SALESITEM.PRODUCTID
AND TRANSACTIONS.TRANSACTIONID = SALESITEM.TRANSACTIONID
AND TRANSACTIONS.TRANSACTIONID = '106'
GROUP BY ROLLUP(TRANSACTIONDATE, PRODUCT.PRODUCTID)

Do you think a rollup function would work best? My goal is to show the total cost of this transaction plus sales tax.

Answer 1

You could use a CTE

WITH CTE AS (
SELECT TRANSACTIONDATE, PRODUCT.PRODUCTID, (UNITPRICE * QUANTITY) AS TOTAL
FROM SALESITEM, PRODUCT, TRANSACTIONS
WHERE PRODUCT.PRODUCTID = SALESITEM.PRODUCTID
AND TRANSACTIONS.TRANSACTIONID = SALESITEM.TRANSACTIONID
AND TRANSACTIONS.TRANSACTIONID = '106'
)
SELECT * FROM CTE
UNION ALL
SELECT null,null, SUM(TOTAL) * sales_tax_rate_as_decimal
UNION ALL
SELECT null,null, SUM(TOTAL) * ( 1 + sales_tax_rate_as_decimal);