Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
gfield

Avatar / Picture

Senior Member
Registered:
Posts: 61
Reply with quote  #1 
Hi,
 I have applied V9 SR3. 
I have a number of Grids that use filters to select rows to display. The SQL below
uses the same Parameter name so that if empty the SQL ignores the value or if provided uses it in a like. This makes more efficient SQL.

SELECT  Top 200  ClientId, ClientName, address, Location, OriginatorFE, OpenedOn, clStatus, cloc

FROM vwClientDetail (NoLock)
Where ClientName Like IsNull(@ClientName,'') + '%'
And ((IsNull(@ClientId,'') = '') or (ClientID like '%' + @ClientId ) )
And ((IsNull(@Location,'') = '') or (cloc = @Location))
And (Isnull(@Search,'') = 'Yes')
Order by ClientName
  

However, although this has worked in SR1 and SR2, it now generates the following message

Failed to execute deployed method 'DataValue', using entity 'grdExistingClients'. The variable name '@ClientId' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@param3".

The problem can be resolved by making all the parameters unique as below, but this entails changingthe BO, Dataaccess parameters and reloading the Grid Columns.

SELECT  Top 200  ClientId, ClientName, address, Location, OriginatorFE, OpenedOn, clStatus, cloc

FROM vwClientDetail (NoLock)
Where ClientName Like IsNull(@ClientName,'') + '%'
And ((IsNull(@ClientId,'') = '') or (ClientID like '%' + @ClientIdA ) )
And ((IsNull(@Location,'') = '') or (cloc = @LocationA))
And (Isnull(@Search,'') = 'Yes')
Order by ClientName

The problem have been logged with Metastorm and I'm awaiting a response.




__________________
Cheers Graham Field
0
gfield

Avatar / Picture

Senior Member
Registered:
Posts: 61
Reply with quote  #2 
Metastorm have agredd this is a bug that will be fixed in the next hot fix due in 2 -3 weeks. It also affects editable grids that use paramters as well.



__________________
Cheers Graham Field
0
gfield

Avatar / Picture

Senior Member
Registered:
Posts: 61
Reply with quote  #3 
This has now been fixed in 9.2
__________________
Cheers Graham Field
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #4 
I just upgraded to 9.2 and it's still an issue for me.  I was working with opentext on it as well, and the tech was baffled by it.
0
gfield

Avatar / Picture

Senior Member
Registered:
Posts: 61
Reply with quote  #5 
Have yu got a copy of the code/query/Business object you are having problems with.  The query at the top of this thread is working fine.
__________________
Cheers Graham Field
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #6 
Sorry for the mess

Quote:
SELECT HBM_CLIENT.CLIENT_NAME, HBM_MATTER.MATTER_NAME, HBM_MATTER.CLIENT_CODE, HBM_MATTER.MATTER_CODE

FROM HBM_MATTER INNER JOIN HBM_CLIENT ON HBM_CLIENT.CLIENT_CODE = HBM_MATTER.CLIENT_CODE
WHERE HBM_MATTER.STATUS_CODE = 'OPEN' AND  (( HBM_MATTER.CLIENT_CODE LIKE '%' + @prmSearch + '%' OR
HBM_CLIENT.CLIENT_NAME LIKE '%' + @prmSearch + '%') AND
((IsNull(@prmSearchMatter,'') = '') OR
  HBM_MATTER.MATTER_NAME LIKE '%' + @prmSearchMatter + '%' OR
  HBM_MATTER.MATTER_CODE LIKE '%' + @prmSearchMatter + '%'))
ORDER BY HBM_CLIENT.CLIENT_NAME, HBM_MATTER.MATTER_CODE
0
gfield

Avatar / Picture

Senior Member
Registered:
Posts: 61
Reply with quote  #7 
Hi
I've converted your query above to similar data I have for a Client matter Inception project.  I'm not sure he query is correct, @prmSearch is matched against the Matter's Client Code and Client's Name, and where the Search Matter Parameter is '', then the next ORs related to MatterName and Code.

Do you get an error from Metastorm, so no results?




__________________
Cheers Graham Field
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #8 
Correct, the form will not even load if it has a search box with that query in it.  So currently i'm just adding numbers to the ends of the additional parameters and linking them all to the same local search variable on the form.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!