TO_CHAR to TO_DATE problem addressed


Recently found this issue on an internal forum and found the solution given by Jobinesh.. Thought I could share the info here

Problem description:

“I’m trying to apply a view criteria programmatic way here. When the bind variable for “StartDate” (which is a VO attribute) uses just a “=”. then at runtime the VC gets appended to the query using a TO_DATE for the bind var, which is as expected. However, when the bind variable for “StartDate” uses just a “>=” / “<“>” . then at runtime the VC gets appended to the query using a TO_CHAR for the bind var, which is not that we require. Is there any way to get a TO_DATE for this kind of comparison as well?”

Please take a look at the following sample method :-

public Row getSampleHeaderVORecord(Date activeDate) {
 ViewObjectImpl SampleHeaderVO = getSampleHeader1();
 ViewCriteria viewCriteria = SampleHeaderVO.createViewCriteria() ;
 ViewCriteriaRow vcr1 = viewCriteria.createViewCriteriaRow() ;
 vcr1.setAttribute("StartDate", ">="+(activeDate.));
 viewCriteria.add(vcr1);
 SampleHeaderVO.applyViewCriteria(viewCriteria);
 SampleHeaderVO.executeQuery();
 return SampleHeaderVO.next();
 }

Do let me know.

Sample query that appears at runtime :-

SELECT *WHERE ( ( (TO_CHAR( ForecastHeaderEO.START_DATE, 'yyyy-mm-dd') > '0007-04-02' ) ) )

The TO_CHAR in the last line is what I’m referring to. Is there any way to get a TO_DATE there?

Solution:


ViewObjectImpl SampleHeaderVO = getSampleHeader1();
 ViewCriteria viewCriteria = SampleHeaderVO.createViewCriteria() ;
 ViewCriteriaRow vcr1 = viewCriteria.createViewCriteriaRow() ;
 ViewCriteriaItem vci = vcr1.ensureCriteriaItem("StartDate");
 vci.setOperator(JboCompOper.OPER_ON_OR_AFTER);
 vci.setValue(activeDate);
viewCriteria.add(vcr1);
 SampleHeaderVO.applyViewCriteria(viewCriteria);
 SampleHeaderVO.executeQuery();

Advertisements

3 thoughts on “TO_CHAR to TO_DATE problem addressed

  1. Rajesha

    This solution not working for date fields. Sample case:
    ViewObjectImpl SampleHeaderVO = getSampleHeader1();
    ViewCriteria viewCriteria = SampleHeaderVO.createViewCriteria() ;
    ViewCriteriaRow vcr1 = viewCriteria.createViewCriteriaRow() ;
    ViewCriteriaItem vci = vcr1.ensureCriteriaItem(“StartDate”);
    vci.setOperator(JboCompOper.OPER_BETWEEN);
    vci.setValue(firstDate);
    vci.setValue(secondDate);
    viewCriteria.add(vcr1);
    SampleHeaderVO.applyViewCriteria(viewCriteria);
    SampleHeaderVO.executeQuery();

    (here firstDate and secondDate are java.sql.Date objects)

    The where clause produced by the above code is.
    to_char(SampleHeaderEO.StartDate,’yyyy-mm-dd’) BETWEEN ‘2012/01/01’ AND ‘2012/02/02’

    The expected where clause would be.
    SampleHeaderEO.StartDate BETWEEN ‘2012/01/01’ AND ‘2012/02/02’

    How to fix this issue.

    Reply
    1. Beto

      I would like to find every table that has the contentDelivery aruitbtte either missing or set to the default of “whenAvailable” How would I do that? We need to change those to be either lazy or immediate so that our performance testing tool sees the same number of http requests on the playback as it saw on the recording.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s