I have a table as below in Oracle

Now I want the output for max gen_id which is 3 in this case and serial_code 'smcg' and 'fmcg' I can get the output easily with queries but I want it in a format as below.

How can i achieve this? Any help is much appreciated. Thanks in advance

CodePudding user response：

This does it, but doesn't look very pretty. Read comments within code.

SQL> WITH
  2     test (gen_id, serial_code, is_verified)
  3     AS
  4     -- sample data
  5        (SELECT 1, 'fmcg', 'Y' FROM DUAL
  6         UNION ALL
  7         SELECT 1, 'smcg', 'Y' FROM DUAL
  8         UNION ALL
  9         SELECT 1, 'xmcg', 'N' FROM DUAL
 10         UNION ALL
 11         SELECT 3, 'smcg', 'Y' FROM DUAL
 12         UNION ALL
 13         SELECT 3, 'amcg', 'Y' FROM DUAL),

 14     maxgen (gen_id)
 15     AS
 16     -- MAX gen_id value; will be used later
 17     (SELECT MAX (gen_id) FROM test),
 18     temp
 19     AS
 20        -- compose NOT_PRESENT rows that don't exist in original (TEST) table
 21        (SELECT t.gen_id, c.serial_code, 'not_present' is_verified
 22           FROM test t
 23                CROSS JOIN (SELECT DISTINCT serial_code
 24                              FROM test) c
 25                JOIN maxgen m
 26                   ON     m.gen_id = t.gen_id
 27                      AND (t.gen_id, c.serial_code) NOT IN
 28                             (SELECT gen_id, serial_code FROM test))
 29  -- finally: union existing rows (from TEST) with NOT_PRESENT ones (from TEMP)
 30  SELECT a.gen_id, a.serial_code, a.is_verified
 31    FROM test a JOIN maxgen m ON m.gen_id = a.gen_id
 32   WHERE serial_code IN ('smcg', 'fmcg')
 33  UNION
 34  SELECT x.gen_id, x.serial_code, x.is_verified
 35    FROM temp x
 36   WHERE x.serial_code IN ('smcg', 'fmcg');

    GEN_ID SERIAL_CODE  IS_VERIFIED
---------- ------------ -----------
         3 fmcg         not_present
         3 smcg         Y

SQL>

CodePudding user response：

You can use a PARTITIONed OUTER JOIN for this.

From Oracle 12:

SELECT t.gen_id,
       s.serial_code,
       COALESCE(t.is_verified, 'not_present') AS is_verified
FROM   (SELECT 'smcg' AS serial_code FROM DUAL UNION ALL
        SELECT 'fmcg' FROM DUAL) s
       LEFT OUTER JOIN (
         SELECT *
         FROM   table_name
         ORDER BY gen_id DESC
         FETCH FIRST ROW WITH TIES
       ) t
       PARTITION BY (gen_id)
       ON (s.serial_code = t.serial_code)

In Oracle 11, you can use:

SELECT t.gen_id,
       s.serial_code,
       COALESCE(t.is_verified, 'not_present') AS is_verified
FROM   (SELECT 'smcg' AS serial_code FROM DUAL UNION ALL
        SELECT 'fmcg' FROM DUAL) s
       LEFT OUTER JOIN (
         SELECT gen_id, serial_code, is_verified
         FROM   (
           SELECT t.*,
                  RANK() OVER (ORDER BY gen_id DESC) AS rnk
           FROM   table_name t
         )
         WHERE  rnk = 1
       ) t
       PARTITION BY (gen_id)
       ON (s.serial_code = t.serial_code)

Which, for the sample data:

CREATE TABLE table_name (gen_id, serial_code, is_verified) AS
SELECT 1, 'fmcg', 'Y' FROM DUAL UNION ALL
SELECT 1, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 1, 'xmcg', 'N' FROM DUAL UNION ALL
SELECT 2, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 2, 'fmcg', 'Y' FROM DUAL UNION ALL
SELECT 2, '2mcg', 'Y' FROM DUAL UNION ALL
SELECT 3, 'smcg', 'Y' FROM DUAL UNION ALL
SELECT 3, 'amcg', 'Y' FROM DUAL;

Both output:

   PARTITION BY (gen_id)
   ON (s.serial_code = t.serial_code)

GEN_ID	SERIAL_CODE	IS_VERIFIED
3	fmcg	not_present
3	smcg	Y

Oracle 18 db<>fiddle here - Oracle 11 db<>fiddle here