Обсуждение: Stuck on SQL - Any Takers?

I have a table where each record has nine player fields, like this:

tbl_teams (
fld_team_no int primary key,
fld_cust_no int,
fld_athlete1 int,
fld_athlete2 int,
fld_athlete3 int,
fld_athlete4 int,
fld_athlete5 int,
fld_athlete6 int,
fld_athlete7 int,
fld_athlete8 int,
fld_athlete9 int
);

I'm trying to build a new table that pulls the related player's name and
points from another table, but it isn't working. I'm getting this error:

ERROR:  create: repeated attribute "fld_player_last"

Is there a better way to write this query?


SELECT
tbl_cust.fld_cust_name,
tbl_zip_codes.fld_zip_code,
tbl_zip_codes.fld_city_name,
tbl_zip_codes.fld_state_short,
tbl_zip_codes.fld_area_code,
tbl_teams.fld_team_no,
tbl_athlete_stats_1.fld_player_last,
tbl_athlete_stats_1.fld_player_first,
tbl_athlete_stats_1.fld_total_points_ytd,
tbl_athlete_stats_2.fld_player_last,
tbl_athlete_stats_2.fld_player_first,
tbl_athlete_stats_2.fld_total_points_ytd,
tbl_athlete_stats_3.fld_player_last,
tbl_athlete_stats_3.fld_player_first,
tbl_athlete_stats_3.fld_total_points_ytd,
tbl_athlete_stats_4.fld_player_last,
tbl_athlete_stats_4.fld_player_first,
tbl_athlete_stats_4.fld_total_points_ytd,
tbl_athlete_stats_5.fld_player_last,
tbl_athlete_stats_5.fld_player_first,
tbl_athlete_stats_5.fld_total_points_ytd,
tbl_athlete_stats_6.fld_player_last,
tbl_athlete_stats_6.fld_player_first,
tbl_athlete_stats_6.fld_total_points_ytd,
tbl_athlete_stats_7.fld_player_last,
tbl_athlete_stats_7.fld_player_first,
tbl_athlete_stats_7.fld_total_points_ytd,
tbl_athlete_stats_8.fld_player_last,
tbl_athlete_stats_8.fld_player_first,
tbl_athlete_stats_8.fld_total_points_ytd,
tbl_athlete_stats_9.fld_player_last,
tbl_athlete_stats_9.fld_player_first,
tbl_athlete_stats_9.fld_total_points_ytd,
(tbl_athlete_stats_1.fld_total_points_ytd+
tbl_athlete_stats_2.fld_total_points_ytd+
tbl_athlete_stats_3.fld_total_points_ytd+
tbl_athlete_stats_4.fld_total_points_ytd+
tbl_athlete_stats_5.fld_total_points_ytd+
tbl_athlete_stats_6.fld_total_points_ytd+
tbl_athlete_stats_7.fld_total_points_ytd+
tbl_athlete_stats_8.fld_total_points_ytd+
tbl_athlete_stats_9.fld_total_points_ytd)
AS fld_team_score

INTO tbl_ranked_teams

FROM
tbl_athlete_stats tbl_athlete_stats_1,
tbl_athlete_stats tbl_athlete_stats_2,
tbl_athlete_stats tbl_athlete_stats_3,
tbl_athlete_stats tbl_athlete_stats_4,
tbl_athlete_stats tbl_athlete_stats_5,
tbl_athlete_stats tbl_athlete_stats_6,
tbl_athlete_stats tbl_athlete_stats_7,
tbl_athlete_stats tbl_athlete_stats_8,
tbl_athlete_stats tbl_athlete_stats_9

WHERE
tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9 and
tbl_teams.fld_cust_no = tbl_cust.fld_cust_no and
tbl_cust.fld_zip = tbl_zip_codes.fld_zip_code

ORDER BY fld_team_score DESC;

"Tim Perdue" <perdue@raccoon.com> writes:
> I have a table where each record has nine player fields, like this:
> ...
> I'm trying to build a new table that pulls the related player's name and
> points from another table, but it isn't working. I'm getting this error:
> ERROR:  create: repeated attribute "fld_player_last"
> [ from a SELECT ... INTO ]

You didn't say whether you figured that out, but you are going to have
to add AS clauses to the SELECT so that each column has a distinct
title.  In a plain SELECT the system is not picky about duplicate
column titles, but when it's trying to create an output table it is.

> I played with the query a little bit. In a nutshell, the performance is
> nothing short of horrible. 27:01 CPU time and no results - not even one
> record inserted into the new table.

If you're on 6.4, you're probably running into some of the optimizer
bugs that Bruce fixed for 6.5.  These bugs cause planning of queries
that join more than about half a dozen tables to take an unreasonable
amount of time.  (If EXPLAINing the query takes a long time too, that's
definitely what you are hitting.)

You can work around the bugs by setting GEQO to a smaller value, like
about 6.

What plan does EXPLAIN report for this query, anyway?

            regards, tom lane

On Fri, 19 Mar 1999, Tom Lane wrote:

> You can work around the bugs by setting GEQO to a smaller value, like
> about 6.
>

And how can i do it ??
Change the GEQO to a smaller value, and what does it means ??
I know GEQO is the genetic query optimizer but i didn't know it was
possible to configure it.

Thank you.



David Martinez Cuevas
     Office 622-60-80      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     Home 565-25-17          "Eat Linux, Drink Linux...  SMOKE LINUX "
                           @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@