OraFAQ Forum: Forms » Select statement return the value according to specified parameters

Home » Developer & Programmer » Forms » Select statement return the value according to specified parameters (JServer Release 9.0.1.1.1)

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

Select statement return the value according to specified parameters [message #383499]

Thu, 29 January 2009 00:38

jamil alshaibani
Messages: 5
Registered: January 2007
Location: saudia rabia

Junior Member

Dear Friends
I have 8 parameters fields if the user enter any TOW PARAMETERS
of those parameters or if he enter all the 8 parameters the select
statement should return only the data according to the specified parameters.

My Script as the following :

DECLARE
cursor C1 IS
SELECT B_ITEM_CODE,SUBSTR(ITEM_NAME_E,1,40) NAME,B_BATCH_NO,B_FINISH_QUANTITY,SERIAL_NO

FROM BATCH_TABLE A,IM_INVENTORY B
WHERE B_BLOCK_TYPE = :T_TYPE_CODE
AND A.B_ITEM_CODE = B.ITEM_CODE
AND B_FINISH_QUANTITY > 0
AND ((B_ITEM_LENGTH BETWEEN :BLOCK_REQUEST_TYPE.T_ITEM_LENGTH AND :BLOCK_REQUEST_TYPE.T_ITEM_LENGTH2)
OR (B_ITEM_WIDTH BETWEEN :BLOCK_REQUEST_TYPE.T_ITEM_WIDTH AND :BLOCK_REQUEST_TYPE.T_ITEM_WIDTH2)
OR (B_ITEM_HIGHT1 BETWEEN :BLOCK_REQUEST_TYPE.T_ITEM_HIGHT1 AND :BLOCK_REQUEST_TYPE.T_ITEM_HIGHT2)
OR (B_ITEM_HIGHT2 BETWEEN :BLOCK_REQUEST_TYPE.T_ITEM_HIGHT2_1 AND :BLOCK_REQUEST_TYPE.T_ITEM_HIGHT2_2))
ORDER BY B_BATCH_NO,SERIAL_NO ASC;

Waiting for your valuable answer, with example please .

Best regards

Jamil Alshaibani

Report message to a moderator

Re: Select statement return the value according to specified parameters [message #383510 is a reply to message #383499]

Thu, 29 January 2009 00:53

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

Hi jamil,

Your question is not clear.

read the guidelines of this forum before posting.

If you are posting like this none will respond you.,

thanks
Seyed Mohamed.

Report message to a moderator

Re: Select statement return the value according to specified parameters [message #383518 is a reply to message #383510]

Thu, 29 January 2009 01:08

jamil alshaibani
Messages: 5
Registered: January 2007
Location: saudia rabia

Junior Member

Dear Friend
Thank very much for your response, but what the user require for example
if he enter the value of the tow parameters
:BLOCK_REQUEST_TYPE.T_ITEM_LENGTH AND :BLOCK_REQUEST_TYPE.T_ITEM_LENGTH2
he will get only those record match the T_ITEM_LENGTH and T_ITEM_LENGTH2 ,
so that I have to use between to get all the records match the tow field ,
the user he have to enter at least tow field as the above or he can enter four fields
or he can enter eight fields ,so that he can not enter only a single field .
Waiting for your valuable answer with example please.

Best regards

Jamil Alshaibani

Report message to a moderator

Re: Select statement return the value according to specified parameters [message #383578 is a reply to message #383499]

Thu, 29 January 2009 03:45

ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester

Member

I also not clearly understand your question
but i think you should try with nvl command. like

and B_ITEM_LENGTH like nvl(B_ITEM_LENGTH1,'%');

Regards,

Ganesh Jadhav.

Report message to a moderator

Re: Select statement return the value according to specified parameters [message #383580 is a reply to message #383499]

Thu, 29 January 2009 03:47

ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester

Member

sorry one should have parameter

and B_ITEM_LENGTH like nvl(:B_ITEM_LENGTH1,'%');

Regards,

Ganesh Jadhav.

Report message to a moderator

Re: Select statement return the value according to specified parameters [message #383642 is a reply to message #383499]

Thu, 29 January 2009 07:43

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

Senior Member

You do need an nvl for this, but not with a LIKE.
YOu want something like this:

column BETWEEN nvl(param1, column) AND nvl(param2, column)

Report message to a moderator

Previous Topic:	how to change the LOV column mapping dyamically ?
Next Topic:	Item Master standard form - customization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 18:11:40 CST 2025