This problem was reported in 12.10.FC8 and I confirmed it's also
an issue in 12.10.FC12W1.
The select below will return this error with regards to the
order by in the statement. It would
seem the ambiguity would be resolved by the fact that the
hydraulisch_punt_nummer is a unique
alias in the query's projection list.
324: Ambiguous column (hydraulisch_punt_nummer).
Error in line 36
Near character position 37
The query is:
SELECT
QueryRWZIs.Hydraulisch_Punt_Nummer AS Hydraulisch_Punt_Nummer,
QueryUitlaten.Hydraulisch_Punt_Nummer AS
Hydraulisch_Punt_Nummer1
FROM
( SELECT
d_hydraulisch_punt.hydpt_aqf_id AS Hydraulisch_Punt_Nummer
FROM
dwh:"informix".d_hydraulisch_punt d_hydraulisch_punt
GROUP BY
d_hydraulisch_punt.hydpt_aqf_id
) QueryRWZIs LEFT OUTER JOIN
( SELECT
D1.C0 AS Hydraulisch_Punt_Stroomopwaarts_Nummer,
D1.C4 AS Hydraulisch_Punt_Nummer
FROM
( SELECT
d_hydraulisch_punt_so.hydpt_aqf_id AS C0,
d_hydraulisch_punt.hydpt_aqf_id AS C4
FROM
dwh:"informix".d_hydraulisch_punt d_hydraulisch_punt_so
INNER JOIN
dwh:"informix".d_hydraulisch_punt d_hydraulisch_punt
ON
d_hydraulisch_punt_so.hydpt_intern_id =
d_hydraulisch_punt.gem_intern_id
) D1
GROUP BY
D1.C0,
D1.C4
) QueryUitlaten ON QueryRWZIs.Hydraulisch_Punt_Nummer =
QueryUitlaten.Hydraulisch_Punt_Stroomopwaarts_Nummer
WHERE
QueryUitlaten.Hydraulisch_Punt_Nummer IS NULL
GROUP BY
QueryRWZIs.Hydraulisch_Punt_Nummer,
QueryUitlaten.Hydraulisch_Punt_Nummer
ORDER BY
Hydraulisch_Punt_Nummer ASC NULLS LAST;
and the table schema is:
create database dwh with log;
create table d_hydraulisch_punt
(
hydpt_intern_id serial not null ,
hydpt_id integer not null ,
hydpt_aqf_id integer not null ,
geo_xy varchar(50),
gps_geo_xy varchar(50),
opmerking varchar(250),
verwijderd integer not null ,
hptty_code varchar(10) not null ,
hptty_omschrijving varchar(50) not null ,
dat_wijziging_aqf datetime year to fraction(3),
start_proj_id integer not null ,
start_dat_oplevering date not null ,
start_proj_aqf_id varchar(20) not null ,
start_ip_jaar integer,
start_proj_type varchar(3),
stop_proj_id integer not null ,
stop_dat_oplevering date not null ,
stop_proj_aqf_id varchar(20) not null ,
stop_ip_jaar integer,
stop_proj_type varchar(3),
renovatie_proj_id integer,
renovatie_dat_oplevering date,
renovatie_proj_aqf_id varchar(20),
renovatie_ip_jaar integer,
renovatie_proj_type varchar(3),
koppt_id integer,
koppt_aqf_id decimal(20,0),
koppt_volgnr integer,
koppt_geo_xy varchar(50),
vhaz_intern_id integer not null ,
vhadb_intern_id integer not null ,
vhas_intern_id integer not null ,
gem_intern_id integer not null ,
zvg_intern_id integer not null ,
uitla_volgnr integer,
uitla_zuiv_zone varchar(1),
uitty_code varchar(10),
uitty_omschrijving varchar(50),
ins_batch_id integer,
upd_batch_id integer,
rwzi_intern_id integer not null ,
status varchar(30),
uitlaat_nummer varchar(10),
afsgb_intern_id integer
default -2 not null ,
primary key (hydpt_intern_id)
);
Interestingly enough, there are a number of workarounds, but for
cases where the query
may be generated and not easily modified, they are not
applicable workarounds:
All of these modifications below do not change the query
semantically but do alleviate the -324 error
allowing the query to run.
(1) Changing the GROUP BY to:
GROUP BY
-- QueryRWZIs.Hydraulisch_Punt_Nummer,
-- QueryUitlaten.Hydraulisch_Punt_Nummer
Hydraulisch_Punt_Nummer,
Hydraulisch_Punt_Nummer1
(2) Changing the GROUP BY to:
GROUP BY
1,2
(3) Changing the ORDER BY to:
ORDER BY
2 ASC NULLS LAST;
(4) Changing the ORDER BY to:
ORDER BY
QueryRWZIs.Hydraulisch_Punt_Nummer ASC NULLS LAST;
There are others too |
