Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
lqc1

Member
Registered:
Posts: 13
Reply with quote  #1 
I have a Business Object to count the number of folders for a process.

This works fine:

SELECT COUNT(*) AS numFolder
FROM efolder 
WHERE eMapName = 'MyProcess'
  AND eCreationTime < SysDate

But this doesn't work, it fails to generate the variable name:
SELECT COUNT(*) AS
numFolder
FROM efolder 
WHERE eMapName = '
MyProcess'
  AND eCreationTime < @pEndDate  --@pEndDate is a datetime

Neither does this:
SELECT COUNT(*) AS
numFolder
FROM efolder 
WHERE eMapName = '
MyProcess'
  AND eCreationTime < TO_DATE(@pEndDate
,'mm/dd/yyyy') --@pEndDate is text

Has anyone seen this?  Any suggestions for a workaround...

Stephen



0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #2 
With the second item, are you sure you have the parameter type set as a datetime? This is the most common error in my experience.

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

Avatar / Picture

Guru
Registered:
Posts: 113
Reply with quote  #3 
In the second instance you are using minutes instead of months. in the formatter it would be (MM/dd/yyyy). I would consider using the second item and then where the assignment of the value occurs then use the formatTime(MM/dd/yyyy).

This may not be exactly the right syntax but will do the job.

0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #4 
I have to say that the only way I would ever recommend is using the standard parameters. Faffing about with formatting dates to text for the DBMS to interpret as a date was left behind in version 7.

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

Guru
Registered:
Posts: 688
Reply with quote  #5 

Quote:
Originally Posted by KarlD
In the second instance you are using minutes instead of months. in the formatter it would be (MM/dd/yyyy). I would consider using the second item and then where the assignment of the value occurs then use the formatTime(MM/dd/yyyy).

It appears lqc1 is using Oracle syntax in which case 'mm/dd/yyyy' is compete valid, presuming the input parameter is formatted that way an a character.  (Were he using BPM's date format function, then MM/dd/yyyy would be required.)

However, I agree that the first thing to do is make sure  pEndDate is classified as a datetime value in the BO and not a character string.  Then the first should work as its in "native" format.
0
lqc1

Member
Registered:
Posts: 13
Reply with quote  #6 

I was able to work around this issue by issuing the SQL Query from a Server Side Script.

[Promote(PromotionTargets.ExpressionBuilder)]
[Category("UserCategory")]
public static int getTotalFolderProcessed(string dtmStart, string dtmEnd)
{

    MetastormDefault connMstm = null;
    try
    {
        connMstm = new MetastormDefault();
        string sSQL = "SELECT COUNT(*) FROM EFOLDER WHERE eMapName = 'MyProcess' AND eCreationTime >= TO_DATE('" + dtmStart + "','mm/dd/yyyy') AND eCreationTime <= TO_DATE('" + dtmEnd + "','mm/dd/yyyy')";
        return Mstm.SelectSql(connMstm, sSQL).Integer;

    }
    catch(Exception ex)
    {
      ...

    }
    finally
    {
        connMstm = null;    
    }   

}
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #7 
We always use the standard SQLArg() parameters, and it always works. I see no need to make it more complicated!

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

Guru
Registered:
Posts: 688
Reply with quote  #8 
I've used standard SqlArg myself.
However, I did notice that using ProcessContext.CurrentTime and a local stored variable to do something like a datediff, I had an issue in that CurrentTime has the GMT offset (-05:00) while local variables always seem to be stored zero (server) based (00:00).  A bit of a hassle actually.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!