Home » Developer & Programmer » Forms » How to pass parameter in query ( for in clause)? (Form 6i, oracle 9i)
How to pass parameter in query ( for in clause)? [message #386357] Fri, 13 February 2009 22:48 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi,
I write a cursor in when_button_pressed, query in cursor is

cursor empgrs is (Select a.emplcode,amount
from payfile a,emplmast b  
where a.unitid = 1
and monthno = 4
and yearno = :Fyear - 1
and paycode = 'E97'
and a.emplcode = b.emplcode
and substr(b.catgcode,1,1) in (:Catg))


And Catg is prepared and passed via a screen and the code is

If Checkbox_Checked('ChkWorkmen') Then
	 :Catg := '''W''';
End If;
If Checkbox_Checked('Chkstaff') Then
	 If :Catg is not null Then
	    :Catg := :Catg||',''S'',''O''';
	 Else
	 	  :Catg := '''S'',''O''';
	 End If;
End If;

If Checkbox_Checked('ChkExec') Then
	 If :Catg is not null Then
	    :Catg := :catg||',''E''';
	 Else
	 	  :Catg := '''E''';
	 End if;
End if;


Catg is prepared based on checked checkbox( it have three check boxes worker,staff,executive)

final catg value may have passed like ({'W'},{'S','O'},{'E'}, {'W','S','O'} ect., )

even i passing such way im not getting any output on screen(even i am not having any compliation error also)

I have problem in passing catg

please help me to solve this issue.

kanish
Re: How to pass parameter in query ( for in clause)? [message #386516 is a reply to message #386357] Sun, 15 February 2009 23:24 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
May I STRONGLY recommend that you use the 'default_where' facility and NOT use your own cursor.

When in doubt as to what is being passed in a field, either use the 'message' command to display it or use a non-database item that is temporarily displayed and place the information in it.

David
Re: How to pass parameter in query ( for in clause)? [message #386529 is a reply to message #386357] Mon, 16 February 2009 00:16 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi martin,

My screen is not datablock, so i fell, i cant use default_where, i write "message" to get the value of :catg that is working as per my requirement.

which i passed manualy in query which working perfectly

Select a.emplcode,amount
from payfile a,emplmast b  
where a.unitid = 1
and monthno = 4
and yearno = :Fyear - 1
and paycode = 'E97'
and a.emplcode = b.emplcode
and substr(b.catgcode,1,1) in ('W','S','0)


but it is not working when i pass as parameter to where clause.

 and substr(b.catgcode,1,1) in (:Catg) 


kanish






Re: How to pass parameter in query ( for in clause)? [message #386532 is a reply to message #386529] Mon, 16 February 2009 00:33 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please copy code from your working form. The first code listed can not work as it has a quote missing.

What is in ':Catg'? Use message to display it!

David
[EDITED by DJM: fixed typo.]

[Updated on: Tue, 17 February 2009 23:53]

Report message to a moderator

Re: How to pass parameter in query ( for in clause)? [message #386535 is a reply to message #386357] Mon, 16 February 2009 00:47 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

sorry
1st one my typing mistake.

:catg is one item in block.

when i message the :Catg

value : 'W','S','O'

kanish
Re: How to pass parameter in query ( for in clause)? [message #386609 is a reply to message #386357] Mon, 16 February 2009 04:21 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
To do what you want you'll probably need to have a look at this:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

That said, for limited number of checks you're doing I suspect it'd be easier to have 3 items for the three checkboxes and check them individually.
Something like this:
Select a.emplcode,amount
from payfile a,emplmast b  
where a.unitid = 1
and monthno = 4
and yearno = :Fyear - 1
and paycode = 'E97'
and a.emplcode = b.emplcode
and ((:ChkWorkmen_checked = 'Y' and substr(b.catgcode,1,1) = 'Y')
     or (:Chkstaff_checked = 'Y' and (substr(b.catgcode,1,1) in ('S','O'))
.........
etc
Re: How to pass parameter in query ( for in clause)? [message #386618 is a reply to message #386357] Mon, 16 February 2009 04:36 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

HI

and ((:ChkWorkmen_checked = 'Y' and substr(b.catgcode,1,1) = 'Y')
     or (:Chkstaff_checked = 'Y' and (substr(b.catgcode,1,1) in ('S','O'))



suppose both chkworkmen,chkstaff checked, i need like 'Y','S','O'

Are you instruct me to do combinations, like above.

It may clumsy,in case of more checkboxes.

and

Is this only the solution for me?

kanish

[Updated on: Mon, 16 February 2009 04:37]

Report message to a moderator

Re: How to pass parameter in query ( for in clause)? [message #386635 is a reply to message #386357] Mon, 16 February 2009 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:
Are you instruct me to do combinations, like above

That was the idea.

Quote:
It may clumsy,in case of more checkboxes.


the suggestion was based on the assumption that the code will stay as simple as your example.

Quote:
Is this only the solution for me?

No - read the link
Re: How to pass parameter in query ( for in clause)? [message #386755 is a reply to message #386357] Mon, 16 February 2009 23:16 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi

Very Thankful to you cookie that is very great like to solve my issue.

kanish
Previous Topic: How to determine we are on the first record?
Next Topic: How to translate system defined error messages to the langauge selected
Goto Forum:
  


Current Time: Fri Dec 02 16:52:34 CST 2016

Total time taken to generate the page: 0.25972 seconds