troubleshooting Question

error in my pre-query

Avatar of chalie001
chalie001 asked on
DatabasesOracle DatabaseSQLMongoDB
13 Comments6 Solutions165 ViewsLast Modified:
hi am having this error in my pre-query trigger ORA-00918: column ambiguously defined
SELECT CODE,DESCRIPTION,DESCRIPTION2 FROM (select ASS_APPL_CODE CODE,ASS_APPL_CODE CODE,ASS_APPL_DESC DESCRIPTION,ASS_APPL_DESC DESCRIPTION,null DESCRIPTION2 from IM_L_ASSOC_APPL_CODE order by 1)

the trigger code is
declare
	cursor c  is 
	    select column_name, column_id from all_tab_columns where table_name=:control.reftable and column_id<=3 order by column_id;
  r c%rowtype;	    
	
	q varchar2(2000) := '(select ';
	
	type arr is varray(3) of varchar2(50);
  arrv arr := arr('CODE', 'DESCRIPTION', 'DESCRIPTION2');
begin
	:control.column1:=null;
	:control.column2:=null;
	:control.column3:=null;

  open c ;
  loop
  	 fetch c into r;
  exit when c%notfound;	 
      q := q  || r.column_name||' '|| arrv(r.column_id)||',';
      if r.column_id = 1 then
      	:control.column1:=r.column_name;
      end if;
      
      if r.column_id = 2 then
      	:control.column2:=r.column_name;
      end if;
      
      if r.column_id = 3 then
      	:control.column3:=r.column_name;
      end if;
  end loop;

  if r.column_id=2 then
  	q := q  || 'null' ||' '|| arrv(3)||',';
  end if;
  
  q := substr(q,1,length(q)-1) || ' from '||:control.reftable||' order by 1)';

  set_block_property ('LIST',query_data_source_name,q);
  
  if 	:control.column3 is null then
  	  set_item_property ('LIST.DESCRIPTION2',VISIBLE,property_false);
  	  set_item_property ('LIST.DESCRIPTION',width,385);
  else
  	  set_item_property ('LIST.DESCRIPTION2',visible,property_true);
  	  set_item_property ('LIST.DESCRIPTION2',enabled,property_true);
  	  set_item_property ('LIST.DESCRIPTION2',insert_allowed,property_true);
  	  set_item_property ('LIST.DESCRIPTION2',update_allowed,property_true);
  	  set_item_property ('LIST.DESCRIPTION2',navigable,property_true);
  	 
  	  set_item_property ('LIST.DESCRIPTION',width,192);
  end if;	
  close c;
end; 

am in 11g
i think the error is beacause the table column has change its no longer code description am in 11g
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 6 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros