D3 and mvBase

Expand all | Collapse all

pass keys to execute AQL

Jump to Best Answer
  • 1.  pass keys to execute AQL

    Posted 17 days ago
    Morning ALL,
    I am lacking a bit or brain power and cannot remember how to accomplish this.   In my basic program I have a list of keys that have been placed in a variable.  I now want to run a LIST FILENAME using a EXECUTE to generate and capture a simple report.   How do I make the keys in the variable the active list?

    I hope that makes since.  
    Thanks

    ------------------------------
    Craig Curtis
    System Programmer
    Sevier Valley School District
    Richfield UT United States
    ------------------------------


  • 2.  RE: pass keys to execute AQL

    Posted 17 days ago

    CLEARSELECT
    EXECUTE 'SELECT CONTROLE.LOG IF TIPO.DATA EQ "DATA"'
    10
    READNEXT CHAVE.DATA ELSE GO 5
    ....
    GO 10

    or
    IDS.LIST = "A]V]R]T
    FILENAME = "FILEONE"
    ATR.LIST = "A1]A3]A5   ;* OR CAN BE THE DICT NAME/IF ATR.LIST EQ "" THEN WILL LIST THE DEFAULT DICTS FROM THE FILE
    cmd = 'LIST ':FILENAME:' ':SWAP(IDS.LIST,@VM," ") 
    IF ATR.LIST NE "" THEN
    CMD := CMD:' ':SWAP(ATR.LIST,@VM," ") 
    END
    EXECUTE CMD CAPTURING MENSAGEN RETURNING ERRO
    NUM.RESULTS = DCOUNT(MENSAGEN,@AM)
    FOR LL = 1 TO NUM.RESULTS
    CRT MENSAGEM<LL>
    NEXT LL



    ------------------------------
    Alberto Leal
    System Analist
    Millano Distribuidora de Auto Pecas Ltda
    Varzea Grande MT Brazil
    ------------------------------



  • 3.  RE: pass keys to execute AQL
    Best Answer

    ROCKETEER
    Posted 17 days ago
    Something like this? ( from the D3 Reference Manual > BASIC statements > select

    The array variable, string, is treated as an active list by assigning it to the list variable, list.

    string = '1001':char(254):'1002':char(254):'1003'
    select string to list
    eol=0
    loop
       readnext ID from list else exit
       print ID:" exists"
    repeat


    ------------------------------
    Brian S. Cram
    Principal Technical Support Engineer
    Rocket Software
    ------------------------------



  • 4.  RE: pass keys to execute AQL

    Posted 17 days ago
    Thanks for the answers!!!!  Always like options.

    Brian,  I read write over that in the manual this morning before the post.  

    Thanks again everyone!

    ------------------------------
    Craig Curtis
    System Programmer
    Sevier Valley School District
    Richfield UT United States
    ------------------------------



  • 5.  RE: pass keys to execute AQL

    PARTNER
    Posted 16 days ago
    Craig,

    Here is another option for you. :)
    Write your attribute delimited list to POINTER-FILE as e.g. MYLIST. Execute GET-LIST MYLIST and you have active select list for the LIST FILENAME command.

    Regards,

    Chris

    ------------------------------
    Chris Wolcz
    Senior Software Developer
    Execontrol Global Solutions
    Clifton Park NY United States
    ------------------------------



  • 6.  RE: pass keys to execute AQL

    Posted 15 days ago
    +1 for Craig's suggestion. I do this mainly when creating lists using multiple SELECTs, SAVE-LIST for each list, then GET-LIST list1 list2 etc  (U to get a list of unique keys, i.e. no duplicates.

    ------------------------------
    Walter Kiess
    Manager IT
    Police Superannuation Board
    Adelaide SA Australia
    ------------------------------



  • 7.  RE: pass keys to execute AQL

    Posted 14 days ago
    Thanks again everyone! So nice to hear different approaches.

    I don’t like the save-list option since with the web application the list is always changing as per the user and information it is gathering for that call.

    I have implemented just adding the list after switching the FM to a space. Just hope I don’t run into any length of AQL to execute.

    Craig




  • 8.  RE: pass keys to execute AQL

    Posted 16 days ago
    Hi Craig,

    I do this sort of thing a lot. Depending on what delimiter you have used to store the list of IDs in the variable you may have to change them all to a space first, e.g. LIST.VAR = SWAP(LIST.VAR, "delimiter", " "). Once you have a space delimited list of IDs you can use an EXECUTE statement such as this:
    EXECUTE "LIST FILENAME ": LIST.VAR provided you have default reporting fields set up. You can, of course, use SORT instead of LIST and add as many fields (DICT items) as you need.

    HTH

    ------------------------------
    Walter Kiess
    Manager IT
    Police Superannuation Board
    Adelaide SA Australia
    ------------------------------



  • 9.  RE: pass keys to execute AQL

    ROCKETEER
    Posted 14 days ago
    Actually, the SELECT STRING TO LIST method is ( I believe ) more efficient as it creates the list without actually doing a SELECT.  EXECUTE SELECT with a space-delimited set of IDs actually performs an AQL SELECT to find those items and populate a list.

    ------------------------------
    Brian S. Cram
    Principal Technical Support Engineer
    Rocket Software
    ------------------------------



  • 10.  RE: pass keys to execute AQL

    Posted 14 days ago
    Hi Brian. I couldn’t get it to work. Not sure why. That really was what I wanted but it didn’t seem to make the readnext key work. ?

    Craig

    > On Sep 10, 2021, at 10:09 AM, Brian Cram via Rocket Forum <mail@forum.rocketsoftware.com> wrote:
    >
    > 
    > Be sure to join the forums you're interested in to be notified of new content. Click the join button from either the forum listing page or the home page of any given sub-forum.
    >
    > D3 and mvBase
    > Post New Message Online
    > Re: pass keys to execute AQL
    > Reply to Group Online Reply to Group
    >
    > Sep 10, 2021 12:07 PM
    > Brian Cram
    > Actually, the SELECT STRING TO LIST method is ( I believe ) more efficient as it creates the list without actually doing a SELECT. EXECUTE SELECT with a space-delimited set of IDs actually performs an AQL SELECT to find those items and populate a list.
    >
    > ------------------------------
    > Brian S. Cram
    > Principal Technical Support Engineer
    > Rocket Software
    > ------------------------------
    > Reply to Group Online View Thread Recommend Forward Flag as Inappropriate Post New Message Online
    > -------------------------------------------
    > Original Message:
    > Sent: 09-08-2021 19:04
    > From: Walter Kiess
    > Subject: pass keys to execute AQL
    >
    > Hi Craig,
    >
    > I do this sort of thing a lot. Depending on what delimiter you have used to store the list of IDs in the variable you may have to change them all to a space first, e.g. LIST.VAR = SWAP(LIST.VAR, "delimiter", " "). Once you have a space delimited list of IDs you can use an EXECUTE statement such as this:
    > EXECUTE "LIST FILENAME ": LIST.VAR provided you have default reporting fields set up. You can, of course, use SORT instead of LIST and add as many fields (DICT items) as you need.
    >
    > HTH
    >
    > ------------------------------
    > Walter Kiess
    > Manager IT
    > Police Superannuation Board
    > Adelaide SA Australia
    >
    >
    >
    >
    > You are receiving this message because you followed the 'pass keys to execute AQL' message thread. To unsubscribe from this message thread, go to Unsubscribe.
    > Update your email preferences to choose the types of email you receive
    >
    > Unsubscribe from all participation emails
    >
    >




  • 11.  RE: pass keys to execute AQL

    Posted 14 days ago

    THIS IS A FUNCTION DEMO 

    1 OPEN "SGVAR036" TO SGVAR036
    2 EXECUTE 'SELECT SGVAR036 IF A0 EQ "E]"'
    3 SELECT SGVAR036 TO LIST
    4 LOOP
    5 READNEXT CHAVE36 FROM LIST ELSE EXIT
    6 PRINT CHAVE36
    7REPEAT
    8 STOP


    line 1 open the file
    line 2 execute the select with aql
    line 3 use the basic select to get the result from line 2 and set to a variable in this case list but can be anithing
    line 4 starts the loop
    line 5 use the readnext to get the next item id from the list you just create and when get to the end it exits the loop
    line 6 print the item id
    line 7 repeat the loop
    line 8 stops the program



    ------------------------------
    Alberto Leal
    System Analist
    Millano Distribuidora de Auto Pecas Ltda
    Varzea Grande MT Brazil
    ------------------------------



  • 12.  RE: pass keys to execute AQL

    ROCKETEER
    Posted 14 days ago
    Craig:

    No idea why it's not working for you, unless you're using SYSTEM(11) to detect an active list. Here's a test program I just tried using the CUSTOMERS file in the SQLDEMO account ( yeah, I changed the name of customer "1" ). Works both flash and non-flash. If you continue to have trouble with this, let's open a case and get on your system together.

    craig
    001 open 'customers' to file else stop 201,'customers'
    002 str = "1"
    003 str<-1> = "2"
    004 str<-1> = "3"
    005 str<-1> = "4"
    006 str<-1> = "5"
    007 crt "str: ":str
    008 select str to lst
    009 crt "sys(11): ":system(11):", and why is it zero???"
    010 loop
    011 readnext id from lst else stop
    012 read i from file,id else i = "not read"
    013 crt id'l## ':i<1>
    014 repeat

    :run bp craig
    str: 1^2^3^4^5
    sys(11): 0, and why is it zero???
    1 The Most Excellent Craig
    2 Cyber Products
    3 Software Inc
    4 Computer Warehouse
    5 Bits and Bytes Inc
    :compile bp craig (o
    craig
    ..

    [820] Creating FlashBASIC Object ( Level 0 ) ...
    [241] Successful compile! 2 frame(s) used.
    :run bp craig
    str: 1^2^3^4^5
    sys(11): 0, and why is it zero???
    1 The Most Excellent Craig
    2 Cyber Products
    3 Software Inc
    4 Computer Warehouse
    5 Bits and Bytes Inc
    :


    ------------------------------
    Brian S. Cram
    Principal Technical Support Engineer
    Rocket Software
    ------------------------------



  • 13.  RE: pass keys to execute AQL

    Posted 9 days ago
    Using the select to list method, how would you go about sorting by another value?  Would the most performant way still just be executing sselect / list?

    open 'mvdemo,customers,' to f.cust else stop 201,'customers'
    *
    slist=1:@am:2:@am:3:@am:4:@am:5
    *
    dlist=""
    select slist to clist
    loop
       readnext id from clist else exit
       read cust from f.cust,id
       dlist<-1>=id:@vm:cust<1>
    repeat
    *
    * now can we sort dlist by customer name?
    print dlist
    *
    nlist = \"\: swap(slist, @am, \" "\) :\"\
    execute \sselect mvdemo,customers, \: nlist :\ by organizationname\
    execute \list mvdemo,customers, customerid organizationname id-supp\​


    ------------------------------
    Jeremy Lockwood
    Awesome
    ASE Supply Inc
    Portland OR United States
    ------------------------------



  • 14.  RE: pass keys to execute AQL

    Posted 9 days ago
    Hi Jeremy,

    I always use D3's database handling commands as they are the most efficient at what they do. Thus I create a command using BASIC, then execute it. Try this:

    file = 'mvdemo,customers,'
    open file to f.cust else stop 201, file
    *
    slist="1 2 3 4 5"
    *
    verb = 'select ': file: ' by a1 ': slist ;* Assuming a1 is the customer name
    execute verb capturing text
    if system(11) = 0 then crt 'No items on file'; stop
    *
    loop
       readnext id else exit
       read cust from f.cust,id else cust = ''
       crt cust<1>
    repeat
    *
    ​

    HTH



    ------------------------------
    Walter Kiess
    Manager IT
    Police Superannuation Board
    Adelaide SA Australia
    ------------------------------



  • 15.  RE: pass keys to execute AQL

    PARTNER
    Posted 9 days ago
    hi, craig,
    I suggest to use the second version of the execute statement where you can use 'passlist list'.
    In this sample, I know some MD keys and I'll  the ones which are keyword (<1>[1,1] ='c'  the sort the new list by the key.

    values = 'md,with,by,bp'
    values = convert(values,',',@am)
    select values to mylist 
    execute 'select md with a1 = "c]" by a0' passlist mylist rtnlist newlist 
    loop 
       readnext k from newlist else exit 
       crt k 
    while 1 do repeat 
    ​
    I hope this help

    ------------------------------
    Manu Fernandes
    ------------------------------