d3/win select list processing via DataBasic question

[David Knight]

This one's a puzzler.

In my app I have a COMMON array variable which contains a list of item keys built via another process. [This was done for performance issues to save the system always having to select from an entire large file.] Let's call it a sub-set.

I now want to utilise that sub-set within a Databasic routine but then want to perform further 'filtering' of that same sub-set; expecting the results to be nearly instantaneous. For the  filtering selection I want to use an EXECUTE.

The general steps in the Databasic process would be:

EXECUTE.STRING = 'SELECT SOMEFILE WITH SOMECRITERIA = "ABC" '            ;* This will be my filter

SELECT COMMONLISTVAR TO SOMELIST                                                                ;* This I expect to create an active list

EXECUTE EXECUTE.STRING CAPTURING DUMMY RETURNING RETVAR           ;* This I expect to filter my active list returning a shorter active list

But it doesn't. It appears the EXECUTE does not 'inherit' the active-list. I guess that's reasonable as an EXECUTE pushes a level and spawns a new workspace; but my question then is:

How do I achieve what I want? The only thing I can think of [which I consider clumsy] is to write out the common select list into POINTER-FILE [thus create a 'gettable' get-list]; and stacking two commands in the EXECUTE to firstly perform a get-list and then the filtering execute thus returning an active list to Databasic.

Anyone got any better ideas?

[I hope I'm explaining myself]

[Bob Dubery]

On Sunday, 10 December 2017 11:29:28 UTC+2, David Knight wrote:

But it doesn't. It appears the EXECUTE does not 'inherit' the active-list. I guess that's reasonable as an EXECUTE pushes a level and spawns a new workspace; but my question then is:

How do I achieve what I want? The only thing I can think of [which I consider clumsy] is to write out the common select list into POINTER-FILE [thus create a 'gettable' get-list]; and stacking two commands in the EXECUTE to firstly perform a get-list and then the filtering execute thus returning an active list to Databasic.

Anyone got any better ideas?

[I hope I'm explaining myself]

I haven't worked in a Pick product for years now, and I don't think I ever worked in D3, but it must have a mechanism for executing multiple commands in a single session.

In UniVerse you'd do something like

TclCmd = ' SELECT SomeFile WITH ....'
TclCmd<2> = [your filtering commands]
Execute TclCmd.

But there must be other ways to do it. Write a PROC away and execute the PROC?

[Charlie Noah]

Does D3 not have PASSLIST and RTNLIST (or something similar)? I'm not being facetious, I just haven't worked with D3 enough to remember. I use these all the time in Jbase.

Charlie

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms

[Peter McMurray]

Instead of saving your short list in a common variable save it to the pointer-file and use GET-LIST POINTER.NAME

The SELECT TO variable is expecting something like READNEXT FROM VARIABLE

EXECUTE.STRING = 'SELECT SOMEFILE WITH SOMECRITERIA = "ABC" '

DATA SAVE-LIST SHORT.LIST

EXECUTE EXECUTE.STRING

later new program or subroutine

EXECUTE "GET-LIST SHORT.LIST" ;* as often as you like

[Wols Lists]

On 10/12/17 09:29, David Knight wrote:
> This one's a puzzler.
>
> In my app I have a COMMON array variable which contains a list of item
> keys built via another process. [This was done for performance issues to
> save the system always having to select from an entire large file.]
> Let's call it a sub-set.
>
> I now want to utilise that sub-set within a Databasic routine but then
> want to perform further 'filtering' of that same sub-set; expecting the
> results to be nearly instantaneous. For the filtering selection I want
> to use an EXECUTE.

Does D3 have a PERFORM command?

The behaviour you want is what INFORMATION did. UV has "EXECUTE" and
"PERFORM" to provide Pick and PI style behaviour. IFF D3 has a PERFORM
command, this should do exactly what you want (I've never used D3, I
wouldn't know ...)

Cheers,
Wol

[David Knight]

Great to hear from everyone... Thank you! Some missed the point a little: in order to retain performance I wanted to have a 'list' kept in COMMON for that users; but who could then 'filter' down that list for display purposes. This required the passing of an active list to an EXECUTE. I did not want to re-execute the first selection each time as it takes too long on the large datafile in question.

But some answers I think have found the key: I was reading the manual concerning the SELECT statement; not the EXECUTE statement. Duh! Yes, it appears there is a Clause which can be attached to an EXECUTE statement to pass it a list. Presumably this is the "me to" part of d3 behaving like PI/UV etc that you guys are more familiar with. First blush reading of the manual suggests this will do what I need; but I'll test & let you know.

Thank you for all your prompt feedback, it gave me the 'nudge' I needed to look in the right place.

[David Knight]

Ok, that worked! Impressive performance now - instantaneous! From the manual here is the extended optional d3 syntax:

execute tcl.exp {[stacking|,//in.<] exp} {[passlist|,//select.<] list.var} {[rtnlist|,//select.>] list.var } {[returning|setting] var} {[capturing|,,//out.>] var}

I shan't paste the expansive entry on the options but refer those interested to the d3 help; but suffice to say it appears to do everything those here are saying are features of other mvdbms'

[Peter McMurray]

I am intrigued as to why you feel that a COMMON array is the answer when the pointer-file is designed to hold lists until you remove them and is common.

As the documentation states  "The stacking clause allows the user to stack data for the TCL statement. This clause is identical to using a data statement before the execute."

This was discussed with several options in the recent topic re PICK Power and SQL

I used a simple example of doing it. I often select a large file; in my case over half a million records, for just the current months data or any data not yet invoiced and use the resulting saved list for feeding other selections. Of course one can use keys specific to the current user and simply execute delete-list when finished with the intermediate short list.

 data "save-list currCards"

 execute 'sselect sscards with status "c"'

 execute 'get-list currCards'

 data "save-list cardAdds1"

 execute 'sselect sscards by 11 11'

 execute 'get-list cardAdds1 (u' 

[David Knight]

Hi Peter,

Good question! The answer lies in other application-functionality design and toolset issues. The application is a pure web-based, and thus 'state-less'; application built using designbais. One of the harder [for me] obstacles in building applications without using powerful toolsets is to handle issues of state. Designbais takes care of all that for me, so having a specific user's primary selection held in COMMON which designbais maintains the state for, meaning each user gets the correct data; makes the 'flow' of the application easier to follow. Secondly, in terms of the application itself, I can turn on/off areas of forms, buttons etc for functionality purposes based upon the existence of a list or not.

I'm not saying my solution is the best; but I think it is the best for me and the other areas of the application beyond the scope of this thread.

Cheers and thank you for your input.

PS: And this is a minor point: the slightly Reverse-Polish-Notation form of the command snippet you offered is difficult for me to read and follow. I get it, but it took 2-3 reads; rather than being a more 'obvious' solution. Now, I realise that shows my non-programmer roots which University-educated professionals may frown upon; but it is a valid point for me!

[Scott Ballinger]

Hi David,

As you have figured out, a SELECT list inside of BASIC is different from a SELECT list at TCL: the BASIC SELECT is a variable in BASIC memory and cannot be acted upon by subsequent TCL SELECTs. The mechanism to save a TCL SELECT list is SAVE-LIST; you can also save a BASIC SELECT list by writing it to the pointer-file because it is just an AM delimited list; but note that each READNEXT "uses up" the list by removing the first element.

...this works:

mylist= ""
for n = 1 to 1000

 mylist<-1> = n

next n

open "pointer-file" to pf else stop 201,"pointer-file"

write mylist on pf,"newlist"

execute "get-list newlist"

[404] 1000 items selected out of 1 items.

...and this will work

for n = 1 to 1000

 mylist<-1> = n

next n

select mylist to newlist

open "pointer-file" to pf else stop 201,"pointer-file"

write newlist on pf,"newlist"

execute "get-list newlist"

[404] 1000 items selected out of 1 items.

...but this will only save ids 101 - 1000 in the pointer-file 'mylist' because of the READNEXT

mylist = ""

for n = 1 to 1000

 mylist<-1> = n

next n

select mylist to newlist

c = 0

loop

  readnext id from newlist else exit

  c +=1

  if c ge 100 then exit

repeat

open "pointer-file" to pf else stop 201,"pointer-file"

write newlist on pf,"newlist"

execute "get-list newlist"

[404] 900 items selected out of 1 items.

BTW, the trick of turning an AM delimited list into a SELECT list and employing READNEXT to get the next list element is is a very fast way to process a large list inside of BASIC.

...cust.list is a AM delimited list of 10,000 (or more) customer ids:

...slow way: gets slower as N increases

max = dcount(cust.list,@am)

for n = 1 to max

  cust.id = cust.list<n>

  read cust.rec from cust.file,cust.id then....

next n

...fast way: last id is just as fast as 1st id

select cust.list to active.list

loop

  readnext cust.id from active.list else exit

  read cust.rec from cust.file, cust.id then...

repeat

What if your list is VM delimited instead of AM? Easy, CONVERT is super-fast (like instantaneous even on a 1M+ element array):

convert @vm to @am in cust.list

/Scott Ballinger

Pareto Corporation

Edmonds WA USA

206 713 6006

[Peter McMurray]

Hi David

Thanks for the explanation. I queried COMMON because so many people do not use it in a logical manner. We have always had a defined COMMON since the very first program we wrote in 1977 however many seem to treat it like a sloppy tray resulting in mismatches everywhere. DesignBais obviously have it right. 

As you have discovered PASSLIST can be very useful. The introduction of EXECUTE was in my opinion the most useful command update to Pick ever. We had some horrendous PROCS to get around the lack. Proc Commands such as %%1 could have one scratching one's head but power plus.

Reverse Polish! My favourite methodology :-) My first hand sized calculator was a Hewlett Packard 25 and I use RPN in RealCalc on my phone now. The senior actuary at Colonial Mutual was extremely envious as I got mine before him in 1975 when we were still using hurdy gurdies and the like. I never got into the mood of Algebraic correlatives and still stick to the F correlative.