Output records with null values even if not present

I have a table as below in Oracle

gen_id	serial_code	is_verified
1	fmcg	Y
1	smcg	Y
1	xmcg	N
2	smcg	Y
2	fmcg	Y
2	2mcg	Y
3	smcg	Y
3	amcg	Y

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.

gen_id	serial_code	is_verified
3	smcg	Y
3	fmcg	not_present

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

Answer

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