Informix - Problem description
Problem IT30280 | Status: Closed |
-324 AMBIGUOUS COLUMN IN ORDER BY NOT USING ALIAS FROM PROJECTION LIST OF DERIVED TABLE | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
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 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC14 and 14.10.xC3. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.09.2019 25.02.2020 25.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC14, 14.10.xC3 | |
Problem solved according to the fixlist(s) of the following version(s) |