Home » SQL & PL/SQL » SQL & PL/SQL » How do you count explicit nested_table and incorporate it in the logic
How do you count explicit nested_table and incorporate it in the logic [message #233364] Wed, 25 April 2007 17:12
dpong
Messages: 73
Registered: January 2007
Member
CREATE OR REPLACE PROCEDURE COMPETITOR_ORGS_LOOKUP IS
  
  org tier_orgs%ROWTYPE;
  
  ret  VARCHAR2(100) := NULL;
   
  
  TYPE orgnames_t IS TABLE OF ALL_ORGNAMES_FROM_ORGS_TABLE%ROWTYPE; 
  v_orgrecs    orgnames_t;
  cur_1        SYS_REFCURSOR;
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);

  sqlCommand sqlType;
  sqlCommand2 sqlType;
  sqlCommand3 sqlType;
  
  objcur curtype;
  cur1 	curtype;
  cur2  curtype;

  oname VARCHAR2(100);
  --oname2 VARCHAR2(200); 

  p_tier_id NUMBER := 2;
  
  max_rows       CONSTANT NUMBER := 100; 


							 							 		
BEGIN

   sqlCommand2 := 'SELECT * FROM tier_orgs';							 
						 							  
   sqlCommand := 'SELECT func_sys_refcursor( cursor ( select :cur_1, :cur_2  from dual ) ) INTO cur_1 from dual	;

	OPEN cur2 FOR sqlCommand2;
	FETCH cur2 INTO ORG;
	
	
FOR l_lookup in org

    LOOP
			If (org.org_not_like IS NOT NULL) -- need to use 
			   					 			  /* 
											  case when 
			   					 			  	   ( select count(*) from the 
			   					 			  (SELECT cast (in_list (Org.org_like)  as mytableType) from dual) = 1 ) 
											   THEN select * from the (SELECT cast (in_list (Org.org_like)  as mytableType) from dual) 
											   ELSE [psedo code ]: in_list (Org.org_like) || in_list (Org.org_not_like) 
											  */
											   
			THEN ret := 'WHERE ( ' ||  in_list (Org.org_like) || in_list (Org.org_not_like) || ' )';
			ELSE ret := 'WHERE ( ' ||  in_list (Org.org_like) || ' )';
			END IF;
					
		  OPEN objcur FOR SQLCommand;	 
		    FETCH objcur INTO oname;
		    
		   execute immediate oname USING 'SELECT org_id, org_name FROM ALL_ORGNAMES_FROM_ORGS_TABLE ', ret;
		   
		  CLOSE objcur;
  
    FETCH cur_1 BULK COLLECT INTO v_orgrecs LIMIT max_rows;
    FORALL i IN 1..v_orgrecs.COUNT
      INSERT INTO competitor_orgs_all(ORG_ID,ORG_NAME,COMPETITOR_NAME,MKT_SHARE_TIER	) --(market_share_tier, org_id, org_name)
         VALUES ( v_orgrecs(i), org.competitor_name, org.p_tier_id)  ;--(p_tier_id, v_orgrecs.org_id, v_orgrecs.org_name(i) );
   
   
    END LOOP;
  CLOSE cur_1; 
   		

END;



create table         competitor_tier2_orgs nologging as

select distinct            org_id, org_name

from                              ALL_ORGNAMES_FROM_ORGS_TABLE

where                      org_name like 'ACTUATE%'

or                                (org_name like 'QWEST %' 

                                  and org_name not like 'QWEST%DIAGNOSTIC%'

                                  and org_name not like 'QWEST%DENTAL%'

                                  and    org_name not like 'QWEST%FOODS%'

                                  and    org_name not like 'QWEST%AIR%PART%'

                                  and    org_name not like 'QWEST%BRIAN%ALBANO')

                                  /* QWEST COMMUNICATIONS - not direct competitor or in Oracle's space */

order by                   org_name asc

;
 

/* APPEND MARKET SHARE TIER VALUE FOR ALL ORGS */

 

alter table                competitor_tier2_orgs

add                               (competitor_name             varchar2(500),

                                  mkt_share_tier                           varchar2(3))

;

 

create index  competitor_tier2_orgs_idx on competitor_tier2_orgs (org_name) nologging 

;

 

analyze table competitor_tier2_orgs estimate statistics;

 

update                     competitor_tier2_orgs

set                               mkt_share_tier = '2'

;

update				competitor_tier2_orgs
set					competitor_name = 'ACTUATE'
where				org_name like 'ACTUATE%'
;

update				competitor_tier2_orgs
set					competitor_name = 'QWEST COMMUNICATIONS'
where				(org_name like 'QWEST %' 
					and org_name not like 'QWEST%DIAGNOSTIC%'
					and org_name not like 'QWEST%DENTAL%'
					and	org_name not like 'QWEST%FOODS%'
					and	org_name not like 'QWEST%AIR%PART%'
					and	org_name not like 'QWEST%BRIAN%ALBANO')
;






This is the lookup table I created.
CREATE TABLE tier_orgs (

  P_tier_id INTEGER,

  Competitor_name VARCHAR2(40),

  org_Like VARCHAR2(200),

  org_not_like CHAR(200)

);

 

INSERT INTO tier_orgs VALUES ('2','QWEST COMMUNICATIONS','QWEST %','QWEST%DIAGNOSTIC%|QWEST%DENTAL%|QWEST%FOODS%|QWEST%AIR%PART%|QWEST%BRIAN%ALBANO');

 




In the strings
THEN ret := 'WHERE ( ' || in_list (Org.org_like) || in_list (Org.org_not_like) || ' )';

I'd like to incorporate a logic for it to concatenate
WHERE org_name like org_like(1)
or org_name like org_like(2)
and org_name not like org_not_like(1)
, if necessary

someone knows how to accomplish that ?

[Updated on: Wed, 25 April 2007 17:13]

Report message to a moderator

Previous Topic: Writing to a file from PL/SQL procedure
Next Topic: Max Query Help!!!
Goto Forum:
  


Current Time: Fri Dec 09 03:49:20 CST 2016

Total time taken to generate the page: 0.11184 seconds