I have found when the issue arises. Below is a simplified version of the situation.

I create a temporary VIEW called ‘v_dim_One’ with a random column and a rownum which has a maximum value of for example 200.

%sql

CREATE OR REPLACE GLOBAL TEMPORARY VIEW v_dim_One AS

SELECT

    row_number() over (order by  ColA asc) AS DimA_NK

    , ColA

FROM TableOne

Then I create a second temporary view that has a UNION in it and JOINS the ‘v_dim_One’ to return the rownum column ‘DimA_NK’.

%sql

CREATE OR REPLACE GLOBAL TEMPORARY VIEW v_dim_Two AS

SELECT

ColB

, DimA_NK

FROM TableTwoFirst

LEFT JOIN global_temp.v_dim_One ON ColA=ColB

UNION

SELECT

ColB

, DimA_NK

FROM TableTwoSecond

LEFT JOIN global_temp.v_dim_One ON ColA=ColB

Now when I do a select * from  global_temp.v_dim_Two the previously described error occurs.

I did some extensive tests. The error will not occur in the following scenarios:
- dim_Two only selects from TableTwoFirst (so only the part above the union)

- dim_Two only selects from TableTwoSecond (so only the part below the union)

- Directly query the statement used to create temp view ‘v_dim_Two’

I can prevent the error from occurring by casting the dimA_NK as a long:

%sql

CREATE OR REPLACE GLOBAL TEMPORARY VIEW v_dim_Two AS

SELECT

ColB

, CAST(DimA_NK as long) as DimA_NK --> CAST AS LONG

FROM TableTwoFirst

LEFT JOIN TableOne ON ColA=ColB

UNION

SELECT

ColB

, CAST(DimA_NK as long) as DimA_NK --> CAST AS LONG

FROM TableTwoSecond

LEFT JOIN TableOne ON ColA=ColB

So conclusion, however weird, is to cast every column which is coming from a joined table and which is created through a row_number function, as a LONG whenever a UNION is used. If anyone can explain why this is required even when the rownum value is very small, I am open for reasoning!