select column if not exists return as null - SQL

Question

I have two possibilities to get data from two different tables: Table A and Table B.

The table B doesn't have one column(ColumnD), so I wanna to keep the full schema

I tried using Python(Spark):

#input argument
table_argument = 'B'

spark.sql(f'''
SELECT 
 ColumnA,
 ColumnB,
 ColumnC,
 CASE
   WHEN '{table_argument}' = 'B' THEN NULL
 ELSE ColumnD 
 END AS ColumnD
FROM {table_argument}''')

I got always the ERROR that is not possible to get the ColumnD. The When condition didn't work.

Is there any possibility to do this?

Thanks

Barmar · Accepted Answer · 2022-09-05 20:07:29Z

1

You can't reference a nonexistent column, even in a WHEN clause that's not executed. You need to use a Python condition to construct that part of the query.

spark.sql(f'''
SELECT 
 ColumnA,
 ColumnB,
 ColumnC,
 {'NULL' if table_argument == 'B' else 'ColumnD'} AS ColumnD
FROM {table_argument}''')

answered Sep 5, 2022 at 20:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select column if not exists return as null - SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related