Record Filtering Using "OR" Condition

icetea7icetea7 Member Posts: 3
I have the following and dunno how to resolve it.

I want to filter record by Column A & Column B using OR condition. The output SQL should look like this WHERE ColumnA = 'xxx' OR ColumnB = 'xxx'

But the SETRANGE can only generate WHERE ColumnA = 'xxx' AND ColumnB = 'xxx' (not OR condition); SETFILTER is for the same column.

What is the best way to handle this?

Would greatly appreciate if anyone can help?

Comments

  • BeliasBelias Member Posts: 2,998
    I only imagine a workaround, sorry
    TBMyTable.SETRANGE(ColumnA, 'xxx');
    if TBMyTable.findfirst then begin
      repeat
        TBMyTrueTable.get(TBMyTable.primarykey);
        TBMyTrueTable.mark(true);
      until TBMyTable.NEXT = 0;
    end;
    
    TBMyTable.RESET;
    TBMyTable.SETRANGE(ColumnB, 'xxx');
    if TBMyTable.findfirst then begin
      repeat
        TBMyTrueTable.get(TBMyTable.primarykey);
        TBMyTrueTable.mark(true);
      until TBMyTable.NEXT = 0;
    end;
    
    TBMyTrueTable.markedonly(true);
    

    TBMyTable and TBMyTrueTable are 2 record variables of the same record.

    I don't think this code will generate an "OR" statement, but the result will be the one you want.
    Obviously this method is slow. Hope someone have something better
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • WaldoWaldo Member Posts: 3,412
    I wouldn't mark records, because that is slow indeed... especially on SQL Server.
    You could also work with Temp tables.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • girish.joshigirish.joshi Member Posts: 407
    I may be butchering DeMorgan's rules, but let me see if I can get this:

    we want Column A = 'xxx' OR Columng B = 'xxx' and we can only use ANDs.

    so that should be equivalent to NOT ( Column A <> 'xxx' AND Column B <> 'xxx' )

    So we can get this:

    table.setfilter( A, '<>xxx') ;
    table.setfilter( B, '<>xxx') ;

    table.findset ;

    and we have all the records in the table that are NOT in our filter criteria. Now we have to get that set's complement. There are two ways I see to do that:

    1. We can MARK all of these records. Clear the filters. And then toggle the marks on all of them. The remaining marked records are in our intersection of two sets.

    2. Get all of table withotu filters into a temporary record. Loop through the 'not' set and delete those records from teh temporary record.

    Nice thing about (1) is that you are never looping through records, so for large data sets it should be really fast. (2) avoids using marks, and for most filtering criteria should be faster than Belias method.
  • WaldoWaldo Member Posts: 3,412
    Hm :-k .

    Still, if you're looping your complete table ... I think it's a better idea to avoid the extra (first) database call with opposite filters, loop the complete set anyway and put it directly into a temp table.
    Afterwards, show the temp table.

    Someone should benchmark it. :wink:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • girish.joshigirish.joshi Member Posts: 407
    That's a good point, perhaps the marking solution is better.


    Perhaps the simplest thing of all is to create a new boolean field that is true if either field has the given value.
  • WaldoWaldo Member Posts: 3,412
    The advantage of working with temp tables is that there are no unnecessary database calls. Everything is done in the memory of the client.

    Marking records, then "markedonly" gives you a database call that is unpredictable on SQL Server.
    Marking with a boolean will write every update to the database... .

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • girish.joshigirish.joshi Member Posts: 407
    Not marking with a boolean when you do the call, marking with a boolean through normal transactions. That is to say putting code in the onvalidate triggers of the fields. You are sort of doing some preprocessing for the query.

    Also perhaps both solutions could be combined. -- marking a temporary record instead of the database.

    Of course the temp table would need to be populated in the first place -- and but we have do that in either scenario.
  • icetea7icetea7 Member Posts: 3
    Thank for sharing your knowledge/experince, at least i know there is no straightforward way to resolve it.

    But i still wondering that why Navision Team do not buid in this functionality as it seems quite common.
  • ara3nara3n Member Posts: 9,255
    There is a thread about new features.
    http://www.mibuso.com/forum/viewtopic.php?t=22245

    I've asked for something something like this.
    Table.setrange(Field1,'value1') OR
    Table.setrange(Field1,'value1') ;
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrownbbrown Member Posts: 3,268
    If you are using the SQL version, you could create a view that returns the result set and link a Navision table to the view. Your view could use the OR logic.
    There are no bugs - only undocumented features.
Sign In or Register to comment.