OFBiz › OFBiz - User

query condition in Subtract 2 timestamps

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

Mamdouh Kaadan-2

query condition in Subtract 2 timestamps

Dear all

How can I make SQL query in OFBiz based on subtract 2 timestamps,

By example:

select * from table

where FIRST_ACTION_DATE- Second_ACTION_DATE <'+00 12:00:00.000000'

How can I make the previous statement using the delegator methods??

Thanks in advance

==================================

Mamdouh Kaadan

Software Engineer

IT - Application

Areeba SYRIA

Mobile: +963-94-222763

Office : +963-94-222222 Ext: 2288

E-mail:[hidden email]

David E Jones-2

Re: query condition in Subtract 2 timestamps

Use a view-entity with a complex-alias field (either in an XML view-
entity definition, or in a DynamicViewEntity).

For details about how to do stuff like this you might find the
recently completed advanced framework training package from Undersun
helpful. There are details here:

http://www.undersunconsulting.com/ecommerce/control/product/
~category_id=USC_PROMO/~product_id=OFBADVFWKPKG

-David

On Aug 9, 2006, at 11:43 AM, Mamdouh Kaadan wrote:

> Dear all
>
>
>
> How can I make SQL query in OFBiz based on subtract 2 timestamps,
>
> By example:
>
> select * from table
>
> where FIRST_ACTION_DATE- Second_ACTION_DATE <'+00 12:00:00.000000'
>
>
>
> How can I make the previous statement using the delegator methods??
>
>
>
> Thanks in advance
>
> ==================================
>
> Mamdouh Kaadan
>
> Software Engineer
>
> IT - Application
>
> Areeba SYRIA
>
> Mobile: +963-94-222763
>
> Office : +963-94-222222 Ext: 2288
>
> E-mail:[hidden email]
>
>
>
>
>

Mamdouh Kaadan-2

RE: query condition in Subtract 2 timestamps

In reply to this post by Mamdouh Kaadan-2

Dear David

Thank you for your fast response, I tried to use complex alias, but the
problem is that the first date is from one table and the other date is
from another table, and entity-alias attribute is required in alias
node,

I tried the following:

<view-entity entity-name="IndicatorIssueAndIssueItem"
package-name="org.ofbiz.crm.crm" title="">

<member-entity entity-alias="ISS" entity-name="Issue"/>

<member-entity entity-alias="ISSI" entity-name="IssueItem"/>

<alias entity-alias="ISS" name="issueId"/>

<alias entity-alias="ISS" name="departmentId"/>

<alias entity-alias="ISS" name="callingNumber"/>

<alias entity-alias="ISS" name="dateTimeStarted"/>

<alias entity-alias="ISS" name="partyId"/>

<alias entity-alias="ISSI" name="issueItemId"/>

<alias entity-alias="ISSI" name="caseId"/>

<alias entity-alias="ISSI" name="specId"/>

<alias entity-alias="ISSI" name="statusId"/>

<alias entity-alias="ISSI" name="otherNumbers"/>

<alias entity-alias="ISSI" name="urgent"/>

<alias entity-alias="ISSI" name="currentDeptId"/>

<alias entity-alias="ISSI" name="additionalRemark"/>

<alias entity-alias="ISSI" name="lineTypeId"/>

<alias entity-alias="ISSI" name=" firstActionDate"/>

<alias entity-alias="ISSI" name=" SolvedDate"/>

<alias entity-alias="" name="firstActionDifference">

<complex-alias operator="-">

<complex-alias-field entity-alias="ISSI"
field="firstActionDate"/>

<complex-alias-field entity-alias="ISS"
field="dateTimeStarted"/>

</complex-alias>

</alias>

<alias entity-alias="" name="SolvedDifference">

<complex-alias operator="-">

<complex-alias-field entity-alias="ISSI"
field="SolvedDate"/>

<complex-alias-field entity-alias="ISS"
field="dateTimeStarted"/>

</complex-alias>

</alias>

<view-link entity-alias="ISS" rel-entity-alias="ISSI">

<key-map field-name="issueId"/>

</view-link>

</view-entity>

Can you help me please, thanks

==================================

Mamdouh Kaadan

-----Original Message-----
From: David E. Jones [mailto:[hidden email]]
Sent: Wednesday, August 09, 2006 6:55 PM
To: [hidden email]
Subject: Re: query condition in Subtract 2 timestamps

Use a view-entity with a complex-alias field (either in an XML view-

entity definition, or in a DynamicViewEntity).

For details about how to do stuff like this you might find the

recently completed advanced framework training package from Undersun

helpful. There are details here:

http://www.undersunconsulting.com/ecommerce/control/product/

~category_id=USC_PROMO/~product_id=OFBADVFWKPKG

-David

On Aug 9, 2006, at 11:43 AM, Mamdouh Kaadan wrote:

> Dear all

>

>

>

> How can I make SQL query in OFBiz based on subtract 2 timestamps,

>

> By example:

>

> select * from table

>

> where FIRST_ACTION_DATE- Second_ACTION_DATE <'+00 12:00:00.000000'

>

>

>

> How can I make the previous statement using the delegator methods??

>

>

>

> Thanks in advance

>

> ==================================

>

> Mamdouh Kaadan

>

> Software Engineer

>

> IT - Application

>

> Areeba SYRIA

>

> Mobile: +963-94-222763

>

> Office : +963-94-222222 Ext: 2288

>

> E-mail:[hidden email]

>

>

>

>

>

David E Jones-2

Re: query condition in Subtract 2 timestamps

So, is this working for you, or what error are you getting? Leaving
the attribute blank should be okay, though I don't know that I've
tested it...

-David

On Aug 9, 2006, at 3:12 PM, Mamdouh Kaadan wrote:

> Dear David
>
>
>
> Thank you for your fast response, I tried to use complex alias, but
> the
> problem is that the first date is from one table and the other date is
> from another table, and entity-alias attribute is required in alias
> node,
>
> I tried the following:
>
> <view-entity entity-name="IndicatorIssueAndIssueItem"
> package-name="org.ofbiz.crm.crm" title="">
>
> <member-entity entity-alias="ISS" entity-name="Issue"/>
>
> <member-entity entity-alias="ISSI" entity-name="IssueItem"/>
>
>
>
>
>
>
> <alias entity-alias="ISS" name="issueId"/>
>
> <alias entity-alias="ISS" name="departmentId"/>
>
> <alias entity-alias="ISS" name="callingNumber"/>
>
> <alias entity-alias="ISS" name="dateTimeStarted"/>
>
> <alias entity-alias="ISS" name="partyId"/>
>
> <alias entity-alias="ISSI" name="issueItemId"/>
>
> <alias entity-alias="ISSI" name="caseId"/>
>
> <alias entity-alias="ISSI" name="specId"/>
>
> <alias entity-alias="ISSI" name="statusId"/>
>
> <alias entity-alias="ISSI" name="otherNumbers"/>
>
> <alias entity-alias="ISSI" name="urgent"/>
>
> <alias entity-alias="ISSI" name="currentDeptId"/>
>
> <alias entity-alias="ISSI" name="additionalRemark"/>
>
> <alias entity-alias="ISSI" name="lineTypeId"/>
>
> <alias entity-alias="ISSI" name=" firstActionDate"/>
>
> <alias entity-alias="ISSI" name=" SolvedDate"/>
>
>
>
> <alias entity-alias="" name="firstActionDifference">
>
> <complex-alias operator="-">
>
> <complex-alias-field entity-alias="ISSI"
> field="firstActionDate"/>
>
> <complex-alias-field entity-alias="ISS"
> field="dateTimeStarted"/>
>
> </complex-alias>
>
> </alias>
>
> <alias entity-alias="" name="SolvedDifference">
>
> <complex-alias operator="-">
>
> <complex-alias-field entity-alias="ISSI"
> field="SolvedDate"/>
>
> <complex-alias-field entity-alias="ISS"
> field="dateTimeStarted"/>
>
> </complex-alias>
>
> </alias>
>
> <view-link entity-alias="ISS" rel-entity-alias="ISSI">
>
> <key-map field-name="issueId"/>
>
> </view-link>
>
> </view-entity>
>
>
>
> Can you help me please, thanks
>
>
>
> ==================================
>
> Mamdouh Kaadan
>
>
>
>
>
> -----Original Message-----
> From: David E. Jones [mailto:[hidden email]]
> Sent: Wednesday, August 09, 2006 6:55 PM
> To: [hidden email]
> Subject: Re: query condition in Subtract 2 timestamps
>
>
>
>
>
> Use a view-entity with a complex-alias field (either in an XML view-
>
> entity definition, or in a DynamicViewEntity).
>
>
>
> For details about how to do stuff like this you might find the
>
> recently completed advanced framework training package from Undersun
>
> helpful. There are details here:
>
>
>
> http://www.undersunconsulting.com/ecommerce/control/product/
>
> ~category_id=USC_PROMO/~product_id=OFBADVFWKPKG
>
>
>
> -David
>
>
>
>
>
> On Aug 9, 2006, at 11:43 AM, Mamdouh Kaadan wrote:
>
>
>
>> Dear all
>
>>
>
>>
>
>>
>
>> How can I make SQL query in OFBiz based on subtract 2 timestamps,
>
>>
>
>> By example:
>
>>
>
>> select * from table
>
>>
>
>> where FIRST_ACTION_DATE- Second_ACTION_DATE <'+00 12:00:00.000000'
>
>>
>
>>
>
>>
>
>> How can I make the previous statement using the delegator methods??
>
>>
>
>>
>
>>
>
>> Thanks in advance
>
>>
>
>> ==================================
>
>>
>
>> Mamdouh Kaadan
>
>>
>
>> Software Engineer
>
>>
>
>> IT - Application
>
>>
>
>> Areeba SYRIA
>
>>
>
>> Mobile: +963-94-222763
>
>>
>
>> Office : +963-94-222222 Ext: 2288
>
>>
>
>> E-mail:[hidden email]
>
>>
>
>>
>
>>
>
>>
>
>>
>
>
>

Mamdouh Kaadan-2

RE: query condition in Subtract 2 timestamps

In reply to this post by Mamdouh Kaadan-2

No, it is not working, as below the OFBiz doest not start at all, and without any error, I tried to run verbose mode but there is no error related to this field, and the system stop in the middle of startup
And when I delete entity-alias attribute, it gives an error that this attribute is missing, is there any work around like making the entity-alias filed optional??

Thanks

==================================
Mamdouh Kaadan

-----Original Message-----
From: David E. Jones [mailto:[hidden email]]
Sent: Wednesday, August 09, 2006 11:24 PM
To: [hidden email]
Subject: Re: query condition in Subtract 2 timestamps

So, is this working for you, or what error are you getting? Leaving
the attribute blank should be okay, though I don't know that I've
tested it...

-David

On Aug 9, 2006, at 3:12 PM, Mamdouh Kaadan wrote:

David E Jones-2

Re: query condition in Subtract 2 timestamps

Mamdouh,

I spent a bit of time looking into this. In SVN rev 430913 did a few
things:

1. added a view-entity in the example component called
ExampleStatusDetail that does something similar to the one you
created, this will be there for testing from now on and should help
avoid additional problems like this in the future

2. I'm not sure which version of OFBiz you are using, but with the
code before this commit I was getting an error message in the console
about why it was failing on startup; the reason was code that was
added as part of the entity cache improvements but that didn't handle
the complex-alias type field aliases; I changed this code to ignore
ComplexAlias type ModelAlias objects, ie not create a ModelConversion
object because at the minute it does not support complex aliases

The startup is fine now, but just don't cache these view-entities...
It may not complain (other than the startup warning), but the map
conversions won't work, so in memory comparisons and such won't work.

Could you test this out with what you're planning on doing and let me
know how it goes?

-David

On Aug 10, 2006, at 8:43 AM, Mamdouh Kaadan wrote:

> No, it is not working, as below the OFBiz doest not start at all,
> and without any error, I tried to run verbose mode but there is no
> error related to this field, and the system stop in the middle of
> startup
> And when I delete entity-alias attribute, it gives an error that
> this attribute is missing, is there any work around like making the
> entity-alias filed optional??
>
> Thanks
>
> ==================================
> Mamdouh Kaadan
>
>
>
> -----Original Message-----
> From: David E. Jones [mailto:[hidden email]]
> Sent: Wednesday, August 09, 2006 11:24 PM
> To: [hidden email]
> Subject: Re: query condition in Subtract 2 timestamps
>
>
> So, is this working for you, or what error are you getting? Leaving
> the attribute blank should be okay, though I don't know that I've
> tested it...
>
> -David
>
>
> On Aug 9, 2006, at 3:12 PM, Mamdouh Kaadan wrote:
>
>> Dear David
>>
>>
>>
>> Thank you for your fast response, I tried to use complex alias, but
>> the
>> problem is that the first date is from one table and the other
>> date is
>> from another table, and entity-alias attribute is required in alias
>> node,
>>
>> I tried the following:
>>
>> <view-entity entity-name="IndicatorIssueAndIssueItem"
>> package-name="org.ofbiz.crm.crm" title="">
>>
>> <member-entity entity-alias="ISS" entity-name="Issue"/>
>>
>> <member-entity entity-alias="ISSI" entity-name="IssueItem"/>
>>
>>
>>
>>
>>
>>
>> <alias entity-alias="ISS" name="issueId"/>
>>
>> <alias entity-alias="ISS" name="departmentId"/>
>>
>> <alias entity-alias="ISS" name="callingNumber"/>
>>
>> <alias entity-alias="ISS" name="dateTimeStarted"/>
>>
>> <alias entity-alias="ISS" name="partyId"/>
>>
>> <alias entity-alias="ISSI" name="issueItemId"/>
>>
>> <alias entity-alias="ISSI" name="caseId"/>
>>
>> <alias entity-alias="ISSI" name="specId"/>
>>
>> <alias entity-alias="ISSI" name="statusId"/>
>>
>> <alias entity-alias="ISSI" name="otherNumbers"/>
>>
>> <alias entity-alias="ISSI" name="urgent"/>
>>
>> <alias entity-alias="ISSI" name="currentDeptId"/>
>>
>> <alias entity-alias="ISSI" name="additionalRemark"/>
>>
>> <alias entity-alias="ISSI" name="lineTypeId"/>
>>
>> <alias entity-alias="ISSI" name=" firstActionDate"/>
>>
>> <alias entity-alias="ISSI" name=" SolvedDate"/>
>>
>>
>>
>> <alias entity-alias="" name="firstActionDifference">
>>
>> <complex-alias operator="-">
>>
>> <complex-alias-field entity-alias="ISSI"
>> field="firstActionDate"/>
>>
>> <complex-alias-field entity-alias="ISS"
>> field="dateTimeStarted"/>
>>
>> </complex-alias>
>>
>> </alias>
>>
>> <alias entity-alias="" name="SolvedDifference">
>>
>> <complex-alias operator="-">
>>
>> <complex-alias-field entity-alias="ISSI"
>> field="SolvedDate"/>
>>
>> <complex-alias-field entity-alias="ISS"
>> field="dateTimeStarted"/>
>>
>> </complex-alias>
>>
>> </alias>
>>
>> <view-link entity-alias="ISS" rel-entity-alias="ISSI">
>>
>> <key-map field-name="issueId"/>
>>
>> </view-link>
>>
>> </view-entity>
>>
>>
>>
>> Can you help me please, thanks
>>
>>
>>
>> ==================================
>>
>> Mamdouh Kaadan
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: David E. Jones [mailto:[hidden email]]
>> Sent: Wednesday, August 09, 2006 6:55 PM
>> To: [hidden email]
>> Subject: Re: query condition in Subtract 2 timestamps
>>
>>
>>
>>
>>
>> Use a view-entity with a complex-alias field (either in an XML view-
>>
>> entity definition, or in a DynamicViewEntity).
>>
>>
>>
>> For details about how to do stuff like this you might find the
>>
>> recently completed advanced framework training package from Undersun
>>
>> helpful. There are details here:
>>
>>
>>
>> http://www.undersunconsulting.com/ecommerce/control/product/
>>
>> ~category_id=USC_PROMO/~product_id=OFBADVFWKPKG
>>
>>
>>
>> -David
>>
>>
>>
>>
>>
>> On Aug 9, 2006, at 11:43 AM, Mamdouh Kaadan wrote:
>>
>>
>>
>>> Dear all
>>
>>>
>>
>>>
>>
>>>
>>
>>> How can I make SQL query in OFBiz based on subtract 2 timestamps,
>>
>>>
>>
>>> By example:
>>
>>>
>>
>>> select * from table
>>
>>>
>>
>>> where FIRST_ACTION_DATE- Second_ACTION_DATE <'+00 12:00:00.000000'
>>
>>>
>>
>>>
>>
>>>
>>
>>> How can I make the previous statement using the delegator methods??
>>
>>>
>>
>>>
>>
>>>
>>
>>> Thanks in advance
>>
>>>
>>
>>> ==================================
>>
>>>
>>
>>> Mamdouh Kaadan
>>
>>>
>>
>>> Software Engineer
>>
>>>
>>
>>> IT - Application
>>
>>>
>>
>>> Areeba SYRIA
>>
>>>
>>
>>> Mobile: +963-94-222763
>>
>>>
>>
>>> Office : +963-94-222222 Ext: 2288
>>
>>>
>>
>>> E-mail:[hidden email]
>>
>>>
>>
>>>
>>
>>>
>>
>>>
>>
>>>
>>
>>
>>
>