Redshift

SQL GROUP BY with CUBE Function Alternative in Redshift

In my other articles, we have discussed GROUP BY with GROUPING SETS and GROUP BY ROLLUP alternatives. In this article, we will check the SQL GROUP BY CUBE function alternative in Amazon Redshift. The relational databases such as Oracle, Teradata, etc. support GROUP BY with CUBE function to group the result rows. However, Amazon Redshift does not support GROUP BY with CUBE function. SQL GROUP BY with CUBE Function Alternative in Redshift The GROUP functions such as ROLLUP, CUBE, GROUPING SETS are an extension of the GROUP BY clause. The CUBE allows you to generate subtotals like…

Continue ReadingSQL GROUP BY with CUBE Function Alternative in Redshift
Comments Off on SQL GROUP BY with CUBE Function Alternative in Redshift
April 5, 2021
Redshift

Amazon Redshift CONCAT Function-Syntax and Examples

The data warehouse is built from many heterogeneous data sources. It is a common requirement to combine data from multiple columns or two strings before loading them to target table. For example, you may get requirement to combine state and city columns before loading data to the customer table. In this article, we will check Amazon AWS Redshift CONCAT function, its Syntax and examples. We will also check how to combine two or more columns using Redshift CONCAT operator (||). Amazon Redshift provides multiple methods to concatenate the strings The…

Continue ReadingAmazon Redshift CONCAT Function-Syntax and Examples
Comments Off on Amazon Redshift CONCAT Function-Syntax and Examples
March 16, 2021
Redshift

Redshift NULL Handling Functions-Usage and Examples

A NULL value in a relational database is a special marker used in SQL to indicate that a data value does not exist in the database. In other words, it is just a placeholder to denote values that are missing or that we do not know. Almost all relational databases support functions to handle nulls. In this article, we will check Redshift NULL handling functions, usage and some examples Redshift NULL Handling Functions A NULL functions are used to handle NULL values that you may receive as a part of the data. For example, you can use…

Continue ReadingRedshift NULL Handling Functions-Usage and Examples
Comments Off on Redshift NULL Handling Functions-Usage and Examples
February 23, 2021
Redshift

Redshift Comparison Operators – ALL, SOME, ANY Alternative

The relational database such as Teradata support SQL comparison operators, including ALL, ANY and SOME. Some databases refer ALL, ANY and SOME as a SQL logical operator. A logical operators is an operator applied to the result of a predicate to determine the result of a search condition. Amazon Redshift does not support ALL, ANY and SOME logical operators. In this article, we will check what are ALL, SOME, ANY comparison operators alternatives in Redshift. SQL Logical Operators ALL, SOME and ANY Before jumping into details about alternative approach, let…

Continue ReadingRedshift Comparison Operators – ALL, SOME, ANY Alternative
Comments Off on Redshift Comparison Operators – ALL, SOME, ANY Alternative
February 17, 2021
Redshift

UnderStanding QUALIFY Clause in Redshift

If you’re familiar with Teradata, you might have used the QUALIFY clause to filter the results of ordered analytical functions. Amazon Redshift also started supporting QUALIFY clause. In this blog post, we’ll explore what the QUALIFY clause does, why it’s useful, and how you can achieve similar functionality in Redshift. Page Content Introduction What is the QUALIFY Clause? How QUALIFY Clause is used in Redshift? Alternatives to the QUALIFY Clause Conclusion Introduction The QUALIFY is one of the important clause as it helps filter results using window functions, similar to how the HAVING clause…

Continue ReadingUnderStanding QUALIFY Clause in Redshift
Comments Off on UnderStanding QUALIFY Clause in Redshift
February 11, 2021
Redshift

What are INSTR Alternative Functions in Redshift?

The INSTR function in relational databases such as Oracle, Teradata, etc. is used to search a string for a substring and find the location of the substring in the string. The Amazon Redshift does not support the INSTR function. However, there are few string functions that you can use. In this article, we will check what are INSTR alternative functions in Redshift. SQL INSTR String Function The SQL INSTR function returns the location of a substring in a string. Optionally, you can provide the starting position and occurrence of the…

Continue ReadingWhat are INSTR Alternative Functions in Redshift?
Comments Off on What are INSTR Alternative Functions in Redshift?
February 11, 2021
Azure Synapse

SQL GROUPING SETS Alternative in Synapse

The GROUPING SETS option in SQL gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of columns by which you group. Azure Synapse SQL data warehouse does not support GROUPING SETS. In this article, we will check this SQL GROUPING SETS alternative in Azure Synapse SQL data warehouse with an example. SQL GROUPING SETS Alternative in Synapse As mentioned, GROUPING SETS will combine multiple GROUP BY clauses into one GROUP BY clause. The results are the…

Continue ReadingSQL GROUPING SETS Alternative in Synapse
Comments Off on SQL GROUPING SETS Alternative in Synapse
January 28, 2021
General

Commonly used Teradata BTEQ commands and Examples

The BTEQ stands for Basic Teradata Query is one of the commonly used tools to interact with Teradata server. BTEQ tool was the original way that SQL was submitted to Teradata as a means of getting an answer in a desired format. In this article, we will check commonly used Teradata BTEQ commands with some examples. Commonly used Teradata BTEQ commands Following BTEQ commands are commonly used when writing scripts. BTEQ CommandDescription=Repeats the previous Teradata SQL request a specified number of times.AUTOKEYRETRIEVEThis control enables users to specify whether the database…

Continue ReadingCommonly used Teradata BTEQ commands and Examples
Comments Off on Commonly used Teradata BTEQ commands and Examples
December 24, 2020
Redshift

How to Create an Index in Amazon Redshift Table?

Indexing is a way to optimize the databases. It will reduce the disk I/O when you execute a query to search particular value. Basically, indexing is a data structure technique which you can use to quickly locate and access the data in a database. Many traditional relational databases such as Oracle, SQL Server, etc support indexes. But, being a columnar database, Amazon Redshift does not support indexing on the tables. In this article, we will check alternate approach to create an index in the Amazon Redshift table. How to Create…

Continue ReadingHow to Create an Index in Amazon Redshift Table?
Comments Off on How to Create an Index in Amazon Redshift Table?
November 25, 2020
Snowflake

Snowflake Unsupported subquery Issue and How to resolve it

Snowflake provides rich support of subqueries. But, some scalar subqueries that are available in the relational databases such as Oracle are not supported in Snowflake yet. We have to identify the alternate methods for such a subqueries. In this article, we will check Snowflake unsupported subquery issue and how to resolve it? Snowflake Unsupported subquery Issue SQL compilation error: Unsupported subquery type cannot be evaluated You are here because you might have got above error message during SQL migration from databases such as Oracle. For example, consider following Oracle query…

Continue ReadingSnowflake Unsupported subquery Issue and How to resolve it
Comments Off on Snowflake Unsupported subquery Issue and How to resolve it
November 10, 2020