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
Devcon 2020 Goes Virtual! LEARN MORE >

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

Compare Two Database Fields To Get Percentage

Collapse
X
Collapse
 
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
    #16
    Re: Compare Two Database Fields To Get Percentage

    In the list control would you just put the sql view statement in the SQL statement?

    Comment

      #17
      Re: Compare Two Database Fields To Get Percentage

      you can select the view - technically its considered a table "unto it's own" in a sense, if I am not mistaken. The view is created in the database itself. What your suggesting would be more of a join statement.
      NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

      Comment

        #18
        Re: Compare Two Database Fields To Get Percentage

        Steven,

        Yes, you create the view in the database, and then just select the view as the datasource for the view.
        Don't just paste in the sql from the view statement, then you loose a lot of the benefits of a view.
        Using the view as a table means is possible because Alpha is clever enough to let you specify the primary key to use for updates, as long as your view has 1 table with sub selects it will be just as fast as using a regular table

        I know you can build complex queries directly in Alpha, and that's great if you don't have access to the database, but also something that your DBA will hate :-)

        So if it all possible create the views directly in the database

        Comment

          #19
          Re: Compare Two Database Fields To Get Percentage

          So in the view, I will need to include all fields that I want to do something with such as show in the List Control Detail View and not just the ones that are doing the comparison?

          Comment

            #20
            Re: Compare Two Database Fields To Get Percentage

            Yes, exactly, it will make very little difference performance wise, and you should also calculate the percentage of matching columns in the view

            Comment

              #21
              Re: Compare Two Database Fields To Get Percentage

              When I am trying to do the view by implementing the below code, it is giving me the total count of all matches of the 1 column in Table A to Many columns in Table B. So if in Table A the column has the number 1 and in Table B 4 columns have the number 1 then the count is 4. I need it to compare Table A column to Table B column one row and it it matches count it one time. I am not sure if this makes sense to you. It almost seems like I need to do an While loop that will look at each row in Table B one at a time and count it one time if it matches and track the matching row in Table B. Seems with a view you cant set up variables though so not sure how to do it in a view.

              SELECT Id,
              Col_A, ( select COUNT(*) from table_B B where B.Col_A = A.Col_A) AS Col_A_COUNT,
              Col_B, ( select COUNT(*) from table_B B where B.Col_B = A.Col_B) AS Col_B_COUNT,
              FROM Table_A A

              Comment

                #22
                Re: Compare Two Database Fields To Get Percentage

                You just additional subqueries for the percentage calculations in the view, that is still the most simply way to do it I think.

                But what you now describe is not the original problem, now it's comparing the value in 1 column to all columns in the target table it seems. you might consider to rethink you database design

                A hint is that you can make views that are based on another view, this normally helps when making complex queries like this

                Comment

                  #23
                  Re: Compare Two Database Fields To Get Percentage

                  A lot of this depends on what you wish to do.

                  BUT, If you already have two tables in lists (List 1 and List 2), then IT COULD be all done client side. You can create an array of the columns and data you are looking for from List 1, and then compare the array from List 2. There are a lot of javascript examples on the web that you can use and adapt. The resulting array after the comparison can then be input into List 3 (you can programmatically create a list on the fly by inserting an array into it).

                  Having said that, the BEST solution I can think of is to create a "procedure" on the database server which accepts variables. The variables would be table1 and fields, and table 2. Then your procedure loops through things to compare and programmatically generates a query and returns results. Then, your ux gets the results as an array and input into a dynamically generated list. Then, you can do whatever you wish with that list in your ux.

                  It all depends on simplicity and/or performance, and what your programming capabilities are.

                  Comment

                    #24
                    Re: Compare Two Database Fields To Get Percentage

                    When you say "procedure" do you mean stored procedure? When you are talking about variables, I am thinking that is about the only way to do it and as you say have a loop that goes through each row at a time and does the comparison. I was thinking that the stored procedure would write the calculated percentages to a database table that I could use in a list control to some the percentages. Wanted to see what you think WebDesignShop and if what you meant by "procedure" was a stored procedure? The only thing I am concerned about is the sql execution time and the amount of times it would have to run with a stored procedure because it would have to run with each addition, or modification to any row with either table.

                    Comment

                    Previous 1 2 template Next
                    Working...
                    X