Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: missing FROM-clause entry for table "t"

    Hi,

    I'm somehow new to PostGreSql and have a problem with the following query:

    Code:
    select 
    	* 
    from
    	(
    		select "IdVictimPlayer" as p, "Damage" as d, "IdAttackerWeapon" as w, sum("dd")
    		from
    			(
    				select 
    					"IdVictimPlayer", "Damage", "IdAttackerWeapon", ("Damage" - t.d) as dd 
    				from 
    					fact_eventplayerdamage 
    				order by 
    					dd 
    				limit 5
    			) as t1 
    		group by
    			p, d, w  
    	) as t

    What I expect the following sql command to get the summation of "damages" of the nearest five rows to each row of the table. Two row are nearer to each other, if their damage values are nearer.

    The problem is that I cannot access 'd' in my inner subquery.

    Code:
    ERROR:  missing FROM-clause entry for table "t"
    LINE 9: ...layer", "Damage", "IdAttackerWeapon", ("Damage" - t.d) as dd...


    Please help me, if you know how I can solve my problem.



    Thanks,
    Reza

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I have no idea what you are trying to achieve, sorry.

    Please show us the table structure (CREATE TABLE...) some sample data and the expected result.

    And please remove the tab characters when pasting code.
    Your example is unnecessary wide due to the tab characters and very hard to read because of that.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    err, why do MS SQL developers always have to quote everything?... and write ugly code

    Anyhow, your problem is specifically with the ("Damage" - t.d). You have no table or table alias for "t" and I'm hoping that you've got no column named "d" You've got an alias "T1" and "d" in an outer query, but you can't reference either of those inside your subquery.

  4. #4
    Join Date
    Nov 2009
    Posts
    6
    Sorry for the ugly code I told you, I'm newbie

    Anyways, thanks for the answers. So, as it seems to me, I cannot access the aliases I have made in the first query, from my subquery. I was hoping that I can do that.

    What I want to do is to: For each combination of players, weapons and damages, find 5 rows that are the nearest to this combination. Being nearer means that their damage values are nearer.

    Code:
    CREATE TABLE fact_eventplayerdamage
    (
      "Id" integer NOT NULL,
      "IdVictimPlayer" integer NOT NULL,
      "IdAttackerWeapon" integer NOT NULL,
      "Damage" double precision,
    )
    And this is my query:
    Code:
    (select "IdVictimPlayer" as p, "Damage" as d, "IdAttackerWeapon" as w, sum("dd")
    from(select "IdVictimPlayer", "Damage", "IdAttackerWeapon", ("Damage" - t.d) as dd 
    from fact_eventplayerdamage order by dd limit 5) as t1 
    group by p, d, w  ) as t


    What I'm doing in this query is that for each combination c, I want to get the top 5 rows, ordered by their difference with the damage of c.


    Does it make sense now?

    --
    Reza

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pluspluss View Post
    What I'm doing in this query is that for each combination c
    I neither see a column called "c" nor a table nor an alias

    An easy to spot error are the brackets around the whole statement. Basically you are writing
    Code:
    (SELECT * FROM mytable) as T
    and that is wrong.

    Secondly: you cannot reference an alias from the "inside out", so your usage of "t.d" will not work.


    Does it make sense now?
    Not without some sample data and the expected result...

    Btw: I strongly recommend not to use quoted identifiers. They create more trouble than they are worth

  6. #6
    Join Date
    Nov 2009
    Posts
    6
    Code:
    Id	Player	Weapon	Damage
    1	1	1	10
    2	2	1	11
    3	3	1	12
    4	4	1	13
    5	1	1	14
    6	2	1	15
    7	3	1	16
    8	4	2	17
    9	5	2	18
    10	7	2	19
    Consider that I'm now talking about the 2nd row. I expect to get 64 (10 + 12 + 13 + 14 + 15) as it is the sum of damage of rows 1, 3, 4, 5, 6 which are the rows that have the nearest value of damage to damage of row 2 (which is 11).
    I want to have this result for every row of my table. So, the final result will be something like:

    Code:
    1	11+12+13+14+15
    2	10+12+13+14+15
    3	10+11+13+14+15
    4	11+12+14+15+16
    ...
    We may just ignore the combination that I talked about and do this for each row of the table.

    Thanks a lot for the help.

    --
    Reza

  7. #7
    Join Date
    Nov 2009
    Posts
    6
    Btw: I strongly recommend not to use quoted identifiers. They create more trouble than they are worth
    A silly question. What do you mean by quoted identifiers?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Thanks for the sample data, that makes things clearer.

    Quote Originally Posted by pluspluss View Post
    A silly question. What do you mean by quoted identifiers?
    Quoted: "IdVictimPlayer"
    Not-quoted: idvictimplayer

    Code:
    Consider that I'm now talking about the 2nd row. 
    I expect to get 64 (10 + 12 + 13 + 14 + 15) as it is the sum of damage of rows 1, 3, 4, 5, 6 which are the rows that have the nearest value of damage to damage of row 2 (which is 11).
    So "nearest" means: 5 rows in ascending order (ordered by damage) where the damage column is equal or higher than the "current" row?

    If I understood you correctly, your first try wasn't that far away
    Code:
    select id, 
           (select sum(damage) 
            from (select damage
                    from fact_eventplayerdamage f2
                   where f2.damage >= f.damage
                   order by damage asc
                   limit 5) t
           )
    from fact_eventplayerdamage f
    (You will need to re-introduce qutoes and make sure the case for the column names are correct)

  9. #9
    Join Date
    Nov 2009
    Posts
    6
    Thanks for the answer. That worked, although it doesn't seem to be efficient, since when I add another constraint to the where clause (like "and f2."IdAttackerWeapon"=f."IdAttackerWeapon"), it takes too much time to run.

    I'm just wondering why in my first query, I couldn't access to the value of the outer query, but here I could. Was there a problem with the aliasing syntax or the query was completely incorrect?

    Is it like this that a subquery in select can access the value of the outer select, but a subquery in from cannot?


    Thanks again for the time
    --
    Reza

  10. #10
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    (You will need to re-introduce qutoes and make sure the case for the column names are correct)
    Assuming that OP created the table with quoted identifiers. If he didn't, he can drop the quotes. Read an interesting article about fixing this at Postgres Planet.
    Lowercasing table and column names - Postgres OnLine Journal

    What version of Postgres are you using? Version 8.4 has windowing functions that make this type of problem trivial.

  11. #11
    Join Date
    Nov 2009
    Posts
    6
    So, this is my final query:
    Code:
    select 	"Damage" , "IdAttackerWeapon" as w, 
    	(select sum(dd) 
    	from 
    		(select "Damage", "IdAttackerWeapon", abs(("Damage" - t."Damage")) as dd 
    		from fact_eventplayerdamage order by dd asc limit 5) 
    	t1)
    from fact_eventplayerdamage t													
    group by "Damage", w
    Thanks all for the help.

  12. #12
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Is it like this that a subquery in select can access the value of the outer select, but a subquery in from cannot?
    You can only do correlate subqueries in SELECT and WHERE clauses.

  13. #13
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    What version of Postgres are you using? Version 8.4 has windowing functions that make this type of problem trivial.
    Yes, I was thinking about that as well, but I don't think this problem is solveable with window functions.
    At least I could not think of a solution

    The problem is the the requirement with the "5 rows" and the fact that we are talking about the difference of the damage column between the "current row" and any other row.

Tags for this Thread

Posting Permissions

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