Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Question Unanswered: reference calculated collumn in SELECT

    Hello,

    I want to reference a collumn of a selectstatement in itself as following:


    SELECT C.ID * 2 AS "SOURCE" , SOURCE * 10 FROM TEST C ;

    The dbms says that "SOURCE" ist not a known collumn. I look for a sollution for build a big view with collumn based collumns. There are no SQL functions and subqueries allowed.

    Does anybody know a way to implement that ?

    rgs

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    At first glance ... why wouldn't you select this:

    select
    c.id * 2 source,
    c.id * 2 * 10 ten_sources
    from test c;

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    because this is only a basic research. I want to build a view with more than 40 collumns and the most of these are based on a other column of these view.
    The main resaon to do it on this wa is that I want to write each formula in the view only once.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think you'll have to write formulas many times I wouldn't know how to do this. Perhaps someone else will.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do this:
    Code:
     
    SELECT SOURCE, SOURCE * 10 
    FROM (SELECT C.ID * 2 AS SOURCE FROM TEST C);
    It is overkill here, but if you were re-using SOURCE many times in the query it may make sense.

  6. #6
    Join Date
    Aug 2004
    Posts
    9
    I Think this is one way to implement that, but no sollution for my special problem. What is when a result called SOURCE3 is based by a formula on SOURCE2 ... an so on.. its like hierarchical formulas..

    DBMS systems are not able to analyse math formula dependencies. In my point of view the dependencies must be described by subsubsubSelects ...

    I think a well dbms must handle this :

    SELECT SQR(C1) * -1 AS "C2" , ABS( COL ) * 5 AS "C1" , C2 -5 AS C3 FROM ...


    rgs

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are right, SQL can't do that. The SQL SELECT clause can only look "down" into values from the tables and views in the FROM clause, it cannot look "across" to other values in the same SELECT clause. That's just the way it is.

    The "select from select" approach is the way around it; why do you say it is no solution for you? You mean you just don't like it?

  8. #8
    Join Date
    Aug 2004
    Posts
    9
    nono .. I like SQL

    Its no way because I have 30 hierarchical levels in this formulas. That means when I implement that using subselects I have 30 subselects and 30 levels.:

    select X from
    ( select Yfrom
    ( select Z from
    (
    .
    .
    .
    up to 30 subselects levels!! .. o no please not !
    )
    )
    )

    .. X depends from Y, and Y from Z ... and so on till 30 dependencies

Posting Permissions

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