Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
tspafford

Senior Member
Registered:
Posts: 64
Reply with quote  #1 
Is there anyway to populate a grid from a straight %SelectSQL statement? The grid db connection tools are too limiting.

I've found that I can specify a left outer join in the TABLES section, inner joins can just be in the ROWS section.

Calculated fields, concatenated fields, etc can go in the COLUMNS - COLUMN NAMES section

ASC and DESC keywords can be used in the ORDER section.

I am currently using e-work 6.5 native on SQL 2000 with ODBC calls to both Oracle 8i and IBM DB2/400.
0
Doogal

Avatar / Picture

Guru
Registered:
Posts: 1,564
Reply with quote  #2 

You can update the eField.eDataValue column for the grid on the form load using %ExecSQL to specify any SQL you like. Take a look at what is in there currently and just update as required. It'll need to be a %GetRecordSet call, which works pretty much like %SelectSQL. You'll still need to specify the columns in the Designer though. 

0
njunsved

Member
Registered:
Posts: 10
Reply with quote  #3 
Hi,

I'm updating efield (not using %ExecSQL) with the statement below:

UPDATE efield
SET eDataValue = '%GetRecordSet("SELECT DISTINCT table1.number, table1.status, table1.manusid, table2.status from table1 INNER JOIN table2 on table1.number = table2.cnumber where (table1.efolderid = ''%tCrossRefFolderId'' and table2.folderid = ''%FolderID'')  ORDER BY table1.status, table1.number",False)'
WHERE eprocedurename = 'aldProcedure' and eformname = 'aldProduction' and efieldname = 'Grid_exposure1'

This works fine and the grid is populated in the expected way in runtime.

The problem is that I can't get this to work using %ExecSQL because I'm not able to make a correct string out of the highlighted string above. I have tried a number of different combinations with escape characters, using %Replace etc but have not been successful so far.

Perhaps someone could give me a hint of what the %ExecSQL expression should look like?

Thanks!

Regards,

Niclas
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #4 
I think that you just need to replace any % with %% and " with %"

It'll look odd (especially in the Designer as it does not understand %" for some daft reason) but that should do it.
try
Quote:
%ExecSQL("UPDATE efield
SET eDataValue = '%%GetRecordSet(%"SELECT DISTINCT table1.number, table1.status, table1.manusid, table2.status from table1 INNER JOIN table2 on table1.number = table2.cnumber where (table1.efolderid = ''%%tCrossRefFolderId'' and table2.folderid = ''%%FolderID'')  ORDER BY table1.status, table1.number%%",False)'
WHERE eprocedurename = 'aldProcedure' and eformname = 'aldProduction' and efieldname = 'Grid_exposure1' ")


__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
njunsved

Member
Registered:
Posts: 10
Reply with quote  #5 
Ctrl+C Ctrl+V

Thanks Jerome worked like a charm!
0
Piush

Awaiting Email Confirmation
Registered:
Posts: 6
Reply with quote  #6 
Hi Jerome/njunsved... hoping one or both can help me on something similar (to the above) that I'm trying to achive.  I'm trying to get my grid to display values in the first column (using field in table) and display values in the second column (SQL calculation based on value of first column). Example:

Table is eEvent

Column1: List of all unique "eUserName" based on eMapName, eToStage etc.
Column2 (alias): Sum of unique eFolderID based on value of column1 for each row.

Seems like it should be a basic GROUP and COUNT but I'm finding the grid a little restrictive!
0
praxkan

Veteran
Registered:
Posts: 142
Reply with quote  #7 
Piush

Have you considered using views? Personally I find views to be the best way to handle these situations with 7.6 grids. Also with views you can add useful things like "nolock" to your queries to make them less lockable with eEvent and other e-tables which are constantly being written to.


0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!