Home » Developer & Programmer » Forms » Select statement return the value according to specified parameters (JServer Release 9.0.1.1.1)
Select statement return the value according to specified parameters [message #383499] Thu, 29 January 2009 00:38 Go to next message
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
Re: Select statement return the value according to specified parameters [message #383510 is a reply to message #383499] Thu, 29 January 2009 00:53 Go to previous messageGo to next message
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.
Re: Select statement return the value according to specified parameters [message #383518 is a reply to message #383510] Thu, 29 January 2009 01:08 Go to previous messageGo to next message
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
Re: Select statement return the value according to specified parameters [message #383578 is a reply to message #383499] Thu, 29 January 2009 03:45 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
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.
Re: Select statement return the value according to specified parameters [message #383580 is a reply to message #383499] Thu, 29 January 2009 03:47 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

sorry one should have parameter

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



Regards,

Ganesh Jadhav.
Re: Select statement return the value according to specified parameters [message #383642 is a reply to message #383499] Thu, 29 January 2009 07:43 Go to previous message
cookiemonster
Messages: 12422
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)
Previous Topic: how to change the LOV column mapping dyamically ?
Next Topic: Item Master standard form - customization
Goto Forum:
  


Current Time: Fri Dec 09 05:31:08 CST 2016

Total time taken to generate the page: 0.20009 seconds