1

I'm using Teradata. Trying to understand a very strange thing.

SELECT
    ID,
    TYPE,
    '' AS SUB_TYPE
FROM Table_A
UNION ALL
SELECT
    ID,
    TYPE,
    SUB_TYPE
FROM TABLE_B

Somehow, valid values of Table B become blank in the result.

ID TYPE SUB_YPE comment
1 A type from table A correct, empty string as a constant
2 A type from table B incorrect, sub type has a value in B
3 Another type from table B null correct, sub type is null in B

But when I exchange query 1 and 2, everything works fine.

SELECT
    ID,
    TYPE,
    SUB_TYPE
FROM TABLE_B
UNION ALL
SELECT
    ID,
    TYPE,
    '' AS SUB_TYPE
FROM Table_A
ID TYPE SUB_TYPE comment
2 A type from table B A sub type from table B correct
3 Another type from table B null correct
1 A type from table A correct

This is bizarre, can anyone help explain?

Billie_H
  • 69
  • 7
  • You should show how is the data in every table. In any case why would you do an union all without any condition? You should get repeated rows by ids – Leandro Bardelli Apr 12 '23 at 03:14
  • 1
    @LeandroBardelli The queries are simplified to demonstrate the problem - In the actual query I do have WHERE clauses that target a specific list of IDs and the current snapshot of the tables. UNION ALL should be fine and probably faster than UNION. Table A and B store different sets of IDs (A for individual customers and B for entity customers) so will not have duplicates. – Billie_H Apr 12 '23 at 04:35
  • 3
    The first Select determines the data type, you probably have to `CAST('' AS VARCHAR(xx))` to match the column from the 2nd table. – dnoeth Apr 12 '23 at 04:57
  • @dnoeth I see, thanks. If my understanding is correct, the 1st query implies CHAR(0), so the 2nd query turns all VARCHAR(xx) values into CHAR(0) too - Interesting. – Billie_H Apr 13 '23 at 07:56
  • Yes, `select type('')` returns `VARCHAR(0) CHARACTER SET UNICODE` – dnoeth Apr 13 '23 at 08:14

0 Answers0