Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: How to combine queries in macro-like fashion

    I am in need to find a way to combine the 4 queries as below and run them as a sort of macro without having to start each of them individually and subsequently having to close the resulting 'changed' windows.
    Also, the running of these 4 queries has to be repeated up to 40 times to complete the task.
    Thanks up front for any useful suggestion.

    ==========
    Query
    ANSWER: :PRIV:ANTWRD.DB

    D:\Zscan\VALUE\MASTER.DB | NAME | ImpactValue |
    | _a | _b, not blank |

    D:\Zscan\VALUE\SUBFILE.DB | SIRE | SireValue |
    | _a | blank, changeto _b |

    EndQuery
    ==========
    Query
    ANSWER: :PRIV:ANTWRD.DB

    D:\Zscan\VALUE\MASTER.DB | NAME | ImpactValue |
    | _a | _b, not blank |

    D:\Zscan\VALUE\SUBFILE.DB | DAM | DamValue |
    | _a | blank, changeto _b |

    EndQuery
    ==========
    Query
    ANSWER: :PRIV:ANTWRD.DB

    D:\Zscan\VALUE\SUBFILE.DB | NameValue | SireValue |
    | blank, changeto (_x + _y) /2 | _x, not blank |

    D:\Zscan\VALUE\SUBFILE.DB | DamValue |
    | _y, not blank |

    EndQuery
    ==========
    Query
    ANSWER: :PRIV:ANTWRD.DB

    D:\Zscan\VALUE\MASTER.DB | NAME | ImpactValue |
    | _a | blank, changeto _z |

    D:\Zscan\VALUE\SUBFILE.DB | NAME | NameValue |
    | _a | _z, not blank |

    EndQuery
    ==========


    Kind regards,
    Peter van Arkel

  2. #2
    Join Date
    Nov 2010
    Posts
    5
    I hope this helps

    I have attached a Paradox script that will run the 4 queries without the changed table appearing. You can repeat the script as many times as needed.
    You will have to replace the .txt extension with .ssl so paradox to run the file as a script.

    Remember to backup you data before running this script in Paradox. Also a loop can be written in the script to repeat as many times as needed.

    Willobee
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2010
    Posts
    8
    Willobee wrote:
    I hope this helps
    I have attached a Paradox script that will run the 4 queries without the changed table appearing. You can repeat the script as many times as needed.
    You will have to replace the .txt extension with .ssl so paradox to run the file as a script.
    Remember to backup you data before running this script in Paradox.
    >>
    >>
    I took the necessary steps....but, after opening/running the [ssl] I get an error window that displays the text:

    [Disk error occured while reading file.]

    The path is exactly as in the query text that I posted.
    (the software installed is Paradox release 9.00.368 in combination with Windows XP professional service pack 2.
    **

    Willobee wrote:
    Also a loop can be written in the script to repeat as many times as needed.
    >>
    >>
    Sounds great, that would be the icing on the cake.

    Thank you for your help so far, I'm sure there is a solution for the current hick-up.

    Kind regards,
    Peter

  4. #4
    Join Date
    Nov 2010
    Posts
    5
    Which file qives the error the script or one of the tables used in the queries? Can you right click on the .ssl and select design to edit it? if you can then it is not the script and we can move on to the queries.

    Maybe something is wrong in the query text embedded in the script. They have been copied and pasted a bit.

    Open the script I sent to you in Paradox by right clicking on it and selecting Design. You can paste the query text into the script for the 4 queries. Locate the Q=Query statement at the beginning of a query. Highlight everything from just past the = sign to the if statement just after endQuery (do not select the line the if statement is on) and delete the text that makes up the query. Then put the cursor right after the = sign and use the menu command Edit>paste From... and select your query. This will paste the query into the script. Do this for all 4 queries. This should remove any errors in the queries contained in the script. Run it and see if it works then.

    Willobee

  5. #5
    Join Date
    Nov 2010
    Posts
    8
    Willobee wrote:
    Which file qives the error the script or one of the tables used in the queries? Can you right click on the .ssl and select design to edit it? if you can then it is not the script and we can move on to the queries.
    >>
    >>
    Nope. I tried all the possible options, and it will open only as text in 'wordpad'. All the other options produce the same error message window.

    Kind regards,
    Peter

  6. #6
    Join Date
    Nov 2010
    Posts
    5
    In the Paradox Project Viewer can you right click on Scripts select New from the pop up menu then copy and paste the text from the Wordpad file into the new script and save it? Do not copy the lines that say method or endMethod since Paradox will automatically add those lines when you create a new script.

    Willobee

  7. #7
    Join Date
    Dec 2007
    Posts
    366
    "disk error" means "disk error".. of course it works at the source.. but somewhere in the upload or download it got hosed.. it really doesn't matter how many different ways you ask him to open the file in paradox.. it ain't gonna open.. s*it happens..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  8. #8
    Join Date
    Nov 2010
    Posts
    8
    Willobee wrote:
    In the Paradox Project Viewer can you right click on Scripts select New from the pop up menu then copy and paste the text from the Wordpad file into the new script and save it? Do not copy the lines that say method or endMethod since Paradox will automatically add those lines when you create a new script.
    >>
    >>
    I followed your instructions.
    When I try to open the new script, I hear the harddisk work for a couple of seconds, and then nothing. No error screen either.

    Is it possible that the 'term 'value' in the directory- and field names causes problems ?

    Thank you.

    Kind regards,
    Peter

  9. #9
    Join Date
    Nov 2010
    Posts
    8

    How to combine queries in macro-like fashion

    This morning I copied the contents out of the original queries into the ssl file, and that did the trick.
    It works and does what it is supposed to do.
    Thank you, Willobee !

    Can you also help me with a method to repeat the cycle say 20 times ?

    Kind regards,
    Peter

  10. #10
    Join Date
    Nov 2010
    Posts
    5

    prounnes field

    I modified the script by adding a loop using the for and Endfor statements. The file is attached. You can edit the line for A from 1 to 20 to change the amount of times the queries run.

    You can also automate this so you can use different values by adding another varable such as B that can be assigned by adding B Smallint to the var endVar block and use the view command to enter a value into B such as B.view("Iterations"). This will open a dialog box titled "Iterations" and you can type in a number. This must be entered before the loop. Also change the line for A From 1 to 20 to for A from 1 to B.

    The attached file should run in Paradox this time. I figured out what was wrong with the original file I sent you. I made the mistake of sending you the actual notepad .TXT file rather than the .SSL file renamed with the .TXT extension. Therefore paradox did not know what to do with it. Try remaning this file with the .SSL extension and running in Paradox.
    Attached Files Attached Files

  11. #11
    Join Date
    Nov 2010
    Posts
    8
    Willobee wrote:
    I modified the script by adding a loop using the for and Endfor statements. The file is attached. You can edit the line for A from 1 to 20 to change the amount of times the queries run.
    >>
    >>
    Thank you -very- much.
    **

    Willobee wrote:
    You can also automate this so you can use different values by adding another varable such as B that can be assigned by adding B Smallint to the var endVar block and use the view command to enter a value into B such as B.view("Iterations"). This will open a dialog box titled "Iterations" and you can type in a number. This must be entered before the loop. Also change the line for A From 1 to 20 to for A from 1 to B.
    >>
    >>
    Ok. This seems a bit more complex, but I may be able to do it right.
    **

    Willobee wrote:
    The attached file should run in Paradox this time. I figured out what was wrong with the original file I sent you. I made the mistake of sending you the actual notepad .TXT file rather than the .SSL file renamed with the .TXT extension. Therefore paradox did not know what to do with it. Try remaning this file with the .SSL extension and running in Paradox.
    >>
    >>
    The reason why I 'renewed' the queries a second time was, that at some point I saw that the spaces/tabs in front of several lines had gone missing.
    After I replaced the queries with the original contents everything was ok.

    I am very grateful for what you did...makes my task way less tedious. Keep up the good work ! :-)

    Kind regards,
    Peter

  12. #12
    Join Date
    Nov 2004
    Posts
    13

    Post

    Mike Irwin put together a simple and effective script for building a query by form:
    http://www.thedbcommunity.com/dload/querybyform.exe
    I have adjusted it to screen data quickly for 200 variables (the limit of fields in a Q by F table is 200) . Note that for logical fields the negative value must be blank. The form will reset after each use.

    The Query exports all applicable records win all fields (some 300+) including .mb files to results.db. A script could easily be written to change the name of that file for each search incident.

    If you are familiar with Access and your Paradox data has no Blob files (memos, embedded documents) there is an easier way: You can open and run your PDX files with Access including querries with form based gui's. (just check "help") unfortunately my data includes documents with every record, and these cannot be opened by or exported to Access (or sql or for that matter any other program). Thanks again Corel for the *(&^y service).



    var
    stNAME String
    stExec Logical

    quQuery Query
    tvResults tableView

    endvar

    stName = fldname'value

    stExec = fldExec'Value

    if StNAME.isBlank() then
    StNAME = "Check"
    else
    StNAME = "Check " + StNAME
    endif

    if stExec.isBlank() then
    stExec = "Check"
    else
    stExec = "Check " + StNAME
    endif

    Query

    ANSWER: :WORK:Results.db

    mainrol.db | Name | Exec |

    | ~stName | ~stExec |

    endquery

    errorTrapOnWarnings(Yes)
    quQuery.writeQBE(":WorkearchQuery.QBE")
    try
    quQuery.executeQBE()
    tvResults.open(":WORK:results.DB")
    tvResults.wait()
    tvResults.close()
    onFail
    errorShow()
    msgStop("", "")
    endTry
    errorTrapOnWarnings(No)

    endMethod

  13. #13
    Join Date
    Nov 2004
    Posts
    13

    addition:

    I didn't mention that I handled the logical fields with check boxes on the form (how cool is that?? click here, there and over there and abra cadabra you've got all the matching records in a database!). In the above excerpt the two fields are both Logical. As long as the default values for the check box (radio button, etc) are set to void on the form the statement "

    if stExec.isBlank() then
    stExec = "Check"
    else
    stExec = "Check " + StNAME
    endif

    --ie, if you do not set the properties of the field on the form to void, Exec or whatever your field is will not be blank but will be "False" or "No", and the query will not work.

  14. #14
    Join Date
    Dec 2007
    Posts
    366
    If you are familiar with Access and your Paradox data has no Blob files (memos, embedded documents) there is an easier way:

    you're really hooked on Access today, aren't you?

    just an FYI.. we've been informed repeatedly that the latest two versions of Access no longer have built-in Paradox drivers.. not a show-stopper, but most users wouldn't have a clue as to how to work around that issue..


    Thanks again Corel

    two things.. first, Corel had nothing to do with the table structures.. they inherited the level 7 table structure from Borland and the BDE.. second, and way more important, do you know of *any* platform that'll import unknown-format blob data from other platforms?

    if you really really really need to export your blob info, there are simple ways to scan the paradox table and pass the blob info to a file, with a file naming convention based on the key field values.. then your other platform can pass the info to its version of a blob field by reading the resultant file the same way..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  15. #15
    Join Date
    Nov 2004
    Posts
    13
    I just got the answer. Better late - and yes, I have figured out how to use thedbcommunity newsgroups which were crashing Firefox (a sorcerer's apprentice like experience), but since it's here, and I know you ghost around here, let me ask:

    "if you really really really need to export your blob info, there are simple ways to scan the paradox table and pass the blob info to a file, with a file naming convention based on the key field values.. then your other platform can pass the info to its version of a blob field by reading the resultant file the same way.."

    Yes, I really, really need to export the blob info. Paradox is eating my blobs (the other way round from the movie..odd), and I need to rescue what I can. What would those "easy ways" be (easy being relative...) and no, I am not fixated on Access, but I have it and know how to use it. I also know how to set up a a query by form in Access, which took me months to complete in Paradox, but if I can do that in MySql or another DB without learning another language (I failled PHP, I think), then all the better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •