Interview topics

2 comments:

Ganesh Ponna said...

'009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
)
and dstrct_code='RTK1'
GROUP BY COLUMN_NAME

So i would then get the list of all values for each column name and how often they are populated.

It should be something along the lines of..

DECLARE

BEGIN
FOR RRR IN (

SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER ='ELLIPSE'
AND TABLE_NAME='MSF690'
AND COLUMN_NAME<>'STD_JOB_NO'
ORDER BY TABLE_NAME
)
LOOP
EXECUTE IMMEDIATE 'select ' ||
RRR.COLUMN_NAME || ', COUNT(STD_JOB_NO)
from msf690
where std_job_no in (
'009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
)
and dstrct_code='RTK1'
GROUP BY RRR.COLUMN_NAME'

and then i want to dbms_output this without inserting the values into a table as I don't have permissions to create tables.








Great effort, I wish I saw it earlier. Would have saved my day :)


,Merci

Radhey Radhey said...

Hi Mate,


cinterviews.com | c interview questions | c tutorials | java tutorials | java interview questions being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.

wrote this dynamic query and facing below error, any suggesions??

declare
sql_stmt clob;
pivot_clause DATE;
begin

select listagg('''' || trunc(rollup_timestamp) || '''', ',') within group (order by trunc(rollup_timestamp)) into pivot_clause from
MGMT$METRIC_HOURLY
where
rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
and rollup_timestamp between sysdate - 7 and sysdate
and METRIC_COLUMN = 'cpuUtil' and target_type='host'
and METRIC_NAME = 'Load' group by trunc(rollup_timestamp);
sql_stmt := 'select * from
(
select target_name, trunc(rollup_timestamp) tgl,round(avg(round ((AVERAGE/10),3)),3) average from
MGMT$METRIC_HOURLY
where
rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
and rollup_timestamp between sysdate - 7 and sysdate
and METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host''''
and METRIC_NAME = ''''Load''''
group by target_name, trunc(rollup_timestamp), rollup_timestamp
union
select target_name,trunc(collection_timestamp) tgl, (round(value,3))
from MGMT$METRIC_current where
METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host'''' and METRIC_NAME = ''''Load'''' ) PIVOT (max(average) for (tgl) in (' || pivot_clause || ')) where target_name = ''''health''''
order by 1,2';

execute immediate sql_stmt;
end;
/


ERROR at line 1:
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01489: result of string concatenation is too long
ORA-06512: at line 5


Anyways great write up, your efforts are much appreciated.


Obrigado,
Radhey

 
Top Blogs