Hi All

I am having problems building a select. I thought I'd try reduce but it doesn't seem to work consistently.

I have included some example code.

The first reduce seems to work where I select the whole table

The one for owned, says it works, ie reduce_ok is still true

but when I get to the select by, it just selects the whole table

If I feed it a property number it seems to work ok ie I just get results for that property.

I am beginning to think it is one of those "I can't see for looking" bugs.

Any help appreciated. Reduce seems like such a nice idea but I just can't get it to work.

* mode=0 ;* new criteria for existing cursor

property_groups=OWNED' ;* this is actually a parameter, so can vary

prop_nos='

default_cursor=' ;* assigned by first mode 1 reduce ?

no_sort='

For csr_ptr=1 To 8

	ClearSelect csr_ptr

Next csr_ptr

num_selected=0

red_table=CHARGES'

* first select the entire charges table so we have something in the cursor to reduce

*

reduce_ok='

mode=1 ;* next avail cursor

select_all='

REDUCE(select_all, no_sort, mode, red_table, default_cursor,reduce_ok)

num_selected=@RECCOUNT ;*not sure @RECCOUNT has the count for the reduce here

If reduce_ok and prop_nos Then

* if I give it a valid property no, this bit actually works and

* we process just the charges for this property

nos_temp=prop_nos

Swap @VM with '" "' in nos_temp

red_criteria= 'WITH {PROPERTY_NO} EQ ': QUOTE( nos_temp)

mode=2 ;* add criteria for existing cursor

 	REDUCE(red_criteria, no_sort, mode, red_table, default_cursor, reduce_ok)

End

if reduce_ok and property_groups then

* this is the bit that doesn't work.

group_temp=property_groups

Swap @VM with '" "' in group_temp

red_criteria= 'WITH {PROPERTY_GROUP} EQ ': QUOTE(group_temp)

* ie red_criteria=WITH {PROPERTY_GROUP} EQ "OWNED"'

* the debugger goes into this code but the end result doesn't reflect it.

mode=2 ;* add criteria for existing cursor

 	REDUCE(red_criteria, no_sort, mode, red_table, default_cursor, reduce_ok)

end

* Sort the records to include.

If reduce_ok Then

sort_type=PROPERTY_GROUP' : @FM : 'PROPERTY_NO' : @FM : 'TENANT_CODE'

select red_table by sort_type using default_cursor Else

 	        error=1

 		mesg=Error in Select"

 		gosub display_error

 	end

 	num_selected=@RECCOUNT		;* I think @RECCOUNT may be set correctly here but not sure.

• We seem to have selected the whole table at this point although the sort has worked.

end else

 	mesg=Reduce failed with criteria:"

 	mesg := "|red_criteria"

gosub display_error

End

Hi Scott,

I can't remember having to ever refine reduce criteria further after an initial gathering. I suggest gathering info. in one hit and sort both reduce statement and subseqent select by the same.

Once you get that working you could try using the different modes if you still wanted to.

Regards,

James

eg.

mode=1 ;* add criteria for existing cursor
group_temp=property_groups
Swap @VM with '" "' in group_temp
red_criteria=WITH {PROPERTY_GROUP} EQ ': QUOTE(group_temp)
sort_type=PROPERTY_GROUP' : @FM : 'PROPERTY_NO' : @FM : 'TENANT_CODE'
mode=1 ;* new
REDUCE(red_criteria, sort_type, mode, red_table, default_cursor, reduce_ok)
if reduce_ok then
  select red_table by sort_type using default_cursor then
    eof=false$
    readnext id using default_cursor else eof=true$
    loop while not(eof)
      read your.rec from table.var, id etc....
      ** note that if you are sorting or selecting a mv field the returned
      ** id will be @vm delimited showing the matching/sorted position
      ** ie. id=key, id will be position
      readnext id using default_cursor else eof=true$
    repeat
  end else
    error=2
    mesg=Error in Select" 
    gosub display_error
  end
end else
  error=1
  mesg=Error in Reduce" 
  gosub display_error
end

Hi James

The trouble with that is when I am using several lists in my select the select is executed bracketed up strictly left to right which produces an inappropriate result. (this happens with RLIST, so far I have had trouble figuring out what happens with Reduce)

Remember that {PROPERTY_NO} is not the same as {PROPERTY_GROUP}

So when you want to get something like

{PROPERTY_NO}=1 OR 2 OR 3

AND

{PROPERTY_GROUP}=OFFICE" OR "RETAIL" OR "RESIDENTIAL"

if you combine them

ie

red_criteria=WITH {PROPERTY_NO} EQ "1" "2" "3" AND WITH {PROPERTY_GROUP} EQ "OFFICE" "RETAIL" "RESIDENTIAL"'

what you actually get returned is

(((((
      (WITH {PROPERTY_NO} EQ "1" )
        OR WITH {PROPERTY_NO} EQ "2")

      OR WITH {PROPERTY_NO} EQ "3" )

    AND WITH {PROPERTY_GROUP} EQ "OFFICE")

   OR WITH {PROPERTY_GROUP} EQ "RETAIL")

OR WITH {PROPERTY_GROUP} EQ "RESIDENTIAL")

Ie you will get all retail and residential properties ignoring what property number they might be. Which isn't what I want.

And as far as I know Reduce and RLIST don't like addition of bracketing to indicate grouping of evaluaton. Leastways it didn't work when I tried it.

So while I could combine everything in one reduce statement (or rlist), I can't be sure of its reliability.

I think part of my problem may be related to the fact that some of the actual select criteria are calculated fields based on a date in common. I read somewhere that the use of @ variables can affect the way reduce (or yield) work. I took all the yields out of my code but it hasn't helped.

If I could get the cursor stuff to work, so I had two lists (against two different tables) then I could dump all the "as at date" calculated fields, which on a large database, plow the system to a grinding slow.

But I can't get the reduces to work.

Are there any limits on how many times you can add criteria to a reduce (using mode 2), is there a limit on how many keys can be returned when the list is evaluated? Is there a limit on how big the combined criteria can get?

Scott, LMS

Scott,

Bracketing with parentheses work fine here in the Northern Hemisphere:

( ( … ) AND ( … ) )

You being south of the Equator have to turn the parentheses the other way:

) ) … ( AND ) … ( (

Honest!

- Oystein -

Scott,

Sorry about that other posting. Just had to do something to counter that early Monday morning feeling.

And seriously: Parentheses do work. Both with Reduce and Rlist selects. Just pile them on. Better with too many than too few. E.g, I never do

but

( ( ) ( ) ) ( )

I remember others having reported problems with parentheses (not you, was it?), but the only problem I can recall having had was with too few spaces in the neighbourhood. I don't know exactly where they are needed, but at least you must have one between "(" and "WITH". To be on the safe side I surround all my parentheses with spaces.

If you're still not successful try expand that

WITH {PROPERTY_NO} EQ "1" "2" "3"

clause:

( ( WITH {PROPERTY_NO} EQ "1" ) OR ( WITH {PROPERTY_NO} EQ "2" ) OR ( WITH {PROPERTY_NO} EQ "3" ) )

Btw - at one point I stopped using Reduce and went for Rlist instead. I don't remember exactly why. But at least there's another thing you can try.

- Oystein -

Hi Oystein

So is it snowing yet?

The following is a short HTML test on formatting

We have to adjust for the clocks running backwards
Or at least a day ahead of the rest of you

The water goes down the plug holes the opposite way
   (Truth is it doesn't care)
The storms and tornados go the opposite way.  
We have a whole set of different stars to yours


And we have cold meat and salads for the feasting season and cold beer.  I still like the pudding hot though, with icecream. And cream. And brandy sauce and custard and raspberries.

Think I'd better go get some extra excercise in advance.

Scott the silly season.

Hi O.

That wasn't supposed to stick a blank line in between each line, must be something in the site parsing system. Ah well. At least it preserves the indenting (

 and 

)

J.

J,

Must be the site parsing. Perhaps you can avoid the blank lines if you just string all the lines together with

or between them, and avoid CR LF. I haven't tried that on

 or 
<code>
 stuff, but I had to do something similar once with a table.

Any luck at all with your Reduce?

- Oystein -

PS. No snow yet where I live. Bergen is known for rain, not snow. And with the changing climate it will be even more rain and less snow.

PSS. Light shower HTML test:


<code>
   /        /       /            /
  /     /       / /             /
       /       /       /    /     /
   /     /        /   /    / 
        /        /             /
          /        /    /     /
    /             /               /
   /         /            /      /
            /       /  /      /
    /     /        /         /
   /         /           /
]

J,

It worked with s and no returns! Except it seems my rain dissolved a near the end.

- Oystein -

Hi O

Something seems to be going wrong between the

Reduce(…,…,default_cursor…)

and the

select mytable by sort_criteria using default cursor then

blah

end else

error

end

ie the select .. by, no matter what I did in the previous reduce, is going away and grabbing everything on the table not just the reduced stuff.

Strange things are happening with the @REDUCTION_DONE and @RECCOUNT and @CURSORS variables.

@RECCOUNT doesn't change for the duration of the program. Why doesn't the clearselect initialise it. It just stays the same as what ever it was when the program started which makes my progress meter reporting x of y records sick.

@REDUCTION_DONE also stays set to 1 for the duration ie I can't tell if the select by updated anything.

I can't tell if the original REDUCE worked although the reduce_ok flag at the end is set to 1, which is supposed to be ok. Although the original sample code I had from Richard Bright suggested flag=1 means error (which is quite possible). If it does mean error, how do I figure out what the error is and how do I fix it.

I think this may be why you ditched reduce and went back to RLISTS.

I wonder how on earth Sprezz got it to work.

Janet

Scott,

Damn my faulty memory! I had the same problem as you a few years back! And never solved it! (I must have had them all. Next time you have a problem I suggest you search the lists with the additional clause "… AND OYSTEIN".) See this thread: .

In the final posting of that thread I mention that I changed from Reduce to RList. I think the reason must have been the problems I had doing two Reduce's in a row, even if I don't explicitly say so. At that time I wanted to do two queries in a row, the last one narrowing in on the results of the first one, just like you. I haven't grasped your reason for doing two separate queries, but from what I've read of your postings so far you could just as well combine them into one and live with that. But my reason at least was I wanted one particular query to run first, because in general I got a faster execution then. You see I found out that if I combined my two queries into one with AND it wasn't certain the first one was executed first. The relevant SSPs decided the order, and didn't always make the smartest choice. Here are some details on that:

But what is really your problem now? I know you can't get two Reduce's in a row to work. But what about combining them into one? Have you had any progress with bracketing?

And if not - have you tried the same combined statement with Rlist instead? (You must of course change the syntax then, replacing the {} stuff with the bare field names.)

Btw - as a bonus - here are a few more of "my" threads on doing queries:

Learning about saving/reactivating lists to/from SYSLISTS:

Problems with sort order and case sensitivity:

- Oystein -

Hi Oystein

It doesn't seem to matter if I have two reduce statements or not. I would prefer to be able to do the select to reduce the number of records it needs to sort instead of sorting before selecting but the thing seems to insist on sorting first which is a waste of time in my book especially when some of the sort fields are symbolic/calculated fields.

I do the reduce with all the criteria combined and it seems to have worked but the associated system variables like

@REDUCTION_DONE, @CURSORS, @RECCOUNT don't seem to be set properly, well @CURSORS has the select criteria in it. If I add the sort criteria to the reduce, it doesn't put that in the @CURSORS record as far as I can tell.

Then when I try to do the Select By using the_cursor_number_returned_by_the_reduce, it's like I never did the reduce. It doesn't care that there was a reduce. It does the same even when there is only one reduce and even when I put the sort criteria in both.

The select does do the sort correctly but not the criteria which is not good enough.

It's frustrating because Sprezz and Richard Bright seem to have gotten the thing working but I can't. Also none of the system variables that I could use to check what is going on seem to be working or updated as the sprezz doco says they should.

So I will stick to sequential RLIST stuff for now. Again the main reason for having lots of them sequentially is that if they enter a specific criterion that reduces the number of records selected significantly, I can then reduce the number of records that need sorting and sometimes the number of symbolic fields that need evaluating by putting them in the last RLISTs processed.

I had no way of checking if the bracketing was working for the reduce as I could not get any information out of the reduce. Not even a why isn't it working. I don't think I need to bother with bracketing for the RLISTS because I just do them sequentially anyway.

Janet

Janet,

I can understand your frustration with Reduce, but hope you're satisfied with doing a sequence of Rlist calls.

Btw: Our reasons for doing a sequence of Rlist calls are similar. We want to reduce the amount of work and processing time by delaying a task which would be time-consuming if executed first.

We both have a complex query A AND B AND C. But while you do a sequence of primitive queries A, B, C, each narrowing in the result of the former, I've just factored out one of my primitive queries. My first Rlist does A AND B. The second and final one does C.

I just mention it because… …eh… …I don't know really. I was about to say there might be an advantage to let OI decide the details on how to perform A AND B. It might sometimes find out doing B first is more efficient. But I realize I'm contradicting myself, because the reason I did C last was OI wasn't very good at finding the best execution order.

Btw2 - (and now I don't know why I'm still typing - the queries I'm talking about are from home-made QBF-like windows. The A and B clauses are from fields filled in by the user (actually there might be anything from zero to a dozen fields filled in, not just two). The C clause is fixed. C by itself would usually select half the rows, so it's important it get run last.

- Oystein -

Hi O

It is interesting that we haven't heard from Sprezz or WinWin on this topic. I was hoping that there was something fundamental that I had done wrong or a way of working around the problem (perhaps using less or no calculate/symbolic fields) that would fix it.

I can't understand why we can get RLIST to work but the REDUCE that RLIST is supposedly built around refuses to work.

It is quite annoying because it would have been nice to have two sets of cursors going at once on my select because some of the calculated fields evaluations which are already slow could be done a lot more efficiently if I could just select the relevant bits of the second file after I had decided which bits of the first file to process.

J

Always there with the difficult questions

How?

Magic….

Years of practise….

We assume you've read our little article on REDUCE.

A few things with reduce.

REDUCE does like parenthesis. Construct the WITH clause just like a TCL select.

Most of the @Vars aren't in play with reduce. @REDUCTION.DONE and @RECCOUNT are only used in the PERFORMS. They are set after the initial readnext is completed and the LIST records are written out.

When trying to refine the select, the MODE variable must be set to 2 for it to work.

With an error condition, flag will be set to False$ (0). Check @FILE.ERROR and STATUS() for error code information.

It's really not all that hard.

The Sprezzatura Group

World Leaders in all things RevSoft

Hi Sprez

Stuff knows how I missed your post. (something to do with DNS errors and a weekend?) Yes I read all your stuff on reduce. On the brackets, it was handy when Oystein told me it cared about how you space things and I wasn't sure which side of the WITH you had to put the bracket. I eventually got it right, well at least the reduce ok flag stays set to 1/ok.

Still have problem where the select bit ignores all the criteria I had been building up with the Reduce calls. Even with mode 2 (for subsequent reduce calls).

Why? According to your post everything is working, the flag stays set to 1 (ie ok).

And the reccount not being resolved until after the readnext is processed makes it very difficult for me to display a progress meter ie x records processed of y total records. I can't suck all the keys into a variable because there is more than 64K worth of keys.

I have tried a whole lot of different ways of processing this, and even with most simple criteria construction, the thing (select using) that activates the list created(?) by the reduce, ignores the reduction critera. It also tries to sort first on the whole humungus file instead of doing the select/reduce first.

So now there are two of us that can't get reduce + select using to work.

Scott

Scott,

So now there are two of us that can't get reduce + select using to work.

Don't include me in the count. That problem of mine was so long ago, it's impossible to say if it was OI's fault or mine. I can get your code to work. I took the code from your original posting and modified it as little as possible, just to get it to fit with a couple of my own tables - one small table and one large (] 30.000 varchar keys, key length ]= 13). To be certain I got the correct result I counted the rows with a readnext loop.

Now isn't that good news?

Btw - why is it really you do that initial Reduce without any criteria? (Not that it seems to matter. I kept it when I tested your code on my tables.)

- Oystein -

Hi Oystein

So did your reduce criteria have lots of calculated (symbolic) fields in it including one that looks up information based on a date stored in common? The code I have is truly horrible ie we start with a charge and then look up the payments which have multivalued lists of charges and then only use payments where the payment date is before the report date. It would be so nice if the charge-payment items were in a separate table but they are not.

I am stuffed if I know why it doesn't work. I thought it was working on my small database and not the big one, but I was wrong it worked with neither.

The original version of my stuff had the reduction on the date stuff (calculated fields) first (because it had to be done to everything) but I moved it to last because it is so slow in the hope that the calculation would be done on a lesser number of records. But reduce does not seem to work that way. I probably should have used the code Richard Bright provided along the lines of if mode=new then mode=add (except that wouldn't have figured out which was first now would it) better would have been to init mode to new, then set it to add at the end of every if test for criteria to apply ie

mode=0

if criteria1 then

reduce with criteria1

mode=2

end

if criteria2

reduce with criteria2

mode=2

end

I think I will try it again without the ugly criteria and see if that gets anywhere. Then at least I will have isolated the problem a bit more.

Scott.

Hi Oystein

Isn't this fun. At least I am getting quicker at checking possibilities.

Using the structure where everything is bracketed up into one huge criteria so I only run the reduce once:

If I exclude the "as at date" symbolic fields it works fine.

If I include this, it is as if I never did the reduce with criteria at all. And strangely the reduce_ok flag is set to 1 after the dodgy reduce. Go figure.

So I am reduced to thinking the reduce only works sometimes. If I had more of an idea why it craps out with the calculations I might be able to work around it. As it is, I am back to RLIST.

Scott.

Scott,

The fields I tried with were very simple - no symbolics.

But if you still get Rlist to work but not Reduce - isn't it time you scrap the latter and go for the former?

Or do you fear there's some dodgy programming in the symbolics - something that'll show itself sooner or later with Rlist too?

FWW:

Let's say one has a set of criteria C1, C2, C3, …, Cn, where some of the criteria might be set, and some not. Here's your way of handling it:

mode=0

if C1 then

. reduce with C1

. mode=2

end

if C2 then

. reduce with C2

. mode=2

end

…

…

if Cn then

. reduce with Cn

. mode=2

end

Exactly the same can be achieved with Rlist:

(clear cursors)

if C1 then

. do Rlist with C1 to TARGET_ACTIVELIST$

end

if C2 then

. do Rlist with C2 to TARGET_ACTIVELIST$

end

…

…

if Cn then

. do Rlist with Cn to TARGET_ACTIVELIST$

end

As I mentioned in an earlier posting I do the latter. Except that I collect most of my criteria into one SELECT before I do my first Rlist, and save just Cn for my second Rlist:

(clear cursors)

Clauses="

if C1 then

. if Clauses then Clauses := " AND "

. make a WITH clause Clause from C1

. Clauses := "( " : Clause : " )"

end

if C2 then

. if Clauses then Clauses := " AND "

. make a WITH clause Clause from C2

. Clauses := "( " : Clause : " )"

end

…

…

if Cn-1 then

. if Clauses then Clauses := " AND "

. make a WITH clause Clause from Cn-1

. Clauses := "( " : Clause : " )"

end

Statement=SELECT MYTABLE " : Clauses

do first Rlist with complex Statement to TARGET_ACTIVELIST$

if Cn then

. do final Rlist with Cn to TARGET_ACTIVELIST$

end

- Oystein -

Hi Oystein

I started with RLIST, and actually had a bit of spare time to experiment with Reduce in the hope that it could make the select work faster. This experiment was encouraged by people who thought that reduce should be faster. Reduce isn't faster and it has an accuracy problem.

At least I learnt something esoteric.

1. Reduce doesn't seem to like the calculated fields I use, especially the ones that involve looking up stuff in common and a multivalue list from another table.

I think I may have blown some size limit somewhere. Charges can be paid by many different payments and Payments can be used to pay many different charges. I only wanted payments for a given charge that had been paid before a given date and was also trying to select only charges that hadn't been fully paid before a given date. What a mess, but RLIST does it fine.

2. Even when you build onto the original Reduce statement with mode 2 (add to existing) none of it gets evaluated until you do the select by so you don't decrease the number of records that need to be processed and you get no corresponding speed advantage.

3. Bracketing works provided you include the WITH keyword inside the bracket and use plenty of spacing around the brackets ie

red_criteria=( ( WITH {A}=X' ) AND ( WITH {B}=Y' ) )

remember the curly brackets around the field names too. I managed to get this bit wrong in lots of different ways. Got it right eventually but then it wouldn't work if A was complicated.

Summary.

I don't know what the limits of Reduce are, but I know it has them so I won't be using it. Which is status quo really.

Scott

FWIW I never use reduce - if I can't do it with btree.extract I use RLIST…

Just one of the Sprezz group's opinion…

The Sprezzatura Group

World Leaders in all things RevSoft

Who's "I" - are you guys afraid of something?

Bob Watson

You can find out who posts if you're clever. There's a slight difference in the animated ZZ logo Aaron, Andrew and Carl use.

- Oystein -

Nope, just where there is likely to be a difference of opinion within the group we distance ourselves from the group stance. The borg we are not . And Oystein - what about Steve?

As Oystein says you can generally tell from both the logos and the grammatical style who is posting. FWIW "I" in this case was Andrew.

The Sprezzatura Group

World Leaders in all things RevSoft

Nope, just where there is likely to be a difference of opinion within the group we distance ourselves from the group stance. The borg we are not . And Oystein - what about Steve?

I haven't seen his logo yet; that's why I didn't mention him.

As Oystein says you can generally tell from both the logos

Now you're pulling my leg, aren't you?

and the grammatical style who is posting.

That's true. You can often guess from the style or the words used.

FWIW "I" in this case was Andrew.

E.g, Andrew often writes "FWIW". Was and is.

(Note the bastard doesn't reveal himself voluntarily this time either.)

- Oystein -

You know what else isn't working consistantly, this discussion board. It keeps eating my messages, turning back to my name with empty contents.

Let's try this again…….

The limits of reduce are the same with those of PERFORM SELECT. All reduce/select statements eventually get turned into some combination of the reduce, select and select.index.

The reduce syntax is tricky, complicated and very non-intuitive. It takes some getting used to. Once you do get used to it, you'll find yourself using it more and more, as it's speed increase is highly noticable. The only downside is the potential loss of @RECCOUNT.

Give the US office a call and we can try and get you going. When we're done, we'll post back the resolutions.

And on a side note, contrary to the opionions expressed by other members of the collective, we do have only one opinion on any topic.

The Sprezzatura Group

World Leaders in all things RevSoft

Was that you Aaron?

I am not sure that giving the USA a call from Australia would be productive, especially when I would rather put that kind of money towards renewing our works subscription, things are unbelieveably tight around here.

Imagined conversation:

LMS: Is that the USA Sprezz office, can I talk to someone (Aaron?) about Reduce statements

Sprezz: Yes, I'll just put you on hold…(15minutes later) Hi this is Insert Tech name here. What is the problem with "Reduce".

LMS: It doesn't work reliably, have you read the thread on the discussion board?

Sprezz: Not all of it, I will just catch up (another 15 minutes later).

At this point I can't imagine the question, but it must involve code and table structures and stuffed if I know how to describe them over the phone any better than I have in the discussion group. My own response to the code is our system needs redesigning, the charge-payment things should be factored out of the payment record so you can select by charge id and date and retrieve and process just the relevant information. Of course I'd never get the money or the people for that either.

I have gotten the reduce syntax to work for simple selects where the criteria data stored on the actual table and simple lookups, but the ones where it goes into for each charge select all income records created before the report date that have that charge id in their charge id list and sum up the amount paids for that charge and compare that to the amount charged and if it is not zero then report this record. Ie a many to many to many relationship where many is in the order of tens to 100's each time for thousands of charges…I am sure it is a size limit thing, although that doesn't explain why the rlist works (although I have that broken up into several rlists).

And I need the @RECCOUNT to make the progress meter work.

Scott.

I created a small table and filled it up with known quantities.

Subroutine FillScott( Void )

 

Open 'SCOTT' to hScott else Null

 

for i=1 to 1000

   rec=i

   if i/3=int(i/3 ) then

      rec=1

   end else

      rec=0

   end

   if i/4=int(i/4 ) then

      rec=1

   end else

      rec=0

   end

   if i/5=int(i/5 ) then

      rec=1

   end else

      rec=0

   end

   write rec on hScott,i else Null

next

         

return

Next, I ran this program against the new file. This is an example of using MODE=2 to add to an existing cursor. Notice the use of the extended ReadNext syntax, as this is the cause of most reduce errors, since the ReadNext cannot find the correct cursor to obtain the information.

Subroutine SelectTest( Void )

 

 

Declare Subroutine Reduce

 

For Cursor=0 to 8

  ClearSelect Cursor

Next Cursor

 

SelectStatement=WITH {F4} EQ 1"

SortList =ID"

Mode =1

TableName =SCOTT"

ReduceFlag =0

 

Reduce( SelectStatement, SortList, Mode, TableName, Cursor, ReduceFlag )

 

If ReduceFlag Then

   SelectStatement=WITH {F3} EQ 1"

   Mode=2

   Reduce( SelectStatement, SortList, Mode, TableName, Cursor, ReduceFlag )

   If ReduceFlag Then

   

      Select TableName By SortList Using Cursor Then

          Keys="

          Done=0

          Loop

             ReadNext Key Using Cursor By AT Else

                Done=1

             End

          Until Done

             Keys=Key

          Repeat

          debug

      End Else

        

          Debug

          x=1

      

      End

    

   End Else

                       

      Debug

      x=1

                                 

   End

   

End Else

 

   Debug

   x=1

End

The Sprezzatura Group

World Leaders in all things RevSoft

Let's try this without all the junk….

Program 1

Subroutine FillScott( Void )

 

Open 'SCOTT' to hScott else Null

 

for i=1 to 1000

   rec=i

   if i/3=int(i/3 ) then

      rec&2]=1

   end else

      rec&2]=0

   end

   if i/4=int(i/4 ) then

      rec&3]=1

   end else

      rec&3]=0

   end

   if i/5=int(i/5 ) then

      rec&4]=1

   end else

      rec&4]=0

   end

   write rec on hScott,i else Null

next

         

return

Program 2

Subroutine SelectTest( Void )

 

 

Declare Subroutine Reduce

 

For Cursor=0 to 8

  ClearSelect Cursor

Next Cursor

 

SelectStatement=WITH {F4} EQ 1"

SortList =ID"

Mode =1

TableName =SCOTT"

ReduceFlag =0

 

Reduce( SelectStatement, SortList, Mode, TableName, Cursor, ReduceFlag )

 

If ReduceFlag Then

   SelectStatement=WITH {F3} EQ 1"

   Mode=2

   Reduce( SelectStatement, SortList, Mode, TableName, Cursor, ReduceFlag )

   If ReduceFlag Then

   

      Select TableName By SortList Using Cursor Then

          Keys="

          Done=0

          Loop

             ReadNext Key Using Cursor By AT Else

                Done=1

             End

          Until Done

             Keys& -1 ]=Key

          Repeat

          debug

      End Else

        

          Debug

          x=1

      

      End

    

   End Else

                       

      Debug

      x=1

                                 

   End

   

End Else

 

   Debug

   x=1

End

The Sprezzatura Group

World Leaders in all things RevSoft

Hi Code-Sprezz

I think I said I have no problems where I am doing a select using reduce based on one file.

I am not sure if the problem is caused by too much processing, or the common variable or what. I just know if I include this field sym1: amount outstanding, in the reduce sequence the reduce ignores all criteria. If I use the field with the rlist sequence it works correctly. I figured this out by using different fields in the reduce criteria. Worked fine until it came to the extra complexity. while the Rlist (provided I did one call per field criteria) worked fine. If I tried to include criteria for different fields the RLIST didn't work properly either but in a different way to the Reduce. Ie the RLIST had a bracketing problem (that was before I found out about spaces around the brackets).

Scott, LMS

(note all my code is pseudo, and will not necessarily compile as written)

Try two files where you have something like

file1: (Charges)

key1

amt_charged

file2_keys: list of keys to file2

amts_paid: list of amts_paid for each key to file2 for this charge

dates_paid: look up file2 using the keys and get the dates

file2: (Payments)

key2

list of keys to file1

list of amounts paid for each key

date_paid

on file1 have a symbolic (calculated field) sym1 that goes something like (a new country and western tune)

sym1:

* total all payments paid before common_date

* and calc outstanding as at common_date

* made this up, but it goes and gets a date from common

* that was put there via the report parameters form.

common_date=get_date_from_common("report_date")

file2_key_list={file2_keys}

date_paid_list={dates_paid}

amt_paid_list={amts_paid}

num_file2_keys=count(file2_key_list, @VM) + (file2_key_list # 0)

amount_paid=0

for key_ptr=1 to num_file2_keys

  this_key2=list_of_payments

  date_paid=date_paid_list

  if date_paid 

  end

end

amount_charged={amt_chrgd}

amount_outstanding=amount_charged - amount_paid

@ANS=amount_outstanding

View this thread on the forum...