Compound Expressions

Expressions

Description

A compound expression is a series of simple expressions joined by arithmetic operators. A simple expression used in a compound expression must return a numeric value.

(RedShift SQL Language Reference Compound expressions)

Grammar Syntax

expression operator {expression | (compound_expression)}

Conversion Table

Redshift	Snowflake	Comments
|| (Concatenation)	||	Fully supported by Snowflake

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE concatenation_demo (
    col1 VARCHAR(20),
    col2 INTEGER,
    col3 DATE
);

INSERT INTO concatenation_demo (col1, col2, col3) VALUES
('Hello', 42, '2023-12-01'),
(NULL, 0, '2024-01-01'),
('Redshift', -7, NULL);

SELECT 
    col1 || ' has number ' || col2 AS concat_string_number
FROM concatenation_demo;

SELECT 
    col1 || ' on ' || col3 AS concat_string_date
FROM concatenation_demo;

SELECT
    COALESCE(col1, 'Unknown') || ' with number ' || COALESCE(CAST(col2 AS VARCHAR), 'N/A') AS concat_with_null_handling
FROM concatenation_demo;

Results

concat_string_number
Hello has number 42
<NULL>
Redshift has number -7

concat_string_date
Hello on 2023-12-01
<NULL>
<NULL>

concat_with_null_handling
Hello with number 42
Unknown with number 0
Redshift with number -7

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE concatenation_demo (
    col1 VARCHAR(20),
    col2 INTEGER,
    col3 DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/16/2024",  "domain": "test" }}';

INSERT INTO concatenation_demo (col1, col2, col3) VALUES
('Hello', 42, '2023-12-01'),
(NULL, 0, '2024-01-01'),
('Redshift', -7, NULL);

SELECT
    col1 || ' has number ' || col2 AS concat_string_number
FROM
    concatenation_demo;

SELECT
    col1 || ' on ' || col3 AS concat_string_date
FROM
    concatenation_demo;

SELECT
    COALESCE(col1, 'Unknown') || ' with number ' || COALESCE(CAST(col2 AS VARCHAR), 'N/A') AS concat_with_null_handling
FROM
    concatenation_demo;

Results

concat_string_number
Hello has number 42
<NULL>
Redshift has number -7

concat_string_date
Hello on 2023-12-01
<NULL>
<NULL>

concat_with_null_handling
Hello with number 42
Unknown with number 0
Redshift with number -7

Known Issues

No issues were found.

Related EWIs

There are no known issues.