Standard SQL Alternative to Oracle Decode

Standard SQL alternative to Oracle DECODE

A CASE expression is the ANSI SQL method, of which there are 2 varieties, "simple" and "searched":

1) Simple CASE expression:

CASE col WHEN 1 THEN 'One'
         WHEN 2 THEN 'Two'
         ELSE 'More'
         END

2) Searched CASE expression:

CASE WHEN col < 0 THEN 'Negative'
     WHEN col = 0 THEN 'Zero'
     ELSE 'Positive'
     END

CASE vs. DECODE

As always with Oracle ... AskTom...

From this post...

Decode is somewhat obscure -- CASE is
very very clear. Things that are
easy to do in decode are easy to do in
CASE, things that are hard or near
impossible to do with decode are easy
to do in CASE. CASE, logic wise, wins
hands down.

From a performance point of view seems they are about the same, again above article mentions some speed differences but without benchmarking the particular statements it's hard to say.

DECODE( ) function in SQL Server

You could use the 'CASE .. WHEN .. THEN .. ELSE .. END' syntax in SQL.

How to implement generic Oracle DECODE function in BigQuery?

Below is for BigQuery Standard SQL

CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));  

You can see how it works using below example

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
)
SELECT product_id, warehouse_id,
  DECODE(warehouse_id, 
    [STRUCT<search INT64, result STRING>
      (1,'Southlake'),
      (2,'San Francisco'),
      (3,'New Jersey'),
      (4,'Seattle')
    ], 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`

with result

Row product_id  warehouse_id    Location_of_inventory    
1   1           4               Seattle  
2   2           2               San Francisco    
3   3           5               Non domestic       

Another example of use is:

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
), map AS (
  SELECT 1 search, 'Southlake' result UNION ALL                               
  SELECT 2, 'San Francisco' UNION ALL                               
  SELECT 3, 'New Jersey' UNION ALL                               
  SELECT 4, 'Seattle'                           
)

SELECT product_id, warehouse_id,
  DECODE(warehouse_id, kv, 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`,
(SELECT ARRAY_AGG(STRUCT(search, result)) AS kv FROM map) arr

with the same output

Update to address - "for a reusable UDF, not having to name the fields makes it closer to Oracle's implementation."

CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS (
  IFNULL((
    SELECT result FROM (
      SELECT NULL AS search, NULL AS result UNION ALL SELECT * FROM UNNEST(map) 
    )
    WHERE search = expr
  ), `default`)
);

So now - previous examples can be used w/o explicit naming fields as in example below

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS (
  IFNULL((
    SELECT result FROM (
      SELECT NULL AS search, NULL AS result UNION ALL SELECT * FROM UNNEST(map) 
    )
    WHERE search = expr
  ), `default`)
);
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
)
SELECT product_id, warehouse_id,
  DECODE(warehouse_id, 
    [ (1,'Southlake'),
      (2,'San Francisco'),
      (3,'New Jersey'),
      (4,'Seattle')
    ], 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`

still with same output as before

Decode equivalent in postgres

There is an equivalent. It's called a CASE statement.

There are two forms of CASE:

Simple CASE:

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Searched CASE:

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE statements are easier to read; I prefer these over decode() in Oracle.

Oracle decode equivalent function to Postgres

You could use a standard case expression:

order by case pts.process_status
    when 'PTSPS_INPROGRESS' then 1
    when 'PTSPS_SUBMITTED'  then 2
    when 'PTSPS_DRAFT'      then 3
    when 'PTSPS_REJECTED'   then 4
    when 'PTSPS_APPROVED'   then 5
end

Oracle SQL: Alternative to nested REPLACE() in Select statement?

A CASE expression would work for your example:

case ToiletType
   when 'A' then 'Automatic Standard'
   when 'P' then 'Portable Potty'
   end

i.e.

SELECT IncidentNumber, 
       case ToiletType
          when 'A' then 'Automatic Standard'
          when 'P' then 'Portable Potty'
       end as ToiletType,
       ToiletDangers,
       IncidentDate
FROM   Core.LostLawsuits

Perhaps better though would be to join to a ToiletTypes table?

---

Related Topics