MySQL Equivalent of Decode Function in Oracle

Decoding fields in Mysql

The MySQL DECODE() function is used for decryption, its signature is:

 DECODE(crypt_str,pass_str)

See the documentation. If you want something equivalent to Oracle's DECODE() function, see:

MySQL equivalent of DECODE function in Oracle

Your query can be rewritten as:

SELECT IF(txn_type = 'Expense', -txn_amount, txn_amount) AS net_amount
WHERE id > 0

or:

SELECT CASE txn_type
            WHEN 'Expense' THEN -txn_amount
            ELSE txn_amount
       END AS net_amount

Are a CASE statement and a DECODE equivalent?

Ben has written a lengthy answer on the differences between DECODE and CASE. He demonstrates that DECODE and CASE may return different datatypes for apparently the same set of values without properly explaining why this happens.

DECODE() is quite prescriptive: it is always the datatype of the first result parameter. Oracle applies implicit conversion to all the other result parameters. It will throw an error , if (say) the first result parameter is numeric and the default value is a date.

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

This is described in the documentation: find out more.

In the first scenario the first result parameter is NULL, which Oracle decides to treat as VARCHAR2. If we change it so that the first result parameter is numeric and the default value is null the DECODE() statement will return a NUMBER; a DUMP() proves that this is so.

Whereas CASE insists that all the returned values have the same datatype, and will throw a compilation error if this is not the case. It won't apply implicit conversion. This is also covered in the documentation. Read it here.

The difference boils down to this. The following DECODE statement will run, the CASE statement won't:

select decode(1, 1, 1, '1') from dual;

select case 1 when 1 then 1 else '1' end from dual;

Obligatory SQL Fiddle.

DECODE Function in SQL

Try properly escaping the inner single quotes

INSERT INTO ABCTABLE (COLUMN1) 
VALUES ('**DECODE**(MDSE_CD,NULL,''0000000000000000'',**LPAD**(TO_NUMBER(MDSE_CD,''16'','' ''))');

How to convert decode function in oracle to standard big query

As @heregoes suggested that case when is available with big query, you can use the following query.

SELECT
    TABLE1.BIC_ZC2ISBN10,
    SUM(CASE
        WHEN TABLE1.BIC_ZC2DCINDC = 'H' THEN CASE
            WHEN TABLE1.BIC_ZC2MOVTYP IN(
                'Z03', 'Z35', 'Z64', 'Z77'
            ) THEN TABLE1.BIC_ZK2CLABS2
            ELSE 0
        END
        ELSE 0
    END - CASE
        WHEN TABLE1.BIC_ZC2DCINDC = 'S'  THEN CASE
            WHEN BOADMIN.BIC_GM_AP2OINVS300_BO_VW.BIC_ZC2MOVTYP IN (
                'Z04',
                'Z36',
                'Z65',
                'Z78'
            ) THEN TABLE1.BIC_ZK2CLABS2
            ELSE 0
        END
        ELSE 0
    END
)
FROM TABLE1 
GROUP BY TABLE1.BIC_ZC2ISBN10;

Cheers!!

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

Does Oracle have any equivalent function for mysql FIELD

Here is a stupid solution: (:-D)

SELECT * FROM mytable WHERE id IN (1,2,3,4)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 2 THEN 2
                 WHEN 1 THEN 3
                 WHEN 4 THEN 4 END;

Or use DECODE function:

SELECT * FROM mytable WHERE id IN (1,2,3,4)
ORDER BY DECODE(id, 3, 1, 2, 2, 1, 3, 4, 4, 0)

---

Related Topics