1

I want to create a hardcoded table so that I can use it as a left join elsewhere.

Color Red Blue Green Yellow

I tried alot of combinations like

with TempTable as ( select * from ("red","blue","green","yellow") as color )

but this doesn't seem to work

Improve this question
1

2 Answers 2

Reset to default
1

Temporary Approach

You may use inline tables

SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)

Example As a Common Table Expressions (CTEs):

WITH colors as ( SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color) ) SELECT tbl1.*, colors.color FROM tbl1 LEFT JOIN colors ON tbl1.color = colors.color

Example as a subquery:

SELECT tbl1.*, colors.color FROM tbl1 LEFT JOIN (SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)) colors ON tbl1.color = colors.color

Persisting this table

create table colors AS SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)

You could then query this as regular table

Reference

Inline Tables

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

Comments

0

You can also create a temp view:

create or replace temp view TempTable as ( select * from values ('Red'), ('Blue'), ('Green'), ('Yellow') )
Improve this answer

Comments