select * from (SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1) as res_time
FROM xxe_f749_init init,xxe_f749_init_history history 
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number 
FROM xxe_f749_init_history 
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE') 
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST')
where res_time ='0'

select * from
(select employee_id , salary+1000 as revised_sal 
from employees)
where revised_sal  < 3500

EMPLOYEE_ID	REVISED_SAL
127	        3400
128	        3200
132	        3100
135	        3400
136	        3200

ALTER SESSION SET OPTIMIZER_INDEX_CACHING  = 0;
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;

       WHERE Child = Parent 
         AND Parent = ‘What_I_am_looking for’ ;

SELECT 	history.init_number, 
        history.current_administrator,
        init.CLASS,
        SUBSTR(resolution_time(history.init_number),1,1)
  FROM 	xxe_f749_init_history history,	   -- DETAIL, PLACE IT LEFT
	xxe_f749_init init         	   -- DRIVER, PLACE IT RIGHT MOST
 WHERE 
 	(history.create_date_sec, history.init_number) 
        IN (
            SELECT 	MAX(create_date_sec),
                        init_number 
              FROM 	xxe_f749_init_history 
             GROUP BY init_number )    
   AND 	history.init_status NOT IN ('Resolved','Closed','Dormant')
   AND 	UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE') 
   AND 	SUBSTR(resolution_time(history.init_number),1,1)='0'
   AND 	history.Init_Number=init.Init_Number    -- now find matching child for that parent 
   AND 	SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
   AND 	UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
   AND 	init.command !='AMST'    
   AND 	init.CLASS IN ('1/A','2/B','3/C','4/D') –- place all parent-checks first
;

  SELECT history.init_number, 
         history.current_administrator,
         init.CLASS,
         SUBSTR(resolution_time(history.init_number),1,1)
  FROM 	xxe_f749_init init
  INNER JOIN xxe_f749_init_history ON history.Init_Number=init.Init_Number 
				      AND history.init_status NOT IN ('Resolved','Closed','Dormant')
				      AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
				      AND SUBSTR(resolution_time(history.init_number),1,1)='0'
  LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
                    init_number in_no 
             FROM  xxe_f749_init_history 
             GROUP BY init_number) ON history.create_date_sec=max_sec 
				      AND in_no=history.init_number
  WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
	AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
	AND init.command !='AMST'    
	AND init.CLASS IN ('1/A','2/B','3/C','4/D')
	AND in_no IS NOT NULL


  SELECT history.init_number, 
         history.current_administrator,
         init.CLASS,
         history.r_time
  FROM 	xxe_f749_init init
  INNER JOIN ( SELECT init_number,
		      current_administrator,
	 	      SUBSTR(resolution_time(history.init_number),1,1) r_time
	       FROM xxe_f749_init_history
	       WHERE history.init_status NOT IN ('Resolved','Closed','Dormant')
		     AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE',
							     'ALPHA MAINT ONSITE')) history  ON history.Init_Number=init.Init_Number
	       											AND r_time='0'
  LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
                    init_number in_no 
             FROM xxe_f749_init_history 
             GROUP BY init_number) ON history.create_date_sec=max_sec 
				      AND in_no=history.init_number
  WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
	AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
	AND init.command !='AMST'    
	AND init.CLASS IN ('1/A','2/B','3/C','4/D')
	AND in_no IS NOT NULL

select *
from sdwh_blnc a
where a.sal_dt=to_date('2008-09-01','yyyy-MM-dd')