Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Constrain OR function not working?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Default Constrain OR function not working?

    Why does this work:

    Code:
    Constrain History.Date EQ dStart

    but this does not:

    Code:
    Constrain History as ((History.Date EQ dStart) or (History.Date2 EQ dStart))


    Both compile with out issues. The first one returns a record correctly -- the second one returns nothing.

    What am I doing wrong?

  2. #2
    Join Date
    Feb 2009
    Location
    Copenhagen, Denmark
    Posts
    1,708

    Default Re: Constrain OR function not working?

    >> Both compile with out issues.

    And that they shouldn't, but alas they do. From the help of the constrain command talking about the "constrain as" variant:

    "These Boolean expressions may not contain any local variables."

    Rewrite to either use a function call (to return the value of dStart) or a date property.

    -Sture

  3. #3
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    9,141

    Default Re: Constrain OR function not working?

    Guy,

    These constrain as expressions may not contain local variables because the local variable is no longer available at the time the expression is evaluated.

    CONSTRAIN AS should be avoided as much as possible due to performance penalties; the filter cannot be offloaded to SQL or used in index optimized finds. In your case it wouldn't help anyway as you compare on different columns. Good news; if you use constrain as and your database is SQL you can optimize the find/filter via psSQLFilter.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  4. #4
    Join Date
    Feb 2009
    Location
    Copenhagen, Denmark
    Posts
    1,708

    Default Re: Constrain OR function not working?

    Note also that "constrain as" may be ineffective if there is "far between" records that satisfy the boolean expression. Don't let it catch you by surprise.

    -Sture

  5. #5
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    9,141

    Default Re: Constrain OR function not working?

    Guy,

    I moved the thread to the Windows application sub-forum as it has nothing to do with DataFlex Reports.

    PS: Next to what Sture says about local variables the use of EQ (or NE, GE, GT, LE, LT) in expressions is not as we like to see the code. About 30 years back it was added to make the transition from commands with parameters to expressions easier but we should have not done this as it makes it harder to understand the code for newbies, people pickup code from others.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  6. #6
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,738

    Default Re: Constrain OR function not working?

    Code:
      Property Date pdStart
    Object oHistory_DDis a cHistory_Datadictionary
    
      Procedure OnConstrain
         Forward Send OnConstrain
         Constrain history as(History.Date = pdStart(Self) or History.Date2 = pdStart(Self))
      End_Procedure
    End_Object
    A few notes:
    - The property needs to be defined in the parent of the DD
    - Indeed 'constrain as' is inefficient, but honestly if the table does not contain too many records it is all fine. If OTOH you have a lot of records, it might not perform well.
    - You might regret using a field name like 'Date'. It is a reserved word in many environments and does not clarify anything. (What kind of date is it?). The fact that there is also a 'date2' is already a hint. It will all work though.

  7. #7

    Default Re: Constrain OR function not working?

    Quote Originally Posted by Evertjan Dondergoor View Post
    Code:
      Property Date pdStart
    Object oHistory_DDis a cHistory_Datadictionary
    
      Procedure OnConstrain
         Forward Send OnConstrain
         Constrain history as(History.Date = pdStart(Self) or History.Date2 = pdStart(Self))
      End_Procedure
    End_Object
    A few notes:
    - The property needs to be defined in the parent of the DD
    - Indeed 'constrain as' is inefficient, but honestly if the table does not contain too many records it is all fine. If OTOH you have a lot of records, it might not perform well.
    - You might regret using a field name like 'Date'. It is a reserved word in many environments and does not clarify anything. (What kind of date is it?). The fact that there is also a 'date2' is already a hint. It will all work though.
    HEY! That worked! Thank you SO much for posting that code sample! I was doing everything right, but I didn't know to put "(SELF)" in there, and I was really struggling with it! Thanks again!

  8. #8

    Default Re: Constrain OR function not working?

    Oh! A description of "Date" and "Date2"...

    "Date" is "Tear Out And Haul Off And Set Posts Date"

    "Date2" is "Hang Fence Date"

    Obviously, I started out with "Date" and "Tear Out And Haul Off And Set Posts Date" was much too long -- so I just went with "Date".

    A few weeks later, I realized I needed a second date -- so I just put "Date2".

    I know it's not the best nomenclature, but I'm the only software developer here, and I might consider it to be in my best interests to make my code difficult for others to understand.

  9. #9
    Join Date
    Feb 2009
    Location
    Somewhere in Vermont, USA - unless I'm not
    Posts
    9,658

    Default Re: Constrain OR function not working?

    Obscure names is one thing - I once worked on a 2.3 system where all subroutines were animal names (gusub elephant) & labels were place names (goto London) - but "date" is a reserved word in any SQL version I know of - so it has to get [] around it in MS SQL code or `` around it if MySQL or Oracle. Also - if you were to ever go to MySQL don;t use # in a field name (Invoice# or the like) as MySQL really hates that.

    This is not in the DF code but in any embedded SQL or stored procedures.
    Garret

    All my life,I always wanted to be somebody. Now I see that I should have been more specific.


  10. #10
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    7,832

    Default Re: Constrain OR function not working?

    In my experience, this can only come back to bite you in the {expletive deleted}. Most of the time, the poor coder who needs to read my code after a few years is ME.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •