When using parameter for join clause in snowflake, getting wrong result. why?

user_11993995 0
Viewed 135

I'm using dbeaver with connection to snowflake database. I want to select data with join clause. but I need to do it with parameters. my code is:

select count(*) from my_table as a ${join}

var join = 'LEFT JOIN table_b AS b ON a.ID = b.ID AND b.NAME = a.NAME'

when I run the select statement (in dbeaver), I get pop up asking me to fill ${join} value, I put the value in the textbox and the command runs. I get WRONG result! (1,254,242)

But when run the following command:

select count(*) from my_table as a LEFT JOIN table_b AS b ON a.ID = b.ID AND b.NAME = a.NAME

I get correct result (900,254)

anybody can help please? thank you.

dbeaver snowflake-cloud-data-platform join sql
1 Answers

General approach to troubleshoot such scenarios:

  1. Check if you are connecting to the same DB/schema from client tool/WebUI

     SELECT current_database(), current_schema();

  2. Check if you are using the same user(there may be access issues or Row Level Security applied that could affect number of rows)

    SELECT current_user(), current_role();

  3. Check the exact query text sent by client tool in Snowflake History tab and compare against the one run manually.

user_50708790
Related