Get a request Number using some data form

Process Design
1

Hello,
We want to get a request number by using a query that contains two textboxs values from request form
ex :
we have two textboxs the first’s id=‘PIECENUMBER’ and the second’s id=‘HOODNUMBER’ .
select requestNumber form … where PIECENUMBER=20 and HOODNUMBER=30.

Best regards
Rami Tobasi

2

Nobody have a answer

3

Hi Rami,

Here is the query that you need to execute. Make sure to change the values @FILTER_NAME, @FILTER_ID_STATE, @PARAM_VALUE0 and @PARAM_VALUE1 to the desired values:

exec sp_executesql N'SELECT PI.ID_PROCESS_INST FROM WFPROCESS_INST PI, WFPROCESS P, USERS, WFPROCESS_PARTICIPANT PP  
WHERE  P.ID_PROCESS=PI.ID_PROCESS  
AND PI.ID_USER_REQUESTER=USERS.ID_USER  
AND PI.ID_PROCESS=PP.ID_PROCESS  
AND PP.ID_PARTICIPANT_ROLE=''REQUESTER''  
AND P.NAME=@FILTER_NAME  
AND PI.ID_STATE=@FILTER_ID_STATE  
AND ((SELECT COUNT(*)  FROM WFPROCESS_INST_RELDATA, WFRELDATA, WFDATASET_VALUE  
WHERE WFPROCESS_INST_RELDATA.ID_RELDATA=WFRELDATA.ID_RELDATA 
AND  WFPROCESS_INST_RELDATA.ID_DATASET=WFDATASET_VALUE.ID_DATASET 
AND  WFPROCESS_INST_RELDATA.ID_PROCESS_INST=PI.ID_PROCESS_INST  
AND  (WFRELDATA.NAME = @PARAM_NAME0 AND VALUE_NUMERIC =@PARAM_VALUE0) ) > 0 
AND (SELECT COUNT(*)  FROM WFPROCESS_INST_RELDATA, WFRELDATA, WFDATASET_VALUE  
WHERE WFPROCESS_INST_RELDATA.ID_RELDATA=WFRELDATA.ID_RELDATA 
AND  WFPROCESS_INST_RELDATA.ID_DATASET=WFDATASET_VALUE.ID_DATASET 
AND  WFPROCESS_INST_RELDATA.ID_PROCESS_INST=PI.ID_PROCESS_INST  
AND  (WFRELDATA.NAME = @PARAM_NAME1 AND VALUE_NUMERIC =@PARAM_VALUE1) ) > 0)  
ORDER BY ID_PROCESS_INST ASC',
N'@FILTER_NAME nvarchar(22),
@FILTER_ID_STATE nvarchar(4),
@PARAM_NAME0 nvarchar(14),
@PARAM_VALUE0 nvarchar(4),
@PARAM_NAME1 nvarchar(13),
@PARAM_VALUE1 nvarchar(1)',
@FILTER_NAME=N'PROCESS_NAME',
@FILTER_ID_STATE=N'open',
@PARAM_NAME0=N'REQUEST_HOODNO',
@PARAM_VALUE0=N'1942',
@PARAM_NAME1=N'REQUEST_PIECE',
@PARAM_VALUE1=N'1'

Regards,
Eddy.