topic Re: [resolved] Union All in tMap in Talend Studio

[resolved] Union All in tMap

Anonymous — Sat, 16 Nov 2024 12:56:52 GMT

How can I Union All the two tables in tMap?

Re: [resolved] Union All in tMap

Anonymous — Tue, 03 May 2011 07:27:59 GMT

Hi
Try the tUnite component, eg:
tMysqlInput_1--main--tUnite--tMap
|
tMysqlInput_2
Best regards
Shong

Re: [resolved] Union All in tMap

Anonymous — Tue, 03 May 2011 08:25:26 GMT

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.

Re: [resolved] Union All in tMap

Anonymous — Wed, 04 May 2011 06:23:09 GMT

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

Re: [resolved] Union All in tMap

Anonymous — Wed, 04 May 2011 07:58:24 GMT

Thanks shong,
I'll be using the output again to a select statement. Here's what I am trying to pertain for visualization.

SELECT //HERE IS MY MAIN SQL STATEMENT
.
.
.
FROM
(
SELECT // This is my first table
. // my first tInputDB
.
.
FROM
......

UNION ALL // Here what will I use here? tMap or tUnite???

SELECT //This is my second table
. // my second InputDB
.
.
FROM
............
) tbl
FROM
...
LEFT JOIN
.......
WHERE
.......
ORDER BY
......

They have different schema so in that case tUnite is not an option ,I hope you get the hit, Help me out.
Thanks,
-D

Re: [resolved] Union All in tMap

Anonymous — Wed, 04 May 2011 08:13:13 GMT

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

Re: [resolved] Union All in tMap

Anonymous — Wed, 04 May 2011 08:42:10 GMT

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?

Re: [resolved] Union All in tMap

Anonymous — Thu, 05 May 2011 03:25:27 GMT

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

Re: [resolved] Union All in tMap

Anonymous — Thu, 05 May 2011 03:56:42 GMT

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.

Re: [resolved] Union All in tMap

bradsheridan — Fri, 28 Dec 2018 17:24:53 GMT

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

Thanks