Vithal S, Author at DWgeek.com

SQL INITCAP Function Alternative in Azure Synapse and TSQL

The INITCAP function is one of the quite useful functions you can use to capitalize the first letter (CamelCase) of each word. The popular relational databases such as Oracle, Teradata, etc. support INITCAP function to represent strings in a CamelCase. But, same function will not work on the Microsoft SQL Server or Azure Synapse analytics data warehouse. Both SQL Server and Azure Synapse databases are based on TSQL. In this article, we will check what are the INITCAP function alternative in Azure Synapse and TSQL. INITCAP Function Alternative in Azure…

Comments Off

February 10, 2021

Azure Synapse

SQL LPAD and RPAD Functions Alternative in Synapse and TSQL

Many relational databases such as Oracle, Teradata supports LPAD and RPAD functions. These functions are quite useful when you are formatting data before storing it in a database or displaying data on reports. For example, add zero to integer column. But, Azure Synapse data warehouse which is based on the Microsoft SQL Server TSQL does not support LPAD and RPAD functions. You have to use TSQL built-in functions or create your own user defined functions (UDF). In this article, we will check what are the LPAD and RPAD Functions Alternative…

Comments Off

February 9, 2021

Azure Synapse

SQL GREATEST and LEAST Functions Alternative in Synapse and TSQL

Many relational databases such as Oracle, Teradata supports GREATEST and LEAST functions. These functions are quite useful when you are comparing data from two or more columns. But, Azure Synapse data warehouse which is based on the Microsoft SQL Server TSQL does not support GREATEST and LEAST functions. You either have to use a case statement or write your own user defined functions (UDFs). In this article, we will check what are the GREATEST and LEAST Functions Alternative in Synapse and TSQL. GREATEST and LEAST Functions Alternative in Synapse and…

Comments Off

February 4, 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

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

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

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

November 10, 2020

GCP BigQuery

What is SQL Cursor Alternative in BigQuery?

SQL Cursor is a database object to retrieve data from a result set one row at a time. The cursor are useful in many scenarios such as updating records using loops, inserting only required records, etc. You can think cursor as a temporary workspace provisioned in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is SQL cursor alternative in Google BigQuery with an example. Cursors…

Comments Off

October 28, 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

October 22, 2020

GCP BigQuery

BigQuery Recursive Query Alternative – Example

You can use recursive query to identify the hierarchies of data, such as an organizational structure, employee-manager, bill-of-materials, and document hierarchy. Many relational databases such as Teradata, Oracle, etc. supports recursive queries, but Google BigQuery does not support all features. One of such features is Recursive CTE or VIEWS. Google BigQuery does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Google BigQuery Recursive Query Alternative with an working example. BigQuery Recursive Query Alternative As mentioned earlier,…

Comments Off

October 16, 2020