OraFAQ Forum: Forms » problem in searching using combo

Home » Developer & Programmer » Forms » problem in searching using combo (forms builder 6i and database 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in searching using combo [message #413904]

Sat, 18 July 2009 11:36

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: problem in searching using combo [message #413908 is a reply to message #413906]

Sat, 18 July 2009 12:30

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

Report message to a moderator

Re: problem in searching using combo [message #413909 is a reply to message #413908]

Sat, 18 July 2009 13:26

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: problem in searching using combo [message #413911 is a reply to message #413909]

Sat, 18 July 2009 14:08

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.

Report message to a moderator

Re: problem in searching using combo [message #413912 is a reply to message #413911]

Sat, 18 July 2009 15:09

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: problem in searching using combo [message #413913 is a reply to message #413912]

Sat, 18 July 2009 15:17

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

Report message to a moderator

Re: problem in searching using combo [message #414053 is a reply to message #413904]

Mon, 20 July 2009 04:03

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: problem in searching using combo [message #414071 is a reply to message #414053]

Mon, 20 July 2009 05:00

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

Report message to a moderator

Previous Topic:	attaching pictures.
Next Topic:	Blob in Form6i and 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 04:43:13 CST 2025