• Admin-Scout-small-Banner
           
    CURSOR Admin-Scout
    get the ultimate tool for Informix
    pfeil  
invispix 10x10
invispix 10x10
Latest versionsfixlist
14.10.xC5 FixList
12.10.xC14.X5 FixList
11.70.xC9.XB FixList
11.50.xC9.X2 FixList
11.10.xC3.W5 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

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
not known / see Local fix
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)
invispix 10x10
invispix 10x10
invispix 10x10
Technical InfoTechnical Info

Technical Info

DocumentationDocumentation

Documentation

IBM NewsletterIBM Newsletter

IBM Newsletter

Current BugsCurrent Bugs

Current Bugs

Bug ResearchBug Research

Bug Research

Bug FixlistsBug Fixlists

Bug Fixlists

Release NotesRelease Notes

Release Notes

Machine NotesMachine Notes

Machine Notes

Release NewsRelease News

Release News

Product LifecycleProduct Lifecycle

Lifecycle

Media DownloadMedia Download

Media Download

invispix 10x10
Service und SupportService und Support

Service and Support

Informix MonitoringInformix Monitoring

Informix Monitoring

Admin-Scout forInformixAdmin-Scout forInformix

Admin-Scout for Informix

Informix News & InfosInformix News & Infos

Informix News & Infos

This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.