Hi
Yes, tUnite need the to have the same schema for the 2 input, the output will depends on the schema of tUnite if you ignore this, the output will union all the records from the 2 input, but some of columns will empty if input don't have these columns. For example:
input1:
id;name
1;shong
2;elise
3;mike
input2:
id
4
5
6
the schema of tUnite has two column: id, name
output:
id;name
1;shong
2;elise
3;mike
4;
5;
6;
Please note that the data type of column should be same, othewise, it has compilation error.
Best regards
Shong
Hi shong,
Based on my question , Is it possible that they have different schema.
This is actually i want to apply in JasperETL
I have two select statement and I'm going to union all that two result of the two sql statement.
Hi
No, tUnite requires all the inputs should have the same schema. Where will you store the result after merging the two results? File? If so, you can append the records into a exiting file.
Best regards
Shong
Hi
UNION/UNION ALL requires the two select statement should have the same columns and data type!
tUnite can fit your need, yo can filter columns so that they have same columns before tUnite, merge the records from the two tables and insert them into a target table. Finally, execute the main select statement. The job looks like:
tMysqlInput_1--main--tFilterColmn---tUnite---tMysqlOutput_1(merge all the records and insert into table tbl)
|
tFilterColumn
|
tMysqlInput_2
|
onsubjobok
|
tMysqlInput_3
On tMysqlInput_3:
"
SELECT //HERE IS MY MAIN SQL STATEMENT
.
.
.
FROM
tbl
......
LEFT JOIN
.......
WHERE
.......
ORDER BY
......"
Best regards
Shong
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?
Hi
Yes, tUnite need the to have the same schema for the 2 input, the output will depends on the schema of tUnite if you ignore this, the output will union all the records from the 2 input, but some of columns will empty if input don't have these columns. For example:
input1:
id;name
1;shong
2;elise
3;mike
input2:
id
4
5
6
the schema of tUnite has two column: id, name
output:
id;name
1;shong
2;elise
3;mike
4;
5;
6;
Please note that the data type of column should be same, othewise, it has compilation error.
Best regards
Shong
Shong, What if in the process the data type is changed for example in my case the transaction_id was an integer and after executing the statement.
It changed to an object data type.
Having a hard time @_@ getting the right job for my statement.
Shong - how can I do a union all on 2 inputs using the tELTMap component? I'm trying to use this instead of a tmap to push the processing down to Redshift
