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…

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…

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…

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…

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…

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

Teradata Extract Function Syntax and Example

The extract function in Teradata extracts date or time part of the date, time, timestamp, or interval fields. The extract function retrieves the sub part such as day, month, year, etc. This extract function is equivalent to date_part() function in other relational databases. In this article, we will check how to use an extract function to get specified subpart. The extract function also converts the extracted value exact numeric format. The output is always a numeric value. Teradata Extract Function In a data warehouse, you will be working with many…

Comments Off on Teradata Extract Function Syntax and Example
October 22, 2020
GCP BigQuery

Google BigQuery Recursive CTE: Native Query and Alternative Example

If you need to query hierarchical data like an organizational chart, employee-manager relationships, or a bill of materials a recursive query is the standard approach. While developers previously had to rely on scripting workarounds, Google BigQuery now fully supports native recursive CTEs. In this guide, we will look at how to write a native BigQuery recursive query, as well as a legacy alternative using a WHILE loop. Page Content Introduction Native BigQuery Recursive CTE (WITH RECURSIVE) BigQuery Recursive Query Alternative Using WHILE Loop Conclusion Introduction Identifying hierarchies of data used…

Comments Off on Google BigQuery Recursive CTE: Native Query and Alternative Example
October 16, 2020
Redshift

Redshift TO_NUMBER Function and Different Arguments

There are many situations where you need to pre-process the received data in order to store it in the data warehouse. For instance, you many receive currency data with currency symbol. You have pre-process currency column to remove the dollar ($) symbol and store clean value in the database. Amazon Redshift support TO_NUMBER function, you can use it to remove unwanted characters from numeric value. In this article, we will check how to use TO_NUMBER type conversion function with its different format. We will also check different examples to use…

Comments Off on Redshift TO_NUMBER Function and Different Arguments
September 21, 2020
Redshift

Redshift Trunc and Date_Trunc Functions, Examples and Differences

Amazon Redshift supports many built-in functions that you can use to perform day to day activities such as extracting only date part from a timestamp value. Redshift provides many date and string functions that you can use to play around date type. In this article, we will check how to use an Amazon Redshift trunc and date_trunc functions, syntax, usage, examples and difference between these two functions. Redshift Trunc and Date_Trunc Functions You can use the trunc and date_trunc functions to trunc certain portions of the date type. However, you…

Comments Off on Redshift Trunc and Date_Trunc Functions, Examples and Differences
September 15, 2020
Snowflake

How to Remove Newline Characters from String in Snowflake?

If you store a string as a variable or a column in relational databases such SQL Server , then it can contain line breaks or newline (\n) characters. But, these newline characters have to be removed in pre-processing steps. In this article, we will check how to remove newline characters from a string or text in Snowflake. Remove Newline Characters from String in Snowflake Removing newline (\n), carriage return (\r) or any special characters is the common pre-processing step before storing records in any relational databases. Many databases supports built-in…

Comments Off on How to Remove Newline Characters from String in Snowflake?
September 10, 2020