Filter a table based on different sources 1

[GiacoF]

I'm working on a complex task which is giving me serious reasonings.
I developed a logic according to which by selecting/check and/or valuing the input of a series of checkboxes and textfields, a filter string (cTextSearch) is composed which is then passed to a method (set Filter) which then essentially applies a SET FILTER TO to a TableAlias. So far so good... now I need to add a discriminant, i.e. I should also check in some points whether this table (cAliasName) contains "expiring" records, i.e. contracts that are expiring or have expired or not... The logic here is a bit different because I need to check before on all records, flag and then filtering the result on the table which theoretically could be filtered for other reason. SO all this need to be integrated with the current system in a way or another.
If I make that different without this query and logic(tried), the string of the SET FILTER TO togheter with the expiring record will become too long and FOX will return me stack error.

For this reason I wrote a procedure that makes a query and returns me a cursor, which I then take from the form (the procedure is an external prg) and would theoretically like to integrate into the current filter if present. In the cursor I flagged in a column with 0 and 1 those records that interest me (0 no 1 expiring).
Once I get to the set filter I'm trying to tell it "Filter the current string and also check whether the record with that contract code (unique CodContr) is valued at 0 or 1 in the cursor, if so include/exclude from the filter).
I don't know how to get out of it, I tried using separate filters or using LOCATE, but I think something is missing in the logic itself or I need a new idea/approach....

The cursor created is visible in the form (checked with BROWSE) and it has this form (sample..) ->
id_contr codcontr codcli email datainiz datafine codlistino listinocli duratamesi importocli autocreate codassegn nofattpec inscadenza
79 00069 000054 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00002 FALSO 0
7 00002 000056 xxxxxxx@xxxx.it 01/01/2022 31/01/2025 00002 50 36 50 FALSO 00002 FALSO 0
61 00052 000107 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00013 FALSO 0
45 00037 000158 xxxxxxx@xxxx.it 01/07/2022 31/07/2025 00002 50 36 50 FALSO 00003 FALSO 0
42 00034 000235 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
40 00032 000332 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 VERO 1
94 00084 000354 xxxxxxx@xxxx.it 01/10/2023 31/10/2026 00002 50 36 50 FALSO 00002 FALSO 0
134 00119 000364 xxxxxxx@xxxx.it 01/09/2021 30/09/2024 00003 70 36 70 FALSO 00006 FALSO 0
60 00051 000366 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00008 FALSO 0
29 00021 000381 xxxxxxx@xxxx.it 01/06/2022 30/06/2025 00002 50 36 50 FALSO 00002 FALSO 0
82 00072 000632 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00004 FALSO 0
84 00074 000648 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
41 00033 000668 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00011 FALSO 1

<-

The procedure VerifyScadenz:

PROCEDURE Verifica_Scadenze_PEC()

LOCAL crTemp, nPecDaysalert, nRecordScadenz, dToday, dStartDate, dEndDate, bHasScadenz, cFilter, crTempCursor 

nPecDaysalert = oApp.oConfig.PecDaysalert    
dToday = DATE()    
dStartDate = dToday - nPecDaysalert    
dEndDate = dToday + nPecDaysalert

crTemp = SYS(2015)

TableOpen("ContrattiPEC")
TableOpen("Clienti")

SET STEP ON 

SELECT ContrattiPEC.*, ;
  IIF(ContrattiPEC.DataFine < dToday OR ;
      (ContrattiPEC.DataFine >= dStartDate AND ;
       ContrattiPEC.DataFine <= dEndDate), 1, 0) AS InScadenza ;
FROM DbContratti!ContrattiPEC ;
INNER JOIN DbContratti!Clienti ;
  ON ContrattiPEC.CodCli = Clienti.CodCli ;
WHERE NOT Clienti.DisPec ;
  AND ContrattiPEC.DataFine = ;
    (SELECT MAX(DataFine) ;
     FROM DbContratti!ContrattiPEC c2 ;
     WHERE c2.CodCli = ContrattiPEC.CodCli) ;
INTO CURSOR (crTemp) READWRITE


nRecordScadenz = RECCOUNT(crTemp)

IF nRecordScadenz > 0      
    cUniqueName = "crScadenzePEC_" + ALLTRIM(STR(VAL(SYS(2015)), 10, 0))      

    SELECT * ;
    FROM (crTemp) ;
    INTO CURSOR (cUniqueName) READWRITE               

    SELECT (cUniqueName)
    INDEX ON CodCli TAG CodCli
    INDEX ON CodCli + DTOS(DataFine) TAG PecScad       

    bHasScadenz = .T.
ELSE   
    bHasScadenz = .F.
    cUniqueName = ""
ENDIF

RETURN cUniqueName
 
ENDPROC

In the method SetFilter of the form in which I'll filter the table... this is the basic version without the attempts done, so cScadenzeCursorName is the cursor received from procedure, cTextSearch is the string received (will be something like CodCli=1 AND/OR other conditions .... need to filter the string normally but do the comparison in the cursor with the final flag (0/1) that tells me which to take.

LPARAMETERS bResetFilter, cScadenzeCursorName
LOCAL cTextSearch, cFinalFilter, nPecDaysalert

cAliasName = ALLTRIM(This.cTableAlias)
ThisForm.bFilterOn = !bResetFilter
ThisForm.lNotvalidfilterdates = bResetFilter

ThisForm.Get_StrFilter(cAliasName, "")
cTextSearch = ThisForm.CfilterWhere
cFinalFilter = cTextSearch

IF !EMPTY(cFinalFilter)
       SET FILTER TO &cFinalFilter IN (cAliasName)
ENDIF

 

[tomk3]

Hello,

I would select the data into a cursor and show that.

Regards
tom

Maybe select a "basicsubset" (daterange) first then apply "filtering" (delete or flag in cursor) or select into cursor where .. with full filter
(text to ...)

 

[GiacoF]

How do you imagine to do that... see now the cursor has already the records filtered for dates and flagged 0/1... I should join with the records filtered in the other way (in the string cTextSearch) ?
Then to show that, how? Now the record source of my form is binding with cAliasName not the cursor.

Can you elaborate a bit more in my context?

 

[Chris Miller]

RETURN cUniqueName

Where is this returned to and what's done with it?

Does it end up being This.cTableAlias?
What is "This in that context?

You code opens more questions than it answers.

ThisForm.Get_StrFilter(cAliasName, "")
cTextSearch = ThisForm.CfilterWhere
cFinalFilter = cTextSearch

What's done in the Get_StrFilter() method?

The two lines afterwards could be done in one:

ThisForm.Get_StrFilter(cAliasName, "")
cFinalFilter = ThisForm.CfilterWhere

Or in the end you do the same as in

ThisForm.Get_StrFilter(cAliasName, "")
IF !EMPTY( ThisForm.CfilterWhere)
    SET FILTER TO (ThisForm.CfilterWhere) IN (cAliasName)
ENDIF

The only reason to copy a property with the where/filter clause into a variable and use macro substitution is, you want the filter to work independent on a (local) variable or property to exist, that's not a bad idea to do, but then I sense you wanted to add to the filter, but didn't, you just made two copies of ThisForm.CfilterWhere and used one of them. I think you wanted to combine two where clauses, then you have to do that, in the simplest case with condition1 + " and " + condition2. I can't tell you what you want, though.

Chriss

 

[Chris Miller]

Let me try to guess (once more - I actually hate that, but I do to get forward a little at least):

This query ends up being shown in the grid:

SELECT ContrattiPEC.*, ;
  IIF(ContrattiPEC.DataFine < dToday OR ;
      (ContrattiPEC.DataFine >= dStartDate AND ;
       ContrattiPEC.DataFine <= dEndDate), 1, 0) AS InScadenza ;
FROM DbContratti!ContrattiPEC ;
INNER JOIN DbContratti!Clienti ;
  ON ContrattiPEC.CodCli = Clienti.CodCli ;
WHERE NOT Clienti.DisPec ;
  AND ContrattiPEC.DataFine = ;
    (SELECT MAX(DataFine) ;
     FROM DbContratti!ContrattiPEC c2 ;
     WHERE c2.CodCli = ContrattiPEC.CodCli) ;
INTO CURSOR (crTemp) READWRITE

And then you want to apply an additional filtering, without doing another query, but by using SET FILTER.

Is that right?

Then that's it, there's nothing to worry about, that is possible.
The usage of READWRITE does not only mean that the result cursor becomes writable, i.e. you can use REPLACE, UPDATE, INSERT, APPEND etc or edit the data in grid cells.

The where clause of the SQL (NOT Clienti.DisPec AND ContrattiPEC.DataFine = ... etc. etc.) is applied and the aliis (crTemp) and finally the name that was in cUniqueName is now a cursor that has all records compliing to the where clause, it's not having a SET FILTER, so an additional filte can be applied with SET FILTER, as simple as that.

What's strange and non working in your code is the determination of the cUniquename:

 cUniqueName = "crScadenzePEC_" + ALLTRIM(STR(VAL(SYS(2015)), 10, 0))

VAL(SYS(2015)) will always be 0.

SYS(2015) returns an alphanumeric value that start with an underscore and VAL('_anything') is always 0.

Just use this:

cUniqueName = "crScadenzePEC"+SYS(2015)

I'm not even able to guess what you wanted to achive with all this poking around with VAL and STR.

Chriss

 

[Chris Miller]

And last not least some general informations about filters and where conditions in SQL:

Basically they do the same, what you need to know is:

About SET FILTER:
1. Applying a filter condition to a workarea by SET FILTER TO is doing that to that workarea, not the underlying DBF or TMP file (DBF(alias))
2. Querying from a table with a SET FILTER will not take that filter into account, because SQL will always (in words: always) reopen an alias in a new workarea where no filter applies - in short: a query always queries 100% of data of the tables in the from clause.
3. Querying INTO a cursor (alias name) - in short a workarea - creates a result set that's only having a SET FILTER condition set under special conditions, if you apply the NOFILTER or READWRITE clause to the SQL you get a resultset that's not just a filtered DBF. On such result cursors you can apply additional filtering with SET FILTER or you can do further SQL on them, because effectively the result is its own new independent DBF.
About WHERE clauses
4. As mentioned in 3 above only under very special conditions when VFP detects it can deliver the result you want by just applying a SET FILTER to a DBF you query from, the where clause becomes a SET FILTER, otherwise a where clause is applied during the query and the result is an independent DBF/TMP file that has the records complying to the WHERE conditions and no filter set and can be filtered additionally after the query with a SET FILTER or by doing further queries with WHERE clauses, anyway you like to do that. You'd only have a problem if you want to add a filter to a workare that already has a SET FILTER to it, because the last one just replaces the old one. Then you have to combine the filter to have an overall final filter. So applying the technique to do everything with SQL would get you to a small subset of records and further queries make smaller and smaller results. There is no need to combine the previous where clause as you only query from records that fulfill the previous where condition anyway. The only downside of that is you lose rushmore optimization for second level queries as you query from a reesult cursor that doesn't share or even inherit indexes from the original DBF constructed to optimize queries. It may or may not become important, as smaller resultsets don't gain much from rushmore optimizations and you still get a filter of a filter of a filter in as many levels as your user wants without any combining of previous with current filters.

To me it makes sense to have a baseline filtering with a query resulting in a small subset of all data like the data of the last 7 days, last month, one customer, one order, any substantial filtering that has a small subset of the complete data, on which you then apply further filtering with SET FILTER. Just make it clear for the users that a change of the filter conditions applied to the baseline query result will be applied to that baseline, so it's not like drilling down to less and less records. Or program combining filters so it becomes drilling further down. One simple way to provide that is store the filter the user applies and let him add to that himself all or only some previous conditions, i.e. give the user full hand on that. Depends onh your audience whether they would like this or other user interface ideas. But the reducing of records with a series of SQL queries that apply a current/new filter on the data as is is easier to program for such drilling down to less and less records, you then don't need to care for any previous filters/where conditions, they're included anyway and you start from the result set they have and each query adds to the filter automatically without the need to combine them into a single filter string.

One more suggestion: If you always want to profit from rushmore optimization enabled from the indexing of the original table, you would add to the where clause and make it more and more complex to query from the original DBF everytime, so your starting point is not a baseline query but the DBF itself. The results you get can profit from that only if there always is some condition that reduces the records vastly anyway, more and more complex when clauses or filters can lead to less and less performant queries or filtering by SET FILTER and cuase a sluggish user experience. The major topic of filtering is selectivity, always put the most selective part of a filter as the first part. But even applying such rules of thumb you can always easily sabotage a query or filter performance with an added OR othercondition that's not very selective and thus adds to the result, in the worst case scenario being that as good as the previous filter was, you introduce almost all data back into the result and it takes as long to query it as it takes to get the whole dbf copied with no filter. In the end more and more complex filter conditions with nesting brackets ANDs and ORs used wrong is the norm for the general user, even if they had a lesson about boolean algebra, because even knowing it you can easily go wrong.

I would therefore mostly recommend the drilling down on data by always querying the current cursor into a next cursor, I think that is also what tom sketched with his recommendation, you have what you have and it can't become more because of a misplaced bracket or OR instead of an AND. If y use gets lost and the resultset shjrinked to the wrong records, you could even offer an undo feature when you keep all result cursors and let the user go back and start from there, besides you can always enable to reset the outset to all data.

Chriss

 

[GiacoF]

I got many hints and informations from you that I cannot say thank you enough!
By the way to get to know more about this task..
Yes applying additional filtering to this query is what I want, but only if it's choosen but the user.

This procedure is being called in another form outside in which there's already a logic for filtering..
I call the procedure put in variable and proceed

cResult = Procedure()

... The results of the query are the records which I 'll need to filter and integrate in my actual logic.

As you see what I got in the query is that table with the last column as a flag inScadenza = 0/1.
When landing in the form, I take this 'cUniqueName' as a parameter and it seems good as a method to manipulate it later(also if I BROWSE, i see that content).

In the forms I'm using Aliases for the grid, the main is cAliasName in this case...

In this form there's already a logic which in pratice compose a string for a filter which then is included in a SET FILTER TO thatString condition. For now, the string is gradually composed in the getStrFilter method (cannot share it today as I'm outside) but basically what happens there is that I check various checkboxes to see if the user choose to filter or not and at the end, I'll have in cTextSearch the result (or CFilterWhere property), so a string similar to "CodCli=1 AND CodListino = X etcc etc).
Originally, when I apply SET FILTER TO this, the cAliasName table would get filtered.

Now the point: I need to add a new "condition" to the filter but I cannot do in the same way given that the filter string would become too long wiht stack issues(at least for me with this method). The user will have the opportunity to check a new checkbox and based on that I will include in this SET FILTER the new records(the flag column 0/1). I was not able to do a string on this condition because it becomes too long, so I decided to do as it is now.
Basically I want the actual logic + included the results of this procedure.

Example:

Original Filter String :

cTextSearch= "CodCli=1 AND CodListino = 2 AND ..."
SET FILTER TO cTextSearch....

Example if the new checkbox is settled to include the records of the cursore with flag=1:
SET FILTER TO cTextSearch AND (include records with flag inscadenza=1-> if not exluded by the other conditions!!!!
The same example can be with another checkbox value and the flag in scadenza=0 ... it's the logic that confuse me:

Other notes:
- Cursor has its own table structure, cAliasName too;
- I have codContr column in both;
- Filter is applied to cAliasName.

Hope my objective it is clearer now..

 

[Chris Miller]

I take this 'cUniqueNameì as a parameter and if I BROWSE, i see that content.

That doesn't answer my question.

the filter string would become too long wiht stack issues(at least for me with this method)

A string can be as long as 16MB, you surely never hit that limit.

Hope my objective it is clearer now.

It has become a llittle clearer, but not clear enough, yet.

I understand you want to extend a filter condition, and you surely can, there's no technical limit for strng variable lengths, if you set one to yourself in code, then break it, the technical limit for strings is 16MB, the technical limit of a command is 8192 Bytes. A condition that's having a few, say even 20 conditions about single fields, would maybe get around a few 100 bytes maybe KBs, it's hardly achieavable to hit the 8KB command length limit.

You can't split the filter in two parts and SET FILTER TO part1 followed by SET FILTER TO part2, that should have become clear. If you really struggle with the complexity of the filter conditions, then you have to bring it down to what's feasable.

As said, you can do a series of queries,

Select ... FROM table WHERE conditionspart1 into cursor temp1 readwrite
folloewd by
Select ... FROM temp1 WHERE conditionspart2 into cursor result readwrite

And you have applied both parts of the conditions.

Chriss

 

[GiacoF]

Thanks again!

So.

A string can be as long as 16MB, you surely never hit that limit.

I tried before to use the same logic and from that query return a string such as (CodCli =2 AND ...)OR(... AND....) OR.... got a stack overflow error, so I thought would be better to do as I'm doing now, but got lost when applying the filter (always get type/syntax errors or wrong records filtered). Thanks also for this explanation anyway

PRG

Procedure()
RETURN cUniqueName

FORM 1
Init Method:

cFilter = Procedure()

ShowForm2(p1,p2,cFilter)

-> this procedure launches the form in which there is the Talbe-grid that I want to filter (I use cAliasName). It returns the same cFilter...

[[ I think my intention was to pass the name (and all the content of the cursor) to use that later]]

-> Let's say now I have the cursor cUniqueName in the FORM 2 (in a method of that form for example)

The cursor
id_contr codcontr codcli email datainiz datafine codlistino listinocli duratamesi importocli autocreate codassegn nofattpec inscadenza
79 00069 000054 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00002 FALSO 0
7 00002 000056 xxxxxxx@xxxx.it 01/01/2022 31/01/2025 00002 50 36 50 FALSO 00002 FALSO 0
61 00052 000107 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00013 FALSO 0
45 00037 000158 xxxxxxx@xxxx.it 01/07/2022 31/07/2025 00002 50 36 50 FALSO 00003 FALSO 0
42 00034 000235 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
40 00032 000332 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 VERO 1
94 00084 000354 xxxxxxx@xxxx.it 01/10/2023 31/10/2026 00002 50 36 50 FALSO 00002 FALSO 0
134 00119 000364 xxxxxxx@xxxx.it 01/09/2021 30/09/2024 00003 70 36 70 FALSO 00006 FALSO 0
60 00051 000366 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00008 FALSO 0
29 00021 000381 xxxxxxx@xxxx.it 01/06/2022 30/06/2025 00002 50 36 50 FALSO 00002 FALSO 0
82 00072 000632 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00004 FALSO 0
84 00074 000648 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
41 00033 000668 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00011 FALSO 1


And I have cAliasName with these columns
CodCli | CodContr | CodListino | xxx | xxx | xxx

-> is filtered applying

SET FILTER TO mystring IN cAliasName

For example cString = "CodCli = 2 AND CodListino = 1"

I want now to check IN the records already filtered in cAliasName

->Need to add to the filtering logic all the records of the curson cUniqueName with flag inScadenza = 1 based on the common unique values in column CodContr (check if the relative CodContr in the cursor with Flag=1 is "included" already in the filtered table) so I show, otherwise no of course...

How would you achieve that?

 

[Chris Miller]

íf yopu only want to see records that are with InScadenza=1, then change your baseline query that way:

SELECT ContrattiPEC.*, ;
FROM DbContratti!ContrattiPEC ;
INNER JOIN DbContratti!Clienti ;
  ON ContrattiPEC.CodCli = Clienti.CodCli ;
WHERE (ContrattiPEC.DataFine < dToday OR ;
 (ContrattiPEC.DataFine >= dStartDate AND ;
  ContrattiPEC.DataFine <= dEndDate) AND ;
  NOT Clienti.DisPec ;
  AND ContrattiPEC.DataFine = ;
    (SELECT MAX(DataFine) ;
     FROM DbContratti!ContrattiPEC c2 ;
     WHERE c2.CodCli = ContrattiPEC.CodCli) ;
INTO CURSOR (crTemp) READWRITE

stack overflow error

You can get up to 127 levels deep, your filter clause producing procedure must be recursive and do a deep level recursion for that error to happen, rething your code in that aspect, you don't need that deep recursion to end up with 3 conditions.

Chriss

 

[GiacoF]

Hey Chris, just a quick update to share with you. At the end I kind of solve like that... I think it's not well written and contains lots of repetitive/useless code , but I share as well to give an idea of the logic I've.
Just a note trying the method cited above with a log string in a "SET FILTER TO " results in a Stack error as well and I think the memory is at minimum in this program,... How or where could I look to increase that stack memory?

SELECT (cScadenzeCursorName)
DO CASE    

CASE ThisForm.CheckScadenz.Value = 1 AND !bResetFilter    
SET FILTER TO inScadenza = 1       
 GO TOP       
  cScadenzPecString = ""        
	  SCAN            
		IF !EMPTY(cScadenzPecString)                
		cScadenzPecString = cScadenzPecString + " OR "           
		ENDIF            
		 cScadenzPecString = cScadenzPecString + "CodContr = '" + TRANSFORM(codContr) + "'"        
	ENDSCAN       

ThisForm.cFilterscadenz = cScadenzPecString   


 CASE ThisForm.CheckScadenz.Value = 0  AND !bResetFilter
         

cScadenzPecString = ""           


 SET FILTER TO inScadenza = 1            
 
 SCAN
 [indent]IF !EMPTY(cScadenzPecString)                    
 cScadenzPecString = cScadenzPecString + " OR "               
 ENDIF[/indent]                
  
  cScadenzPecString = cScadenzPecString + "CodContr = '" + TRANSFORM(codContr) + "'"            
 
 ENDSCAN            

 ThisForm.cFilterscadenz = cScadenzPecString
 cScadenzPecString = IIF(EMPTY(ThisForm.cFilterscadenz),"","!(" + ThisForm.cFilterscadenz + ")")        
 ThisForm.cFilterscadenz = cScadenzPecString
 
 OTHERWISE		

cScadenzPecString = ""	
ThisForm.cFilterscadenz = cScadenzPecString

ENDCASE 


ThisForm.Get_StrFilter(cAliasName, "")    
cTextSearch = ThisForm.cFilterWhere    
         
IF !EMPTY(cScadenzPecString)    
cFinalFilter= cFinalFilter + IIF(EMPTY(cTextSearch),cScadenzPecString , cTextSearch + " AND (" + cScadenzPecString + ")")    
ELSE
	cFinalFilter = cTextSearch     
ENDIF        

IF !EMPTY(cFinalFilter) AND !bResetFilter


cCommand = "SET FILTER TO " + cFinalFilter + "IN (cAliasName)"    
&cCommand

	
SELECT (cAliasName)
GO TOP IN &cAliasName 

ELSE

SET STEP ON 
 
 SET FILTER TO IN (cAliasName)
 GO TOP IN &cAliasName
 ThisForm.CheckScadenz.Value = 2
 ThisForm.bFilterOn = .F.

 

[Chris Miller]

Let me see if I understand this right, just looking at one first piece of the filter condition you create:

cScadenzPecString = ""           

 SET FILTER TO inScadenza = 1            
 SCAN
     IF !EMPTY(cScadenzPecString)                    
        cScadenzPecString = cScadenzPecString + " OR "               
     ENDIF
     cScadenzPecString = cScadenzPecString + "CodContr = '" + TRANSFORM(codContr) + "'"            
 ENDSCAN

You build up a string that will be like

CodContr = 'A' OR CodContr = 'B' OR CodContr = 'C' OR ... OR CodContr = 'X' OR CodContr = 'Y' OR CodContr = 'Z'

Your filter condition just get's longer, it's only expanding all the single CodContr values that are seen with the FILTER inScadenza = 1. Why on Earth?
Why don't you just use inScadenza = 1?

Your problem is not stack memory, your problem is just the filter getting too long.

Even if you want to have all the single CodContr values in your filter instead of inScadenza = 1 for whatever reason, there are other simpler ways to formulate this with IN or INLIST, but this is just awful.

Chriss

 

[GiacoF]

The thing is that in this way is the only way I was able to use that...

The fundamental problem in my thought is that inScadenza flag is not in the table I want to filter, and since that I want to combine the filters, I need a common reference (in my imind) whihc I found in that CodContr...
I imagine there could be a different way but unfortunately don't work...

It's true that my filter is getting too long for the case = 0 (if I use the same method) and so I get this "stack error".

I would be more than happy to use other methods please don't blame me for this shy and awful attempts

 

[Chris Miller]

Instead of building up a list in code, you could also do what's more straight forward for a database programmer and build this list in a cursor:

SELECT codContr From (cScadenzeCursorName) Where inScadenza = 1  into cursor filtervalues
Index on codContr tag filtertag

Then the code condition will become

Set filter to Indexseek( CodContr, .f., "filtervalues","filtertag")

That means where CodContr of some record is in the list of filtervalues, checked by an indexseek that does not even need to move to the record, it's just looking into the filtertag index.


Chriss

 

[Chris Miller]

inScadenza flag is not in the table I want to filte

Well, then you can use an inner join query with the cursor that has the inScadenza = 1 and the other table liike this:

Select * From othertable alias t2 Inner Join (cScadenzeCursorName) alias t1 On t2.CodContr = t1.CodContr And t1.inScadenza = 1

Chriss

 

[Chris Miller]

I get this "stack error".

This macro expansion

&cCommand

will fail if cCommand is longer than 8192 bytes, as I mentioned above somewhere.

And the error you get is "Line is too long", there is no mention of the stack in that error, see for yourself with this sample:

Local lcFilter, lnI

Create Cursor Test (iNum integer autoinc)
Append Blank
Append Blank

lcFilter ="iNum=1"
For lnI = 1 to 1000
  lcFilter = lcFilter + " or iNum="+Transform(lnI)
EndFor

cCommand = "Set Filter To "+lcFilter
&cCommand

So when you get a stack related error, that has another cause and you will need to investigate that separately, but one thing is for sure, too long filters can't work and you can't raise the limit for a line of code.

The solution using another cursor (filtervalues) with Indexseek() or using the cursor you already have for an inner join SQL will not lead to lengthy code and have no limits on the number of records with inScadenza = 1 other than the 2GB limit of tables. It's always better to solve something of such a nature by usage of data itself. So the filter you wanted is already existing in the form of the cursor you have, use it in one of many forms with the help of joins (inner or outer), functions like inlist(), indexseek(), lookup().

It's okay, of course, that you don't know what you don't know. But your spider senses must tell you that a lengthy filter condition is no approrpriate solution, even far before you hit the brick wall by getting errors.

Chriss