How to convert decode to pg in oracle

For the Oracle DECODE function in PostgreSQL, you can use a CASE expression or a union. The CASE expression returns the first matching result based on the condition. The syntax is: CASE WHEN condition THEN result ELSE result END. A union combines multiple SELECT statements using the UNION ALL operator, returning a table containing the result columns that satisfy each condition.

The replacement of the Oracle DECODE function in PostgreSQL

The Oracle DECODE function is used to return based on conditional judgment Functions with different values. There is no exact equivalent of the DECODE function in PostgreSQL, but you can use CASE expressions or unions to achieve similar functionality.

CASE expression

CASE The syntax of the expression is as follows:

<code>CASE
    WHEN условие1 THEN результат1
    WHEN условие2 THEN результат2
    ...
    ELSE результат ELSE
END</code>

It judges based on the conditions in sequence and returns with the first result that satisfies the condition. If no conditions are met, the ELSE result is returned.

Join

Join is a technique that uses multiple SELECT statements to retrieve data from multiple tables. It can also be used to implement the functionality of the DECODE function by combining multiple SELECT statements using the UNION ALL operator:

<code>SELECT CASE WHEN условие1 THEN résultat1 ELSE NULL END AS результат_1
UNION ALL
SELECT CASE WHEN условие2 THEN résultat2 ELSE NULL END AS результат_2
...</code>

The above query will return a table in which each row contains a Result columns that meet the conditions.

Example

Suppose we have a table people with the following columns:

• name - Person Name
• gender - Person Gender

We want to create a query to get a person's honorific based on their gender:

Oracle DECODE function:

<code>SELECT name, DECODE(gender, 'M', 'Mr.', 'Ms.') AS title
FROM people;</code>

PostgreSQL CASE expression:

<code>SELECT name, 
    CASE
        WHEN gender = 'M' THEN 'Mr.'
        WHEN gender = 'F' THEN 'Ms.'
        ELSE NULL
    END AS title
FROM people;</code>

PostgreSQL union:

<code>SELECT name, 
    CASE WHEN gender = 'M' THEN 'Mr.' ELSE NULL END AS title
UNION ALL
SELECT name, 
    CASE WHEN gender = 'F' THEN 'Ms.' ELSE NULL END AS title
FROM people;</code>

All three queries will produce the same results as follows:

name	title
John	Mr.
Mary	Ms.

The above is the detailed content of How to convert decode to pg in oracle. For more information, please follow other related articles on the PHP Chinese website!