UDx RPC call InvokeProcessPartition()
Getting Failure in UDx RPC call InvokeProcessPartition(): Connection reset by peer
when running the following query
when i remove the last 3 inner queries from the list of Union all inner queries , it works fine ... what can be the issue ?
WITH tch_nok AS (
SELECT date_id, bts_network_sid
,csw_split(tch_frequencies, ',', 30000) OVER (PARTITION BY date_id, bts_network_sid) AS frequency
FROM zksinsprd.cxt_nok_g_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
),
tch_hua AS (
SELECT date_id, network_sid
,csw_split(tch_frequencies, ',', 30000) OVER (PARTITION BY date_id, network_sid) AS frequency
FROM zksinsprd.cxt_hua_g_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
)
,freq AS (
SELECT DISTINCT date_id ,bts_network_sid AS cell_sid,'BCCH' AS frequency_type,bcchno AS frequency_value,technology,vendorFROM zksinsprd.cxt_nok_g_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date AND 1 = 1
UNION ALL
SELECT cxt.date_id ,cxt.bts_network_sid AS cell_sid,'TCH' AS frequency_type,tch_nok.frequency::INT AS frequency_value,technology,vendorFROM zksinsprd.cxt_nok_g_cells cxt
INNER JOIN tch_nok ON cxt.bts_network_sid = tch_nok.bts_network_sid AND cxt.date_id = tch_nok.date_id WHERE cxt.date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,network_sid AS cell_sid,'BCCH' AS frequency_type,bcchno AS frequency_value,technology,vendor
FROM zksinsprd.cxt_hua_g_cells WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT cxt.date_id ,cxt.network_sid AS cell_sid,'TCH' AS frequency_type ,tch_hua.frequency::INT AS frequency_value ,technology ,vendor FROM zksinsprd.cxt_hua_g_cells cxt
INNER JOIN tch_hua ON cxt.network_sid = tch_hua.network_sid AND cxt.date_id = tch_hua.date_id WHERE cxt.date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,wcel_network_sid AS cell_sid ,'PSC' AS frequency_type ,priscrcode AS frequency_value ,technology ,vendor
FROM zksinsprd.cxt_nok_u_cells WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,wcel_network_sid AS cell_sid ,'ARFCN' AS frequency_type ,uarfcn_dl AS frequency_value ,technology ,vendor
FROM zksinsprd.cxt_nok_u_cells WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,network_sid AS cell_sid ,'PSC' AS frequency_type ,pscrambcode AS frequency_value ,technology ,vendor
FROM zksinsprd.cxt_hua_u_cells WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,network_sid AS cell_sid ,'ARFCN' AS frequency_type ,uarfcndownlink AS frequency_value ,technology ,vendor FROM zksinsprd.cxt_hua_u_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,lncel_network_sid AS cell_sid ,'EARFCNUL' AS frequency_type ,lncel_earfcnul AS frequency_value ,technology ,vendor FROM zksinsprd.cxt_nok_l_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id ,lncel_network_sid AS cell_sid ,'EARFCNDL' AS frequency_type ,lncel_earfcndl AS frequency_value ,technology ,vendor
FROM zksinsprd.cxt_nok_l_cells WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id,lncel_network_sid AS cell_sid,'PHYCELLID' AS frequency_type,lncel_phycellid AS frequency_value,technology,vendor
FROM zksinsprd.cxt_nok_l_cellsWHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id,cell_network_sid AS cell_sid,'EARFCNUL' AS frequency_type,cell_earfcnul AS frequency_value,technology,vendor FROM zksinsprd.cxt_hua_l_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id,cell_network_sid AS cell_sid,'EARFCNDL' AS frequency_type,cell_earfcndl AS frequency_value,technology,vendor FROM zksinsprd.cxt_hua_l_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
UNION ALL
SELECT DISTINCT date_id,cell_network_sid AS cell_sid,'PHYCELLID' AS frequency_type,cell_phycellid AS frequency_value,technology,vendor FROM zksinsprd.cxt_hua_l_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
)
SELECT t.* FROM (
SELECT
date_id
,cell_sid
,frequency_type
,LAG(frequency_value, 1) OVER (
PARTITION BY vendor
,technology
,cell_sid
,frequency_type
ORDER BY vendor, technology, cell_sid, frequency_type, frequency_value, date_id
) AS old_frequency
,frequency_value AS new_frequency
,technology
,vendor
FROM freq
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND cell_sid is not null) t
WHERE NVL(old_frequency, -1) != NVL(new_frequency, -1)
AND date_id = '05/10/2016'::date;
Comments
btw the csw_split is a user function
if i run the following query that is using the user defined function .
SELECT date_id, bts_network_sid
,csw_split(tch_frequencies, ',', 30000) OVER (PARTITION BY date_id, bts_network_sid) AS frequency
FROM zksinsprd.cxt_nok_g_cells
WHERE date_id between '05/10/2016'::date - 1 and '05/10/2016'::date
AND 1 = 1
it works fine ... .