Retrieve one of two possible values from 1 table in INFORMIX

informix

I have a table which for the same key, it will always have one record with value 'P' but sometimes that same key could have two records one with value 'P' and one with value 'L'; I need to create a VIEW that if both 'P' and'L' exist; it will return the record with 'L', ELSE, it will return record with value 'P';

ex1 has two records 'P' and 'L' -> only 'L' should be returned
ex2 has one record 'P' -> 'P' will be returned.

Best Answer

This answer assumes that your table Ex1 has columns:

  1. key_col — the key column.
  2. flag_col — the flag column, containing L or P (with a check constraint).
  3. data_col — a proxy for the unidentified set of columns containing the other data.
  4. Primary key on key_col and flag_col.

A view like this should work (I didn't say it would work fast):

CREATE VIEW Relevant_Ex1(key_col, flag_col, data_col) AS
    SELECT A.Key_Col, A.Flag_Col, A.Data_Col
      FROM Ex1 AS A
      JOIN (SELECT B.Key_Col, MIN(Flag_Col) AS Flag_Col
              FROM Ex1 AS B
             GROUP BY B.Key_Col
           ) AS C
        ON A.Key_Col = C.Key_Col AND A.Flag_Col = C.Flag_Col;

This uses the fact that the flag column contains either L or P and L sorts before P. So, if there are two rows for a given key, the value L will be selected, else the value P will be selected.

Related Question