I want to simplify (or more accurately, generalize) this query:
SELECT * FROM MRE_TABLE
WHERE col1 = 'my-data-val' OR col2 = 'my-data-val' -- OR ... there are 68 columns
I really do not want to write 67 ORs for 4 different data-values.
3 Answers
Using Snowflake SEARCH function combined with * EXCLUDE()/* ILIKE():
SELECT *
FROM mre_table
WHERE SEARCH((* EXCLUDE (ID)), 'my-data-val', SEARCH_MODE => 'EXACT');
For sample data:
CREATE OR REPLACE TABLE mre_table(
ID INT,
COL1 STRING,
COL2 STRING,
COL3 STRING,
COL4 DATE
);
INSERT INTO mre_table VALUES
(1, 'my-data-val', 'a','b',CURRENT_DATE()),
(2, 'z', 'a','b',CURRENT_DATE()),
(3, 'z', 'a','my-data-val',CURRENT_DATE());
Output:
Alternatively using ARRAY_CONTAINS:
WITH cte AS (
SELECT *, ARRAY_CONSTRUCT(* ILIKE 'COL%') AS arr
FROM mre_table
)
SELECT * EXCLUDE (arr)
FROM cte
WHERE ARRAY_CONTAINS('my-data-val'::VARIANT, arr);
2 Comments
MatBailie
Results and code as an image?
Lukasz Szozda
@MatBailie Code(the first block) and input data(the second block) are provided as text to be able to copy-paste and reproduce it. Normally I would also include link to db<>fiddle to play live with the query if RDBMS is supported. Here image to show that the query is actually working, it contains interesting language extensions.
The simplest way:
SELECT
STRING_AGG(COLUMN_NAME + ' = ''my-data-val''', ' OR ') AS where_clause
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MRE_TABLE'
5 Comments
kesarling
So the snowflake equivalents seem to be
|| for + and LISTAGG for STRING_AGG but apart from that, I think this is what I wanted. Let me see.MatBailie
@kesarling If all you wanted was a way to generate the column list as text, there are many ways to do that, and you should have said in the question :)
kesarling
lol, I did! (Just not in those words) :D
MatBailie
@kesarling Not sure how how this simplifies or generalises your query.
kesarling
It simplifies in the sense that I do not need to type 68 column names and 67 ORs manually. I see that as a win.
The simplest method, if not very efficient in many DBMSs, is to use INTERSECT between all columns and all values in a subquery.
SELECT *
FROM MRE_TABLE AS m
WHERE EXISTS (
(
SELECT *
FROM (VALUES
(m.col1),
(m.col2),
(m.col3),
(m.col4) -- etc
) AS v(data)
)
INTERSECT
(
SELECT *
FROM (VALUES
('my-data-val1'),
('my-data-val2'),
('my-data-val3'),
('my-data-val4')
) AS v(data)
)
);
If your data was normalized into a proper unpivoted table then you could maybe join on an input/temp table.
SELECT
m.id
FROM MRE_TABLE AS m
JOIN INPUT AS i
ON i.name = m.name
AND i.value = m.value
GROUP BY
m.id;
3 Comments
kesarling
You still forgot the main goal here: Not having to type out 68 column names. The whole reason why I am doing this is because I do not want to write 68 column names
MatBailie
@kesarling Your question says
I really do not want to write 67 ORs for 4 different data-values. People are always going to answer what you actually wrote, not what you meant.
Charlieface
@kesarling If you use some kind of temp table or table-valued-parameter (or XML etc) then you can send it all from your application side without having to type them.
WHERE 'val' IN (col1, col2, ..., coln)So, which, RDBMS are you using?ORs though, even if you have to write the actual names.