OraFAQ Forum: Forms » Condition based execute_query

Home » Developer & Programmer » Forms » Condition based execute_query (Oracle IDS 10g,Windows XP)

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

Condition based execute_query [message #383068]

Tue, 27 January 2009 03:04

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi All !

I have created forms based on a table say form1,form2 and form3..

I retrieve records that is saved in form1 in form2 and form3 using

execute_query;

in order to avoid duplications..

Now my requirement is retrieve records in form3 based certain conditions..

I tried with the coding

Trigger:when button pressed Block : HODAL

Set_Block_Property('HODAL',DEFAULT_WHERE,'status like %Pending%');
 execute_query;

and

Trigger:when button pressed Block : HODAL

Set_Block_Property('HODAL',DEFAULT_WHERE,'status=Pending Approval');
 execute_query;

Both didnt work and gave me the error ..

FRM-40505: Oracle error: unable to perform query

Kindly help me to overcome this error..

Actually i need to check 2 conditions in the set_block_property for 2 variables which is of datatype varchar..

I tried searching for examples for this , but could not find .. If possible pls help with some examples for the same..

pls help!

Thanks and Regards,
varosh

Report message to a moderator

Re: Condition based execute_query [message #383070 is a reply to message #383068]

Tue, 27 January 2009 03:13

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Dear,

Check your code. I think you have worte in correct where condition.

Both conditions are not correctly written.

To help you apply these where clauses in SQL first just to correct your incorrect syntax.

Regards,
Azam Khan

Report message to a moderator

Re: Condition based execute_query [message #383079 is a reply to message #383068]

Tue, 27 January 2009 04:01

cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester

Senior Member

To be more precise your problem is quotes - you don't have enough.

Report message to a moderator

Re: Condition based execute_query [message #383080 is a reply to message #383068]

Tue, 27 January 2009 04:02

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Obviously, such a syntax is wrong.

When dealing with strings, you have to enclose them into single quotes. Quoting single quotes is a little bit more complicated task, as you'll have to double them.

Therefore, it would be better to write a query in SQL*Plus, make sure it works and then apply this solution in Forms.

This time, I'll help: correct syntax is

Set_Block_Property('HODAL', DEFAULT_WHERE, 'status like ''%Pending%''');

Do the rest by yourself.

Report message to a moderator

Re: Condition based execute_query [message #383101 is a reply to message #383080]

Tue, 27 January 2009 04:35

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi Sir !

Thanks for your reply..

Actually i was trying with

Set_Block_Property('HODAL', DEFAULT_WHERE, 'status like ''[B]'[/B]%Pending%[B]'[/B]''');

Now i got clear by the code u suggested..

Thanks and Regards,
varosh

Report message to a moderator

Re: Condition based execute_query [message #383564 is a reply to message #383079]

Thu, 29 January 2009 03:21

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi sir !

I tried with the coding u suggested..

Trigger: when new block instance
 
if :global.employeeid='1111' then
[B]Set_Block_Property('HODAL', DEFAULT_WHERE,'employeeid=2 or employeeid=3 and status like ''Pending%''');[/B]
execute_query;
elsif :global.employeeid='2222' then
[B]Set_Block_Property('HODAL', DEFAULT_WHERE, 'employeeid=234 and status like ''Pending%''');[/B]
execute_query;
elsif :global.employeeid='3333' then
[B]Set_Block_Property('HODAL', DEFAULT_WHERE, 'employeeid=5 and status like ''Pending%''');[/B]
execute_query;
end if;

Initially i tried with 2 records with employeeid=2 and employeeid=3, whose status value'Pending Approval'..

It worked fine..

I have to change the status of employeeid=2 to Approved from Pending Approval and save the record in this block HODAL..

When i enter this block next time the record employeeid=2 with status=Approved is present, which should not happen..

Pls help to overcome this..

Thanks and Regards,
varosh

Report message to a moderator

Re: Condition based execute_query [message #383573 is a reply to message #383068]

Thu, 29 January 2009 03:37

ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester

Member

Just do one thing,

After updating record, do
commit
clear
and again retrive the records.

i think it will work.

Report message to a moderator

Re: Condition based execute_query [message #383589 is a reply to message #383564]

Thu, 29 January 2009 04:12

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi Sir !

Thanks for your reply..

I tried by giving

Trigger: when button pressed 

clear_form(no_validate);

in a separate button..

But still the same thing happens..

Pls help to solve this

Thanks and Regards,
varosh

Report message to a moderator

Re: Condition based execute_query [message #383594 is a reply to message #383564]

Thu, 29 January 2009 04:24

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try to rearrange triggers; instead of using the WHEN-NEW-BLOCK-INSTANCE, move code into the PRE-QUERY trigger - don't forget to remove EXECUTE-QUERY out of it!

-- PRE-QUERY trigger:

if :global.employeeid='1111' then
   Set_Block_Property('HODAL', DEFAULT_WHERE,'employeeid=2 or employeeid=3 and status like ''Pending%''');
elsif :global.employeeid='2222' then
   Set_Block_Property('HODAL', DEFAULT_WHERE, 'employeeid=234 and status like ''Pending%''');
elsif :global.employeeid='3333' then
   Set_Block_Property('HODAL', DEFAULT_WHERE, 'employeeid=5 and status like ''Pending%''');
end if;

If it still doesn't work, you'll have to debug the form; check "global.employeeid" value (perhaps nothing is true so DEFAULT_WHERE is not set), check "employeeid" value, etc.

Report message to a moderator

Re: Condition based execute_query [message #383607 is a reply to message #383594]

Thu, 29 January 2009 05:24

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi Sir !

Thanks for your reply!

I did as u suggested, still its not working..

The global employeeid's are very much existing..

Thanks and Regards,
varosh

Report message to a moderator

Re: Condition based execute_query [message #386182 is a reply to message #383607]

Fri, 13 February 2009 00:54

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi !

I resolved this issue using (),

 Set_Block_Property('HODAL', default_where,'(employeeid=2 or employeeid=3) and status like ''Pending%''');

Now my issue is, i need to check two conditions for status for which i tried with the coding,

Set_Block_Property('HODAL', DEFAULT_WHERE,
  '(employeeid=2 or employeeid=3) and (status like ''Pending%'' or status like ''Cancelled'')');

But this gave me the error

frm-40350 Query caused no records to be retrieved

Pls help to resolve the same !

Thanks and Regards
varosh

[EDITED by DJM: split long line]

[Updated on: Sun, 15 February 2009 23:09] by Moderator

Report message to a moderator

Re: Condition based execute_query [message #386197 is a reply to message #386182]

Fri, 13 February 2009 01:49

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is not an error; Oracle has just informed you that condition you've used doesn't return any records.

I'd suggest you to test your querying skills in SQL*Plus first; once you learn how to write a SELECT statement which returns desired records, implement this knowledge in Forms Developer.

Report message to a moderator

Re: Condition based execute_query [message #386200 is a reply to message #386197]

Fri, 13 February 2009 02:06

varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI

Senior Member

Hi !

I tried in my database window with,

SELECT *
  FROM empaply WHERE (   employeeid = 2
       OR employeeid = 3)
  AND (   status LIKE 'Pending%'
       OR status LIKE 'Cancel%');

This is working !

In my case since i have created 2 forms based on single table i cant use select into statement to retrieve records since into clause tablename and from clause table name cant be the same right?

So i used execute_query, which i wanted to happen based the condition i gave in my coding ..

Thanks and Regards
varosh

Report message to a moderator

Re: Condition based execute_query [message #386236 is a reply to message #383068]

Fri, 13 February 2009 04:09

cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester

Senior Member

Presumably the form isn't executing the query you think it's executing.
I suggest you use

get_block_property(<block_name>, last_query);

To find out for sure what query the form is actually executing.

Report message to a moderator

Re: Condition based execute_query [message #386510 is a reply to message #386236]

Sun, 15 February 2009 23:11

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Your latter post says "status LIKE 'Cancel%'" but your earlier post says "status like ''Cancelled''". They are not the same.

David

Report message to a moderator

Previous Topic:	hyperlink style selection in forms 6i
Next Topic:	tab canvas

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 22 16:12:08 CDT 2025