Alpha Video Training
Results 1 to 6 of 6

Thread: Multi-table query -> answer table?

  1. #1
    Member
    Real Name
    Daniel Hofer
    Join Date
    Mar 2006
    Location
    Austin, Texas
    Posts
    9

    Default Multi-table query -> answer table?

    I'm porting a Paradox 7 application to A5V7 and have many paradox queries for which the code looks like the following:

    Query
    ANSWER: :PRIV:mastI.DB

    CLIENTS.DB | Client Id | Company |
    | _join1 | Check |

    CONTACTS.DB | Contact Id | Client Id |
    | _join2 | _join1 |

    ORDERS.DB | Order No | Contact Id |
    | _join4 | _join2 |

    INVOICE.DB | Invoice No | Order No | Invoice Date |
    | _join5! | _join4 | >= 1/1/2005, <= 12/31/2005 |

    INVOICE.DB | Invoice Grand Total |
    | calc sum as Invoiced |

    PAYMENTS.DB | Invoice No | Amount |
    | _join5 | calc sum as Paid |

    EndQuery

    The 5 tables, each in cascading 1 to many relationship, are joined and a few fields or calculated fields are output to a temporary answer table. The _join5! is the equivalent of a A5V7 join, the rest are intersections. The fields with the word check under them or an expression are output.

    I can not see how to use more than 2 tables in A5V7 with the intersection genie. Is there any other way to do the above?

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    I think you need to create a set and link the tables on the appropriate fields. Once you have the set you can query it for specific information.

  3. #3
    Member
    Real Name
    Daniel Hofer
    Join Date
    Mar 2006
    Location
    Austin, Texas
    Posts
    9

    Default Limitations on sets in intersections.

    I had tried 2 sets, client->contact->orders with links 1 to many, and orders->contact->client with links 1 to 1.

    In both cases one can only select fields to output from the first table [top level] in the set when defining the intersection. I need fields or calculated fields from any table in the set not just the master table and the transaction table.

    Another problem is that if you try to link from any field in the set other than the top table fields offered in the drop down box [by using the expression builder to get at the fields from the other tables in the set] you get an error:

    variable intersect.m_key expression-field-not recognized.

    I guess my problem is not a master table - transaction table intersection problem, it's an SQL problem. Is there another approach or tool in A5V7? I haven't received my XBasic 2006 book yet.

  4. #4
    VAR
    Real Name
    Cheryl Lemire
    Join Date
    Jul 2003
    Location
    Pembroke Pines, FL
    Posts
    2,914

    Default

    Hi Daniel,

    I can not see how to use more than 2 tables in A5V7 with the intersection genie.
    You can't. From the help files:
    An Intersect operation takes the records that two tables have in common and uses them to create a new table.
    Lets forget your original cascading relation for a minute. What is the final result you are trying to accomplish? Are you looking to produce a specific report? What details, summary, etc do you need for your report.

    I do agree that what you will need is a specific way to create a set in alpha to accomplish your end result, what I am not clear on, is what is the end result that you are trying to accomplish?

    Good luck
    Cheryl

  5. #5
    Member
    Real Name
    Daniel Hofer
    Join Date
    Mar 2006
    Location
    Austin, Texas
    Posts
    9

    Default Desired result

    I'm producing a temp table, in this case for a report. The report has fields: company, total of invoices, total of payments on those invoices for an invoice date range of invoice dates >= 1/1/2005, <= 12/31/2005. In other other words, which clients are in arrears for last year?

    This is a typical [much simplied in this test] business intellegence question I get from Management. Often I have to break a management question down into subset questions to get the answer, thus the need to get a temp table which may be fed into another query, etc. My most complicated problem I have to solve involves a combination of 26 cascading and parallel multi-table queries involving original and temp tables. That particular problem has to be solved using sets. Is there something in A5V7 with SQL abilities, or is SQL expected in the next client server release?

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    With the properly constructed set I don't believe you need an intersection, join, or other. You can use a copy operation to place the desired fields to a new/temporary table filtering in the process.

Similar Threads

  1. Parent table based on query of child table.
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-25-2005, 04:14 PM
  2. Multi-state button to filter a table
    By Martin in forum Alpha Five Version 4
    Replies: 7
    Last Post: 03-12-2003, 09:05 AM
  3. Using Query-by-Table
    By Kendra Wilson in forum Alpha Five Version 4
    Replies: 4
    Last Post: 09-25-2001, 10:23 AM
  4. Query by Table
    By Arthur Casse in forum Alpha Five Version 4
    Replies: 13
    Last Post: 09-23-2000, 07:43 PM
  5. Scrolling Screen query table and update table
    By mpierce in forum Alpha Five Version 4
    Replies: 2
    Last Post: 09-21-2000, 09:38 AM

Bookmarks

Posting Permissions

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