help my final project on oracle database. i'm struggling now. anyone help me please.
this is my query
SELECT tr.id_kasir,
tr.tgl_transaksi,
sum (
SELECT CASE tr.id_member when 'M000'
then SUM ( mn.harga_menu * st.qty_menu)
+ SUM(mn.harga_menu*st.qty_menu) * 0.1
- nvl2 ( pro.discount,
sum(mn.harga_menu * st.qty_menu) * (pro.discount/100),
0)
ELSE SUM(mn.harga_menu*st.qty_menu)
- SUM( mn.harga_menu * st.qty_menu) * 0.05
+ SUM(mn.harga_menu*st.qty_menu) * 0.1
- nvl2 ( pro.discount, sum(mn.harga_menu * st.qty_menu) * (pro.discount/100) , 0)
END Setoran
FROM transaksi tr
join struk st on (st.no_transaksi = tr.no_transaksi)
join menu mn on (st.kode_menu = mn.kode_menu )
left outer join promo pro on (pro.kode_menu = mn.kode_menu)
GROUP BY tr.id_kasir, tr.tgl_transaksi, tr.id_member,pro.discount
ORDER BY tr.tgl_transaksi, tr.id_kasir) setoran
from transaksi tr
group by tr.id_kasir, tr.tgl_transaksi
I have no idea what this query is trying but I see a few issues there. E.g. you can't use the ORDER BY
inside SUM()
, you can't use the SUM
inside CASE
this way...
Let's try fixing it by breaking the query... start with the inner part. Does the following query work?
SELECT Sum( CASE tr.id_member WHEN 'M000'
then ( mn.harga_menu * st.qty_menu)
+(mn.harga_menu*st.qty_menu) * 0.1
- nvl2 ( pro.discount,
(mn.harga_menu * st.qty_menu) * (pro.discount/100),
0)
ELSE (mn.harga_menu*st.qty_menu)
- ( mn.harga_menu * st.qty_menu) * 0.05
+ (mn.harga_menu*st.qty_menu) * 0.1
- nvl2 ( pro.discount, (mn.harga_menu * st.qty_menu) * (pro.discount/100) , 0)
) Setoran
FROM transaksi tr
join struk st on (st.no_transaksi = tr.no_transaksi)
join menu mn on (st.kode_menu = mn.kode_menu )
left outer join promo pro on (pro.kode_menu = mn.kode_menu)
GROUP BY tr.id_kasir, tr.tgl_transaksi, tr.id_member,pro.discount
If the above query works, you can easily wrap it within the outer query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments