Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #1 
Hi,

I like to know whether it is possible to build a where clause for grid dynamically.

I have a situation:
****************************************************
I have 2 drop downs and 2 date fields.
I need to populate the data in grid according to the values in drop down

I can select any one  of the drop down or both of them
and I have to select the date range also (from date and to date)

If I'm not selecting anything from dropdown, grid needs to display all the records for the selected Date range.
****************************************************
What I tried now is I check the conditions and create a querry dynamically and assign it to a memo variable on a button click. And then in the row condition I give this memo variable to the row condition of the Grid.
But it threw an error:
Quote:
Failed to process 'eExecuteSQL' Metastorm engine Database Connector request. ErrorCode: '-2147217904'. Description: 'No value given for one or more required parameters.'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: ''. NativeErrorCode: '0' Procedure GetRecordSet call failed.  Position: 1232


I'm in urgent need for this soultion . Any help will be greatly appreciated and if you have any samples on this, kindly share it with me

Thanks
Anoop

0
Pete

Senior Veteran
Registered:
Posts: 315
Reply with quote  #2 
Can you show how you concatinate your where-statement? The idea sounds correct, we're doing it in the same way. Just make sure that if no value from a dropdown is selected, you mustn't extend the where condition...

If you're unsure about your condition, write it in a file using %WriteFile and past it in the query browser

__________________
If you want the job done right, hire a professional.
0
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #3 
This is the way I did it. memWhereClause  is the variable where I set on the button Click.
How will you give this variable in the Row condition of Grid?
I mean will you give it as %memWhereClause or '%memWhereClause' or ..?

Quote:

( (%TNumber = %Empty()) & (%VNumber <> %Empty()) )?((%memWhereClause:=" ((Report_V.VNum = '%VNumber')  AND
 (Report_V.DateOfrequest >= '%dtmFromDate')  AND
 (Report_V.DateOfrequest <= '%dtmToDate'))")):((%TNumber <> %EMPTY()) & (%VNumber = %EMPTY()) )?((%memWhereClause:="((Report_V.TNum = '%TNumber')  AND
 (Report_V.DateOfrequest >= '%dtmFromDate')  AND
 (Report_V.DateOfrequest <= '%dtmToDate'))")):(( (%TNumber <> %EMPTY()) & (%VNumber <> %EMPTY()) )?((%memWhereClause:=" ((Report_V.VNum = '%VNumber')  AND
 (Report_V.TNum = '%TNumber')  AND
 (Report_V.DateOfrequest >= '%dtmFromDate')  AND
 (Report_V.DateOfrequest <= '%dtmToDate'))")):((%memWhereClause:="")))

0
Pete

Senior Veteran
Registered:
Posts: 315
Reply with quote  #4 

just  %memWhereClause  in the row property is correct. Can't check the condition right now, will do it tomorrow. Have you wrote it out in a field or textfile to see what is generated and to try it in the query analyzer? can you show what is generated exactly?


__________________
If you want the job done right, hire a professional.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #5 
don't you need to keep adding to the variable? It seems previous conditions will get overwritten.

If you look at our Admin Tools procedure (available elsewhere on the forum) you can see an example on the 'Refresh' button of the' Query Folder' form:
Quote:
// empty the existing criteria
%mWhereClause:=""
// set all selected criteria
((%tMapName = "") | (%tMapName="[All Folders]"))?():(%mWhereClause:=%mWhereClause AND eMapName='%tMapName')
(%mStages = "")?():(%mWhereClause:=%Concatenate(%mWhereClause," AND eFolder.eStageName IN ('",%Replace( %mStages, %Chr(44), '%Chr(44)', 0),"')"))
(%tFolderName = "")?():(%mWhereClause:=%mWhereClause AND eFolderName LIKE '%%%%%tFolderName%%%%')
(%tCategory = "")?():(%mWhereClause:=%mWhereClause AND eCategory LIKE '%%%%%tCategory%%%%')
(%tSubject = "")?():(%mWhereClause:=%mWhereClause AND eSubject LIKE '%%%%%tSubject%%%%')
(%tOriginator = "")?():(%mWhereClause:=%mWhereClause AND eOriginator='%tOriginator')
(%tActionedBy = "")?():(%mWhereClause:=%Concatenate(%mWhereClause," AND eFolderID IN (SELECT eFolderID FROM eEvent WHERE eUserName='%tActionedBy')"))
((%dSubmittedFrom = %Never()) | (%dSubmittedTo = %Never()))?():(%mWhereClause:=%mWhereClause AND eCreationTime BETWEEN {d '%FormatTime(%dSubmittedFrom,yyyy-mm-dd)'} AND {ts '%FormatTime(%dSubmittedTo,yyyy-mm-dd 23:59:59)'})
((%iCreatedInLast = 0) | (%tCreatedInLastUnits=""))?():(%mWhereClause:=%Concatenate(%mWhereClause," AND eCreationTime > {ts '%%FormatTime(%%MakeDate(%iCreatedInLast,%tCreatedInLastUnits,before,%%System.Time),yyyy-mm-dd hh:nn:ss)'} "))
((%iActionedInLast = 0) | (%tActionedInLastUnits=""))?():(%mWhereClause:=%Concatenate(%mWhereClause," AND eUpdated > {ts '%%FormatTime(%%MakeDate(%iActionedInLast,%tActionedInLastUnits,before,%%System.Time),yyyy-mm-dd hh:nn:ss)'} "))
//(% = "")?():(%mWhereClause:=%mWhereClause AND )
// set the order, if any
(%tOrderBy = "")?():(%mWhereClause:=%mWhereClause ORDER BY %tOrderBy)
// and desc if checked
((%tOrderBy <> "") & (%cOrderDesc = True))?(%mWhereClause:=%mWhereClause DESC):()
You'll have to copy and poaste it into the Designer to read it properly, I'm afraid.


__________________
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
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #6 
I tried it in some other way.
I concattinated the string in Server side.
Checking each condition and

Quote:
var txtwhereClause="Condition1"
if(nextcondition?)
{
  txtwhereClause+="and Condition2";
}
ework.txtCondition=txtWhereClause;

After that I used %txtCondition in the Row property of the grid

Then also it didnt worked :(
0
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #7 
When I set it the way I found out that,
The date I pass comes as 'Fri Feb 1 00:00:00:00 UTC+0530 20080'
Is the erro occurrs because of this? As when it querries the SQL, the format doesntmatch?
I'm  in need of this and I'm not having an idea why this happens

Thanks
Anoop
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #8 
If you see the example I posted you will see how dates should be formatted.

__________________
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
toddgreve

Avatar / Picture

Senior Veteran
Registered:
Posts: 313
Reply with quote  #9 
Also make sure that your variable is initialized when you open your form to something valid for the query, otherwise you will get an error at the point you open the form.

__________________
Sometimes I make sense, and sometimes I know what I am talking about, sometimes ... ;)

These comments are provided of my own volition, expresses only my viewpoint, and does not represent Metastorm's “official” position.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #10 
oooops! Forgot about that - thanks, Todd!

__________________
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
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #11 
Yes.
I was having this error always I open my form.
Thanks all :)
One more question .
I'm doing all these in server ide script. So if I wanna format my date (now i'm getting error on this),
Can i do as ework.Format(ework.dtmDate,mm-dd-yyyy)?

Thanks
Anoop
0
anoopvj

Veteran
Registered:
Posts: 130
Reply with quote  #12 
Hey I do have a doubt. How we gonna check a date range"???

Greater than or Equal to and less than or equl o doent works.
If my date is 2/22/2008 and if I check as
Date >= 2/22/2008  and Date <= 2/22/2008 , it doesnt show the record
:(

Why?

Thanks
Anoop
0
abeebe

Member
Registered:
Posts: 29
Reply with quote  #13 
Is there a way to use a select statement in the where clause of a v7.6.4 grid?  I have come across a security vulnerability with using a memo field and being able to show the SQL code on the form.  Instead of tryng to put it into a variable or field, I want to use a package and funciton in Oracle to process the where clause.  This has a lot of filter constraints and the reason for the dynamic where clause.

I have a funciton that receives a few parameters, and when I run the SQL code, it works fine and gives me what I am looking for, but Metastorm doesn't seem to run the select statement inside the where clause of the grid (or even a drop down).

Thanks for any help or ideas.

Alan
0
BMellert

Guru
Registered:
Posts: 688
Reply with quote  #14 
Do you mean to provide a complete select statement which you use in a where clause (along the lines of select cols from tab where [provided select]), or having a select, generally predefined except for some parameters, in a where clause (along the lines of select cols from tab where exists (select x from tab2 where [p1] = x and [p2] = y))?

Worse case, off the top of my head, I'd think if you created a database procedure which builds your statement and returns the resulting set, I'd think that would work.  From MBPM's perspective, it should just be a query.  (Conjecture without knowing the context of the kind of where/select you are talking about.
0
abeebe

Member
Registered:
Posts: 29
Reply with quote  #15 
After more trial and errors, I was able to get it to run.  Basically I had to put the (%SelectSQL("...) into the rows field of the grid.  The problem I had originally when I tried it that way was that I wasn't getting any results in my grid.  The stupid part (yes, all mine) was that I forgot that our development environment didn't have any data for the locaiton I was looking at.  Once I changed it to a location that had data, it returned data as it should.

Now it will be off to our security team to see if the flaw still exists.  If so, we are back to the drawing board since this will be a Metastorm flaw that the SQL code is easily decryptable from the source code.

Thanks for the reply.

Alan
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!