I have a first SQL query which return the following :

TEST_NAME	SUBNAME	BDATE	CODE	Number
TEST1	Blabla1	01-JAN-2022	TEST_A	15645
TEST1	Blabla1	01-MAR-2022	TEST_B	58464
TEST1	Blabla1	01-JUN-2022	TEST_C	46456
TEST1	Blabla1	01-SEP-2022	TEST_D	68676
TEST1	Blabla1	01-DEC-2022	TEST_E	68766
TEST2	Blabla2	01-JAN-2022	TEST_A	15645
TEST2	Blabla2	01-MAR-2022	TEST_B	58464
TEST2	Blabla2	01-JUN-2022	TEST_C	46456
TEST2	Blabla2	01-SEP-2022	TEST_D	68676
TEST2	Blabla2	01-DEC-2022	TEST_E	68766

On an other side I have made an other sql request :

SELECT * 
FROM db.Test_Table TT
WHERE TT.TC_CODE = 1 

Which gibes the below output :

A	B	C
TEST1	05-MAR-2022	4564123
TEST1	05-DEC-2022	1561618
TEST2	05-JAN-2022	1651156
TEST2	05-JUN-2022	1564132
TEST2	05-SEP-2022	1561565

I was wondering how to simply join the above table to the first one, to have the below output :

TEST_NAME	SUBNAME	BDATE	CODE	Number
TEST1	Blabla1	01-JAN-2022	TEST_A	15645
TEST1	Blabla1	01-MAR-2022	TEST_B	58464
TEST1	Blabla1	05-MAR-2022	NewItem	4564123
TEST1	Blabla1	01-JUN-2022	TEST_C	46456
TEST1	Blabla1	01-SEP-2022	TEST_D	68676
TEST1	Blabla1	01-DEC-2022	TEST_E	68766
TEST1	Blabla1	05-DEC-2022	NewItem	1561618
TEST2	Blabla2	01-JAN-2022	TEST_A	15645
TEST2	Blabla2	05-JAN-2022	NewItem	1651156
TEST2	Blabla2	01-MAR-2022	TEST_B	58464
TEST2	Blabla2	01-JUN-2022	TEST_C	46456
TEST2	Blabla2	05-JUN-2022	NewItem	1564132
TEST2	Blabla2	01-SEP-2022	TEST_D	68676
TEST2	Blabla2	05-SEP-2022	NewItem	1561565
TEST2	Blabla2	01-DEC-2022	TEST_E	68766

TRYING to do :

SELECT * FROM T1
UNION
SELECT 
TT.A as TEST_NAME,
TT.B as BDATE,
TT.C as Number,
FROM db.Test_Table TT

Throw :

01789. 00000 -  "query block has incorrect number of result columns"

Also I don't know how to fill empty cells with corresponding value, I guess that if the UNION works, I would have the following DF

TEST_NAME	SUBNAME	BDATE	CODE	Number
TEST1	Blabla1	01-JAN-2022	TEST_A	15645
TEST1	Blabla1	01-MAR-2022	TEST_B	58464
TEST1	(null)	05-MAR-2022	NewItem	4564123
TEST1	Blabla1	01-JUN-2022	TEST_C	46456
TEST1	Blabla1	01-SEP-2022	TEST_D	68676
TEST1	Blabla1	01-DEC-2022	TEST_E	68766
TEST1	(null)	05-DEC-2022	NewItem	1561618
TEST2	Blabla2	01-JAN-2022	TEST_A	15645
TEST2	(null)	05-JAN-2022	NewItem	1651156
TEST2	Blabla2	01-MAR-2022	TEST_B	58464
TEST2	Blabla2	01-JUN-2022	TEST_C	46456
TEST2	(null)	05-JUN-2022	NewItem	1564132
TEST2	Blabla2	01-SEP-2022	TEST_D	68676
TEST2	(null)	05-SEP-2022	NewItem	1561565
TEST2	Blabla2	01-DEC-2022	TEST_E	68766

How can I replace these (null) by right value ? For this example values would be Blabla1 or Blabla2 depending on TEST_NAME

CodePudding user response：

Step 1: we start with a UNION ALL to combine the 2 tables.

       SELECT TEST_NAME, SUBNAME, BDATE, CODE     , Number FROM T1
UNION  SELECT A        , NULL   , B    , 'NewItem', C      FROM db.Test_Table

BTW, the column names are entirely deduced from the query before the first UNION, you can remove all the AS ColumnAlias after that first part.

---

Step 2: with the above query, the SUBNAME column is empty for all the records that come from Test_Table.

On that part, you must notice your database is not super good in terms of normal forms (1NF, 2NF, 3NF). Here, it means I will need to use the DISTINCT keyword, which should normally not be necessary.

There are 2 methods to fill SUBNAME

Method 1: Scalar subquery (= a query that returns a single value).

Replace NULL from the query in Step 1 by:

(SELECT DISTINCT SUBNAME FROM T1 WHERE TEST_NAME = Test_Table.A)

Method 2: JOIN

...
UNION ALL
SELECT A, T.SUBNAME, B, 'NewItem', C
FROM db.Test_Table
JOIN (SELECT DISTINCT TEST_NAME, SUBNAME FROM T1) T ON A = TEST_NAME

Again, I suppose you will not need DISTINCT in your real case.

Additional notes:

• Method 1 will throw an error if the subquery returns more than 1 record (= if a TEST_NAME is linked to 2 SUBNAME)
  If you expect TEST_NAME will only be linked to 1 value for SUBNAME, then I urge you to consider this error as a good thing, that is a protection that will tell you the data may not look like what you expect.
• In Oracle but possibly in some other databases, a scalar subquery could be faster than the JOIN counterpart due to the use of a cache. See here for instance.