I have one table name employees where I have ID and hire_date column. So, I need to write query which will give output like below:

when hire_date is null for ID=101 then it will give hire_date of ID=110. Vice versa like when hire_date is null for id=110 then it will give hire date of ID=101

CodePudding user response：

You mean like this?

SELECT
    id,
    hire_date,
    CASE
        WHEN hire_date IS NULL AND id = 101 THEN
            ( SELECT hire_date FROM employees WHERE id = 110 )
        WHEN hire_date IS NULL AND id = 110 THEN
            ( SELECT hire_date FROM employees WHERE id = 101 )
        ELSE
            NULL
    END AS hire_date2
FROM
    employees

CodePudding user response：

You can use conditional aggregation in analytic functions to avoid a self-join:

SELECT e.*,
       CASE
       WHEN hire_date IS NULL AND id = 101
       THEN COALESCE(
              MIN(CASE id WHEN 110 THEN hire_date END) OVER (),
              DATE '1900-01-01' -- Default if both NULL
            )
       WHEN hire_date IS NULL AND id = 110
       THEN COALESCE(
              MIN(CASE id WHEN 101 THEN hire_date END) OVER (),
              DATE '1900-01-01' -- Default if both NULL
            )
       ELSE hire_date -- value for other rows.
       END AS value
FROM   employees e;

Which, for the sample data:

CREATE TABLE employees (id, name, hire_date) AS
SELECT 101, 'Alice', DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 110, 'Beryl', NULL              FROM DUAL UNION ALL
SELECT 111, 'Carol', DATE '1980-01-01' FROM DUAL;

Outputs:

fiddle