Thanks Shong, Sorry I understand what you're trying to say. It's just I have a different situation well actually the statement for union goes like this that keeps me
knowing if it's possible with different schema.
#################################################################
-- --------------------
-- CASH/CREDIT CUSTOMER
-- --------------------
SELECT
GROUP_CONCAT(st.transaction_id SEPARATOR ',') AS transaction_id,
st.shift_report_id,
case
when st.payment_mode='CASH' then 'CASH CUSTOMER'
when st.payment_mode='CREDIT' then 'CREDIT CARD CUSTOMER'
end AS customer_name,
'NOT APPLICABLE' AS customer_ref,
NULL AS ref_number,
NULL AS ref_plate_number,
st.transaction_type,
st.payment_mode,
st.pump_code,
st.item_code,
st.product_name,
st.unit_price,
sum(st.quantity) AS quantity,
sum(
case
when st.lb_discount>0
then ( st.lb_discount * st.quantity * -1 )
end
) AS lb_discount,
NULL AS other_discount_type,
NULL AS other_discount,
NULL AS ar_from_spi
FROM
st_transactions st
UNION ALL
-- ------------------
-- NONE CASH CUSTOMER
-- ------------------
SELECT
st.transaction_id,
st.shift_report_id,
case
when st.transaction_type='GIFT CARD'
OR st.transaction_type='FLEET CARD'
OR st.transaction_type='VIP REDEMPTION'
OR st.transaction_type='EMPLOYEE GAS UP'
OR st.transaction_type='HAULER GAS UP'
OR st.transaction_type='MISFILL'
OR st.transaction_type='OVERFILL'
then 'CASH CUSTOMER'
when ( st.transaction_type='KEY ACCOUNT'
OR st.transaction_type='SALE' )
AND st.payment_mode='CASH'
then CONCAT(ucase(st.ref_name_1),'-CASH')
when ( st.transaction_type='KEY ACCOUNT'
OR st.transaction_type='SALE' )
AND st.payment_mode='CREDIT CARD'
then CONCAT(ucase(st.ref_name_1),'-CREDIT CARD')
else ucase(st.ref_name_1)
end AS customer_name,
.
.
.
st.payment_mode,
st.pump_code,
st.item_code,
st.product_name,
st.unit_price,
st.quantity,
case
when st.lb_discount>0
then ( st.lb_discount * st.quantity * -1 )
end AS lb_discount,
case
when st.other_discount>0
AND st.transaction_type='KEY ACCOUNT'
AND st.payment_mode!='CASH'
then 'KEY ACCOUNT DISCOUNT'
when st.other_discount>0
AND st.payment_mode='CASH'
AND (
LEFT(st.item_code,3)='FUE'
OR LEFT(st.item_code,3)='LPG'
)
then 'FUELS DISCOUNT'
when st.other_discount>0
AND st.payment_mode='CASH'
AND LEFT(st.item_code,3)='LUB'
then 'LUBES DISCOUNT'
end AS other_discount_type,
case
when st.other_discount>0
then ( st.other_discount * st.quantity * -1 )
end AS other_discount,
case
when st.transaction_type='GIFT CARD'
OR st.transaction_type='FLEET CARD'
OR st.transaction_type='VIP REDEMPTION'
OR st.transaction_type='EMPLOYEE GAS UP'
OR st.transaction_type='HAULER GAS UP'
OR st.transaction_type='MISFILL'
OR st.transaction_type='OVERFILL'
OR st.transaction_type='SPONSORSHIP'
then ( ( st.unit_price - st.lb_discount - st.other_discount ) * st.quantity * -1 )
end AS ar_from_spi
FROM
st_transactions st
#######################################################################
Anyways Again,
thank you very much shong, In some way your layout is like in my draft plan.
And oh btw, I'm trying the job and it seems in tUnite needs to have same schema for the 2 input it still works and has an output. Any ideas what may happen if I ignore this?
