Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    11

    Unanswered: evaluate logic output as resulting field from query

    have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.

    For a very general example: In the .NET code, the following variables are recognized:
    sex="M"
    Paid=4350.00
    Outstanding=28000.50

    One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..

    EVAL EXPR
    BOOL Sex='F' and Paid/Outstanding < 27.50
    BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
    INT Paid*52.33

    In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?

    If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This used to be possible in SQL 6.5, but it has been a challenge since then due to changes in the underlying SQL engine. The query tree (a precursor to the query plan) is already constructed before the query begins to execute... You can't easily add additional logic at that point. Once your query starts to execute, you're along for the ride...

    It might be possible to do this if you knew enough about the engine and were gutsy enough to try it, but I don't have the knowledge or the time for that kind of thing.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The condition is very strict. Are you sure that there is nothing that can be done on the backend to accomodate the requirements?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2005
    Posts
    11
    before converting it to asp.net (from asp), what I did was:

    a: use the input data to formulate the proper query
    b: grab the query results and cycle through them
    c: use VB's "execute" command to further test each record and further evaluate the code

    First off, this wasnt as clean of a solution as I'd have liked, especially if it ends up sending thousands of results to the resultset, but really needs that final step to get the biggest filter. That seems like it could really bog it down.

    Now with asp.net, I'm not sure I can even do that, since its pre-compiled...

Posting Permissions

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