Home » Developer & Programmer » Forms » problem in searching using combo (forms builder 6i and database 9i)
problem in searching using combo [message #413904] Sat, 18 July 2009 11:36 Go to next message
titan_eye
Messages: 7
Registered: May 2009
Location: iraq
Junior Member

hi everyone ;

I am new to oracle, and I have project to finish, so please help me.
my project is searching depending on list item (combo) selected valeus . I`v made 2 data block EMP & CONTROL , CONTROL have T3 and T7 ( combo lists) and push button , T3,T7 filled manaually of job and deptno
i am using form 6i and back-end database 9i
i get error: unable to perform query

this is my code
DECLARE
   v_query VARCHAR2(200) := '(select empno,ename,job,mgr,hiredate,sal,deptno from EMP where ';
        v3 VARCHAR2(50); 
        v7 VARCHAR2(50);
N NUMBER;
r_id RECORDGROUP;
BEGIN
    go_block('CONTROL');
      if NVL(LENGTH( (:CONTROL.t3) ),0)<>0 then
     v3:=' AND job = '''||rtrim(ltrim(:CONTROL.t3))||'''';
   else
       v3:=' AND 1=1 ';
       end if;
      if NVL(LENGTH( (:CONTROL.t7) ),0)<>0 then
     v7:= ' AND DEPTNO ='||(:CONTROL.t7)||'';
   else
       v7:='  AND 1=1 ';
       end if;
   v_query := v_query ||v3||v7||')' ;
    go_block('EMP');
   clear_block;
set_block_property('EMP', QUERY_DATA_SOURCE_NAME, v_query);
   execute_query(no_validate);
   raise form_trigger_failure;

END;


please help me its urgent to me

thanks

[EDITED by LF: green code formatting removed; applied [code] tags instead]

[Updated on: Sat, 18 July 2009 12:15] by Moderator

Report message to a moderator

Re: problem in searching using combo [message #413906 is a reply to message #413904] Sat, 18 July 2009 12:16 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Run the form. Once the error appears, go to Help menu and choose "Display error". It will reveal some information which might be useful. If you don't know how to solve the problem, please, post result of the "Display error" back here.
Re: problem in searching using combo [message #413908 is a reply to message #413906] Sat, 18 July 2009 12:30 Go to previous messageGo to next message
titan_eye
Messages: 7
Registered: May 2009
Location: iraq
Junior Member

oh thank you very much littlefoot , i am very appreciate your relpy.

this what done


SQL statement in error :
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM (select empno,ename,job,mgr,hiredate,sal,deptno from EMP where AND job = 'MANAGER' AND DEPTNO =20)

error :
ORA-00936: missing expression
Re: problem in searching using combo [message #413909 is a reply to message #413908] Sat, 18 July 2009 13:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Then it is obvious:
where AND job = 'MANAGER' 
Either remove "AND" or include another condition behind the WHERE keyword.
Re: problem in searching using combo [message #413911 is a reply to message #413909] Sat, 18 July 2009 14:08 Go to previous messageGo to next message
titan_eye
Messages: 7
Registered: May 2009
Location: iraq
Junior Member

ok , i remove it it stil same ;

i think this is what make the problem ( in red )

SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM (select empno,ename,job,mgr,hiredate,sal,deptno from EMP where AND job = 'MANAGER' AND DEPTNO =20)

what do u think ??

may you check the whole code , please.
Re: problem in searching using combo [message #413912 is a reply to message #413911] Sat, 18 July 2009 15:09 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that it is just too complicated. It is the same as
select empno, ename, job, mgr, hiredate, sal, deptno 
from emp 
where job = 'MANAGER' 
  and deptno = 20

By the way, where is that piece of code written?
Re: problem in searching using combo [message #413913 is a reply to message #413912] Sat, 18 July 2009 15:17 Go to previous messageGo to next message
titan_eye
Messages: 7
Registered: May 2009
Location: iraq
Junior Member

these code written in push button

but colored part , dont know where it come from
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM (select empno,ename,job,mgr,hiredate,sal,deptno from EMP where AND job = 'MANAGER' AND DEPTNO =20)

rest of query (select empno,ename,job,mgr,hiredate,sal,deptno from EMP where AND job = 'MANAGER' AND DEPTNO =20)
formelated at run time
Re: problem in searching using combo [message #414053 is a reply to message #413904] Mon, 20 July 2009 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the coloured bit isn't causing your error. So while it might be an idea for you to work out where it came from to understand these things better it's not what you need to fix.

As Littlefoot pointed out earlier your problem is this:
where AND job = 


That and does not belong there.
Re: problem in searching using combo [message #414071 is a reply to message #414053] Mon, 20 July 2009 05:00 Go to previous message
titan_eye
Messages: 7
Registered: May 2009
Location: iraq
Junior Member

ok thax u all

the problem is really fien and every thing is ok

thano you alot
Previous Topic: attaching pictures.
Next Topic: Blob in Form6i and 10g
Goto Forum:
  


Current Time: Sat Dec 03 03:50:53 CST 2016

Total time taken to generate the page: 0.14089 seconds