Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Question Unanswered: Use subquery more than once in query

    Hi.

    I want to use some subquery in one big query more than one time. Is the only solution temptable? Does postgre have samothing like with clausule? (http://www.orafaq.com/node/1879)

    I want to do something like this:

    select a, b, c, (select a + b from t) from t where (select a + b from t) > x;

    (select a + b from t) is wery complex select.

    so I want to do something like "select a, b, c, (select a + b from t) as QQ from t where QQ > x".

    If someone have some nice solution, please give it here.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by LuVar
    Is the only solution temptable?
    What about a view?
    Does postgre have samothing like with clausule? (http://www.orafaq.com/node/1879)
    No I don't think so.

    select a, b, c, (select a + b from t) from t where (select a + b from t) > x;
    This does not make sense to me. I'm not really sure whether where (select a + b from t) > x is actually legal.

    Out of the blue, I'd try something like this:

    Code:
    SELECT t1.a, t2.b, cx.myvalue
    FROM
      (SELECT ti.A + ti.B as myvalue FROM t ti) cx, 
      t
    WHERE cx.myvalue > somevalue;
    Haven't tried this though.
    And I'm sure you'll need some kind of join to the derived table

  3. #3
    Join Date
    Sep 2008
    Posts
    2
    My concrete select. Bold parts are same, and I want it somehow to optimalize. Previous example with plus sign in select was missplaced. This real example should be explanatory enough.

    Code:
    SELECT
    (SELECT aaa from cache where bbb = (select bbb from data where id = data_fk)) as aaaColumn,
    date + interval '2 hours'
    from input 
    where 
    input_number = 51 and 
    (SELECT aaa from cache where bbb = (select bbb from data where id = data_fk)) like 'BA%' and
    data_fk = (select id from data where bbb = (select bbb from cache where aaa = 'BA-123')) and 
    date >= '2008-09-07 16:00:00'
    order by data_fk, date;
    PS: view isnt bad idea, but it should be parametric (if it could be done).

    OT: does postgre somehow cache a results of views? (if the view selecs is crazy and it takes minute to be done...)
    Last edited by LuVar; 09-18-08 at 04:45.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Is that the real statement?
    How are the tables data, cache and input related?
    Where does the column data_fk come from?

    And please put your statements into [ code ] tags so that they easier to read.
    does postgre somehow cache a results of views
    Postgres does not have materialized views as Oracle does. But if the tables are properly indexed I would only start optimizing this, if you really see a performance problem. How big (=number of records) are those tables and how are they indexed? How does the execution plan look like?

Posting Permissions

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