HI there,
I am doing my internship with accenture and i got my first assignment.
Basically i first thing with this task is i need to find way to modify the query so that it will pull only the data that has existed/activated more than one hour. This is the complete query :
select account_no, unixts_to_date(created_t), poid_id0, state_flag from
(
SELECT a.account_no, m.poid_id0, m.state_flag, c.created_t, row_number() OVER (PARTITION BY c.account_obj_id0 ORDER BY c.created_t DESC) rnk
FROM pone_bt_prod_inst_t m, pone_act_inst_t c, account_t a
WHERE m.state_flag IN ('0', '4', '5', '6', '7')
AND a.poid_id0 = m.account_obj_id0
AND c.prod_inst_id_id0 = m.poid_id0
AND m.service_type = '/service/ip/wimax'
AND m.entity_type = 'Plan'
AND c.state_flag NOT IN ('1', '2', '14')
ORDER BY created_t ASC
)
WHERE rnk = 1
UNION ALL
SELECT account_no, unixts_to_date(created_t), poid_id0, state_flag FROM
(
SELECT a.account_no, m.poid_id0, m.state_flag, c.created_t, row_number() OVER (PARTITION BY c.account_obj_id0 ORDER BY c.created_t DESC) rnk
FROM pone_bt_prod_inst_t m, pone_act_inst_t c, account_t a
WHERE m.state_flag IN ('16', '17', '18')
AND a.poid_id0 = m.account_obj_id0
AND c.prod_inst_id_id0 = m.poid_id0
AND m.service_type = '/service/ip/wimax'
AND m.entity_type = 'Deal'
AND c.state_flag NOT IN ('1', '2', '14')
ORDER BY created_t ASC
)
WHERE rnk = 1;
I show you the query so that you could grab the whole figure how the database looks like just in case you want to.
Anyway, from that query, I tr to play with it and I am trying to get the difference between the current time with the time in the created_t column.
As far as i got, this is the query :
SELECT TO_CHAR( unixts_to_date(created_t), 'SSSSS' )
FROM pone_bt_prod_inst_t
WHERE ( TO_CHAR( SYSDATE, 'SSSSS' ) - TO_CHAR( unixts_to_date(created_t), 'SSSSS' ) ) > 3600;
I dont know whats wrong but it gave me the continous looping of "time" for the output.. And i know that the " 3600" is wrong, but may i know what the number i should put there as the comparison? because i want to output only data than has generated more than 1 hour, but still i dont know what format that original unix system used T___T What i mean is that, when i run the query of SELECT created_t FROM pone_bt_prod_inst_t; , it will output the time like this 1212640384 , so what is it actually?
thank you in advance for helping..
