Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    60

    Unanswered: Query Builder Design help

    Gurus, I'm writing a simple web based query builder and I have a couple issues. I prompt for:

    * Table/Fields involved
    * The direction (ASC/DESC) and fields for ordering the results
    * Pairs of Table/Fields for join clause relationships
    * and the Attributes for the join

    Since these prompts allow the user to choose anything it would appear that errors can happen in the joins E.g. A left join B left join C, but an added C right join A would cause a problem.

    Is there an algorithm for looping thru the indicated JOINs and building a FROM clause? My idea was to hold tables in array and manipulate them so they're in the order of the join but I'm not sure that would work. Any ideas? Thanks.
    Last edited by bill_dev; 09-16-03 at 00:09.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Query Builder Design help

    From my experience, no query builder prevents users from specifying crap. As a luxery, you may consider to let the user specify "contexts", ie the order of joining, but I would focus on a proper error reporting to the user if the built query don't work.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    60

    Re: Query Builder Design help

    SQL Server seems to actually corrects the mistakes on the users behalf. If I'm in EntMgr query builder and I'm building a 3 table query which are related in a circle, I can specify join properties. If I specified something in conflict with a previous join, then Query Builder actually changes/corrects the previous join so there is no longer a conflict.

    All this happens prior to the user trying the query so I was just wondering how they do that. How do you programatically determine if a join is in conflict. But I'm probably asking too difficult of a question...Thx

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Query Builder Design help

    This reminds me about a distributed query processor I wrote about ten years ago for the IBM Global network. I had to parse a query string, and to find "Join Clusters", which could be performed on a physical database.

    The idea was to consider the FROM clause as a network, where the nodes are tables, and the lines are joins. So, you will have to specify your problem on such a network model. However, it's hard to do, so I'd put the responsibility at the user and make sure that I make a clear error report for the user.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Sep 2003
    Posts
    60

    Re: Query Builder Design help

    Yeah, I found some network model white papers around but they were all over my head. You mentioned just do a good job of capturing and reporting the error (which is where I'm heading) - If I just attempted to execute parts of their query with a WHERE clause like: WHERE 1=2 would that be a performance problem or is that workable?

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Query Builder Design help

    I prefer

    WHERE 1<>1

    :-), but why would you do that? If your query is fine, just execute it and return the result to the users, and if not, your query will not be executed at all. Of course, you can consider to make a "Check Syntax" option, but I'd not do that.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Sep 2003
    Posts
    60

    Re: Query Builder Design help

    Originally posted by DoktorBlue
    I prefer

    WHERE 1<>1

    :-), but why would you do that?
    ...so the user might know at design-time which join causes problems. The where clause would be to prevent potentially thousands of rows from coming across the network before the user is finished building the query.
    Last edited by bill_dev; 09-19-03 at 01:53.

Posting Permissions

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