4 ms·

What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or

What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?

dventimi 4y ago
If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.
- alphanumeric0 4y ago
  That's a good point. This would rule out SQLite for me.
- mr_toad 4y ago
  Dynamic SQL isn’t SQL, and it’s not relational. It’s no different from using a language like Python to generate SQL queries.
  - dventimi 4y ago
    It's a little different. Anyway, this is under the constraint "no stored procedures."
adeelk93 4y ago
Check out dbt - it's a great tool for organizing queries and solving such patterns
chrstr 4y ago
CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value); CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value); CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b); CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data); SELECT * FROM complicated_query WHERE test_case = 'a'; SELECT * FROM complicated_query WHERE test_case = 'b';
- alphanumeric0 4y ago
  Nice, that is what I was looking for. Of course, it'd need to point to production data as well, so maybe test_case is null, in that case: CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value); CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value); CREATE TABLE data_prod AS (SELECT NULL AS test_case, prod_table.value FROM prod_table); CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b UNION ALL SELECT * FROM data_prod); CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data); -- when testing SELECT * FROM complicated_query WHERE test_case = 'a'; SELECT * FROM complicated_query WHERE test_case = 'b'; -- when in 'production' SELECT * FROM complicated_query WHERE test_case IS NULL;
- naasking 4y ago
  You just reinvented defunctionalization, which is a transformation from a domain that has first-class values to a domain where support is only second-class. Defunctionalization is typically used in programming languages to simulate first-class functions in languages where functions are only second-class citizens, like C and Pascal. This perfectly illustrates my point. You had to manually defunctionalize your data model and queries to support what I'm saying should be inherently part of SQL.
  - CRConrad 4y ago
    > languages where functions are only second-class citizens, like C and Pascal. 1) Only if you define Pascal as only Wirth's very first version. That changed almost immediately. 2) Only if you refuse to equate “pointer to function” with “function”. Which in C, where “everything is a pointer” (a bit like in Unix Linux “everything is a file”), seems rather silly.
Foobar8568 4y ago
TVF ( Table-Valued Function) with Cross apply.

dventimi 4y ago

If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.

alphanumeric0 4y ago
That's a good point. This would rule out SQLite for me.
mr_toad 4y ago
Dynamic SQL isn’t SQL, and it’s not relational. It’s no different from using a language like Python to generate SQL queries.
- dventimi 4y ago
  It's a little different. Anyway, this is under the constraint "no stored procedures."

adeelk93 4y ago

Check out dbt - it's a great tool for organizing queries and solving such patterns

chrstr 4y ago

CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value); CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value); CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b); CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data); SELECT * FROM complicated_query WHERE test_case = 'a'; SELECT * FROM complicated_query WHERE test_case = 'b';

alphanumeric0 4y ago
Nice, that is what I was looking for. Of course, it'd need to point to production data as well, so maybe test_case is null, in that case: CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value); CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value); CREATE TABLE data_prod AS (SELECT NULL AS test_case, prod_table.value FROM prod_table); CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b UNION ALL SELECT * FROM data_prod); CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data); -- when testing SELECT * FROM complicated_query WHERE test_case = 'a'; SELECT * FROM complicated_query WHERE test_case = 'b'; -- when in 'production' SELECT * FROM complicated_query WHERE test_case IS NULL;
naasking 4y ago
You just reinvented defunctionalization, which is a transformation from a domain that has first-class values to a domain where support is only second-class. Defunctionalization is typically used in programming languages to simulate first-class functions in languages where functions are only second-class citizens, like C and Pascal. This perfectly illustrates my point. You had to manually defunctionalize your data model and queries to support what I'm saying should be inherently part of SQL.
- CRConrad 4y ago
  > languages where functions are only second-class citizens, like C and Pascal. 1) Only if you define Pascal as only Wirth's very first version. That changed almost immediately. 2) Only if you refuse to equate “pointer to function” with “function”. Which in C, where “everything is a pointer” (a bit like in Unix Linux “everything is a file”), seems rather silly.

Foobar8568 4y ago

TVF ( Table-Valued Function) with Cross apply.