Home » Developer & Programmer » Forms » Condition based execute_query (Oracle IDS 10g,Windows XP)
Condition based execute_query [message #383068] Tue, 27 January 2009 03:04 Go to next message
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

Re: Condition based execute_query [message #383070 is a reply to message #383068] Tue, 27 January 2009 03:13 Go to previous messageGo to next message
azamkhan
Messages: 548
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
Re: Condition based execute_query [message #383079 is a reply to message #383068] Tue, 27 January 2009 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be more precise your problem is quotes - you don't have enough.
Re: Condition based execute_query [message #383080 is a reply to message #383068] Tue, 27 January 2009 04:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: Condition based execute_query [message #383101 is a reply to message #383080] Tue, 27 January 2009 04:35 Go to previous messageGo to next message
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
Re: Condition based execute_query [message #383564 is a reply to message #383079] Thu, 29 January 2009 03:21 Go to previous messageGo to next message
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
Re: Condition based execute_query [message #383573 is a reply to message #383068] Thu, 29 January 2009 03:37 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
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.
Re: Condition based execute_query [message #383589 is a reply to message #383564] Thu, 29 January 2009 04:12 Go to previous messageGo to next message
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
Re: Condition based execute_query [message #383594 is a reply to message #383564] Thu, 29 January 2009 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: Condition based execute_query [message #383607 is a reply to message #383594] Thu, 29 January 2009 05:24 Go to previous messageGo to next message
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
Re: Condition based execute_query [message #386182 is a reply to message #383607] Fri, 13 February 2009 00:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: Condition based execute_query [message #386200 is a reply to message #386197] Fri, 13 February 2009 02:06 Go to previous messageGo to next message
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
Re: Condition based execute_query [message #386236 is a reply to message #383068] Fri, 13 February 2009 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Condition based execute_query [message #386510 is a reply to message #386236] Sun, 15 February 2009 23:11 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: hyperlink style selection in forms 6i
Next Topic: tab canvas
Goto Forum:
  


Current Time: Fri Dec 09 17:36:04 CST 2016

Total time taken to generate the page: 0.40555 seconds