Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Fun with queries

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Fun with queries

    Is this inefficient? I am specifically wondering about repeatedly creating and dropping a query in a while loop.

    The idea here is that mytable provides family IDs. myRelatedTable is searched for a combination of a family ID (dynamic) and an event id (static). If the ID combination is found in the related table, some action takes place, such as copying data from mytable to myrelatedtable.

    I was wondering if there is an optimized way of doing this, such as creating a query that holds all familyID's that belong to one event, and then using that one query to look up family IDs. I don't know how much overhead creating and dropping queries like this cause, and being somewhat new to the A5 way of doing things, I may be writing pretty inefficient code.

    Code:
    veventid = 1
    mytable.fetch_first()
    while .not. mytable.fetch_eof()
       vFamilyID = mytable.familyid
       myquery=myRelatedTable.query_create("","EventID = "+vEventID .AND. "FamilyID = "+vFamilyID)
       vFound = (myquery.records_get()>0)
       myquery.drop()
       IF vFound
          'do something
       END IF
       mytable.fetch_next()
    end while

    #2
    Re: Fun with queries

    Code:
    veventid = 1
    mytable.fetch_first()
    while .not. mytable.fetch_eof()
       vlink = mytable.familyid+mytable.familyid
       IF mytable.familyid = veventid
          'do something with the link you have captured above on that record
       END IF
       mytable.fetch_next()
    end while
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

    Comment


      #3
      Re: Fun with queries

      Thank you for the suggestion. My explanation may have been a bit obscure -- what I need to do is this:

      Scan through mytable
      for each mytable row, find a row in RelatedTable that has a foreign key match to mytable primary key as well as another column match to a variable (this is the relatedtable query part)
      if a match is found in RelatedTable
      copy data from mytable to RelatedTable
      end if
      end for
      end scan

      It's the query part that I'm not sure I'm doing the most optimized way. I'm used to using self optimizing SQL queries for this kind of stuff, and it is hard to know how Xbasic queries are optimized, if at all.

      Luckily the amount of data I'm dealing with is not huge, so even unoptimized queries work fast enough. This is more of an academic interest at this point, which may well have practical implications in the future if I need to deal with hundreds of thousands of records in this manner. Shudder the thought...

      Comment


        #4
        Re: Fun with queries

        I still don't think you need a query. You can fetch thru both tables: begin with the primary and then fetch the related table until a match if any is foundl; then fetch the next primary table and repeat the search in the related table. When (if) a match is found then run the copy you want while both tables are on the related matching records. I will attempt an example...

        Edit: I thought I had an example script but it was for entering records not changing matching records. Have you tried a Post operation for this task?
        Last edited by MoGrace; 03-09-2012, 11:39 PM.
        Robin

        Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

        Comment


          #5
          Re: Fun with queries

          Hmmm... Interesting idea. I wonder if scanning through the related table for each record in the primary table is faster than building a query each time. It seems a bit brute force to me, but then again I don't know how A5 is optimized internally, and what are the efficiencies/inefficiencies of table query. Maybe there is a fair amount of overhead in building and dropping the query. Would be nice to know a bit more about the internals. I was excited about the debugger profiler feature which would tell me a lot about the execution speeds of different statements, until I realized that the profiler only works for desktop applications. I suppose I could build a dummy desktop app just to attach the profiler to these types of operations, assuming that the internal engine is the same for desktop and web server operations. Seems like a lot of extra work, though...

          Comment


            #6
            Re: Fun with queries

            Did you look into the idea of using a post operation to achieve your goal?
            Robin

            Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

            Comment


              #7
              Re: Fun with queries

              Post operation?

              Comment


                #8
                Re: Fun with queries

                Just wondering.... Is this a MySQL database?

                If you are updating the Myrelatedtable with info from mytable, couldn't you do it with an update statement?
                Earl Allin
                REAInc.net

                We can help you with mentoring and custom programming services in Alpha, .NET, React, PHP, ColdFusion, and more..

                Comment


                  #9
                  Re: Fun with queries

                  Originally posted by nlights View Post
                  Post operation?
                  Yes, if you are looking to match a family id with an event id then that is the filter you need to define in the post definition to find the records you want to update.
                  Robin

                  Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                  Comment

                  Working...
                  X