We’re rewarding the question askers & reputations are being recalculated! Read more.
4

Snowflake currently supports recursive CTEs. I just build an sql to parse an account tree to consolidate amounts at the branch level. From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination with main as ( select a.scode as src , b.scode as tgt from accttree a join accttree b on (a....


2

The error message defines the problem: you can't use a window function directly in an assignment (SET). You can use a CTE to pull out the aggregate and then apply the update to the CTE (which pushes it back to the table). ;WITH t AS ( SELECT key1, date1, date2, date1max = MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) FROM #table ) UPDATE t ...


1

You need in something like SELECT *, CASE WHEN COUNT(CASE WHEN sts = 'Enabled' THEN 1 END) OVER (PARTITION BY acctid) > 0 THEN 'Active' WHEN COUNT(CASE WHEN sts IS NOT NULL THEN 1 END) OVER (PARTITION BY acctid) > 0 THEN 'Cancelled' ELSE 'Unknown' END Status FROM usr fiddle


1

According to your text if any of them are true is must be true, otherwise is false: select key, case when sum(case when B is true then 1 else 0 end) > 0 then true else false end as result from t group by key; db<>fiddle here


1

Alternatively, UPDATE t SET t.date2 = t1.date1 FROM #table t OUTER APPLY ( SELECT MAX(date1) date1 FROM #table t1 WHERE t1.key1 = t.key1 AND t1.date1 > t.date1 ) t1; OR UPDATE t SET t.date2 = t1.date1 FROM #table t OUTER APPLY ( SELECT TOP 1 date1 FROM #table t1 WHERE t1.key1 = t.key1 ...


1

Yes, there are a few: Matillion https://snowflake-support.matillion.com/s/article/2834120 Stichdata you have to reach out to sales https://www.stitchdata.com/contact/ Alooma looks like it may limit enterprise versions https://www.alooma.com/docs/connect-your-data/common-integration-settings/advanced-connectivity AWS Direct Connect and AWSPrivatelink https://...


1

My short answer is: Use truncate if you do not see any errors (because truncate cannot be used in many scenarios, such as the table is referenced or the table is in replication, but you will get error in such cases) The advantages of truncate (over delete esp. in your repeated deletion case) are: fast as it does not involve big overhead of transaction ...


1

It looks like you need to apply a function to parse the array. As of 9/13/2018 functions are not allowed in value lists see Is there a literal syntax for arrays One way around this is to combine INSERT INTO and SELECT statement. Example: CREATE OR REPLACE TABLE array_test_table ( id number, ids array ); INSERT INTO array_test_table (id, ids) SELECT $...


Only top voted, non community-wiki answers of a minimum length are eligible