Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Port test_mutually_exclusive_ranges() to TSQL #18

Open
dataders opened this issue Jan 8, 2021 · 1 comment
Open

Port test_mutually_exclusive_ranges() to TSQL #18

dataders opened this issue Jan 8, 2021 · 1 comment

Comments

@dataders
Copy link
Contributor

dataders commented Jan 8, 2021

my priority for this macro is not the macro itself, but really because it is used in dbt-utils's integration testing for validation of OTHER macros.

calc CTE

these two coalesces are the tricky parts that need to be converted into NULLIFs or CASE WHENs? Also false needs to be replaced with 0 bc TSQL doesn't have a true & false. Here's a gist of the compiled SQL that's error-ing out. If you log into the test db (dbt-msft-serverless-db), you can debug it.

my interpretation for the logic for the first coalesce statement below, though i'm not sure in what scenario lower_bound < upper_bound could be NULL.

  • if lower_bound < upper_bound is not null:
    • return the result of the comparison: true or false (or in TSQL's case: 1 or 0)
  • else if lower_bound < upper_bound is NULL:
    • return false (i.e. 0)

current error

the error i'm getting is Incorrect syntax near '<'. (102)

Database Error in test dbt_utils_mutually_exclusive_ranges_data_test_mutually_exclusive_ranges_no_gaps_not_allowed__lower_bound__upper_bound (models/schema_tests/schema.yml)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102) (SQLExecDirectW)")
  compiled SQL at target/compiled/dbt_utils_integration_tests/models/schema_tests/schema.yml/schema_test/dbt_utils_mutually_exclusive_ranges_data_test_mutually_exclusive_ranges_no_gaps_f27ec0771ee181e29da8b3adeddba768.sql
-- For each record: lower_bound should be < upper_bound.
-- Coalesce it to return an error on the null case (implicit assumption
-- these columns are not_null)
coalesce(
    lower_bound < upper_bound,
    false
) as lower_bound_less_than_upper_bound,

-- For each record: upper_bound {{ allow_gaps_operator }} the next lower_bound.
-- Coalesce it to handle null cases for the last record.
coalesce(
    upper_bound {{ allow_gaps_operator }} next_lower_bound,
    is_last_record,
    false
) as upper_bound_{{ allow_gaps_operator_in_words }}_next_lower_bound

window_functions CTE

this piece I already was able to convert

row_number() over (
    {{ partition_clause }}
    order by {{ lower_bound_column }} desc
) = 1 as is_last_record

becomes

case when
    row_number() over (
        {{ partition_clause }}
        order by {{ lower_bound_column }} desc
    ) = 1
    then 1 else 0
end as is_last_record
@sdebruyn
Copy link
Member

sdebruyn commented Sep 8, 2024

Is this still relevant?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants