Re: UDF Sub-select query problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Carl Howarth (carl_at_carlhowarth.net)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 14:46:17 -0000

The idea of the design is set up in a tree structure so that we may use a
circular reference on the table and have an unlimited number of nodes which,
without going into the whole design of the system is neccessary.

I think the only way to get this extract to work is by using a cursor to
generate a temp table each time. This is a pain and inefficient though the
extract is going to be ran at 3am each morning so the impact is not a
problem.

Thanks, Carl

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1108734969.791580.197880@z14g2000cwz.googlegroups.com...
> Your design doesn't make much sense to me. "NodeValue" here is
> multi-valued column and basically the root of your problem is that you
> haven't identified all the entities properly in tables. Why not a more
> regular, normalized table structure? For example:
>
> CREATE TABLE Vehicles (..., maker_code CHAR(10) NOT NULL, model
> CHAR(10) NOT NULL, FOREIGN KEY (maker_code, model) REFERENCES
> MakerModels (maker_code, model_code), engine_size NUMERIC(5,2) CHECK
> (engine_size BETWEEN 1.0 AND 10), /* PRIMARY KEY NOT SPECIFIED */)
>
> ... etc
>
> Now you can still represent this as a "tree" for your users but your
> queries will likely be much more efficient using joins rather than
> subqueries and table-valued functions. As is so often the case, correct
> design can solve many seemingly knotty query problems.
>
> --
> David Portas
> SQL Server MVP
> --
>

Relevant Pages

  • Re: Database design for tree structure
    ... I have designed a database which for tree structure. ... single sql statement to retrieve all nodes by using the field "path" ... sort by name, it cannot be done, any design than can reduce the ...
    (comp.databases.oracle.server)
  • RE: Extracting data from strings
    ... accomplish tasks using different objects versus cell or sheet manipulation. ... I am not going to explain the details of a table and its design view versus ... Excel, using many of the same function names you used in Excel (except SEARCH ... This the formula I used in Excel to extract the first variable, ...
    (microsoft.public.access.gettingstarted)
  • RE: Extracting data from strings
    ... accomplish tasks using different objects versus cell or sheet manipulation. ... I am not going to explain the details of a table and its design view versus ... Excel, using many of the same function names you used in Excel (except SEARCH ... This the formula I used in Excel to extract the first variable, ...
    (microsoft.public.access.gettingstarted)
  • RE: Extracting data from strings
    ... Seth, ... data, so I can't change the report, I can only attempt to extract the data I ... I am not going to explain the details of a table and its design view versus ... Excel, using many of the same function names you used in Excel (except SEARCH ...
    (microsoft.public.access.gettingstarted)
  • Re: SQL design vs VB design
    ... But again that can be poor design sample, that's why I said - I will ... In fact - I already have another separate table with those ledger codes ... If developer need to agregate some values (like -sum, extract, ... Like for this accounts 6199 = 6110+6125+6130. ...
    (comp.databases.ms-access)