please someone explain what exactly last line is doing

Question

SELECT A.siebel_row_id AS ASSET_ROW_ID,A.SIEBEL_STATUS ,B.STATUS AS THINDB_STATUS
FROM OR_ASSET_THINDB A ,THINDBUSER.MSI B
WHERE A.msisdn=B.msisdn`enter code here`
and b.circle='Orissa'
and DECODE(a.siebel_status,'Suspended','Active','Active','Active','Inactive','Inactive')<>b.STATUS;

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Apr 20 '18 at 05:02
Have a look at the [DECODE](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/DECODE.html#GUID-39341D91-3442-4730-BD34-D3CF5D4701CE) function and try to rewrite it using a [CASE expression](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CASE-Expressions.html#GUID-CA29B333-572B-4E1D-BA64-851FABDBAE96). This will make things clearer. For you and others who read that code. — Philipp Salvisberg, Apr 20 '18 at 05:12

Ajay Venkata Raju · Answer 1 · 2018-09-21T05:37:39.593

I am not sure about this queries requirement but here is my understanding

DECODE(siebel_status,'Suspended','Active','Active','Active','Inactive','Inactive')
this will function like following:

case  
when siebel_status = 'Suspended' THEN 'Active'  
when siebel_status = 'Active' THEN 'Active'  
when siebel_status = 'Inactive' THEN 'Inactive'
end;

the result returned from the above would be compared to:
b.STATUS;

so the final answer could be like this:
if('Active','Active','Inactive')<>b.STATUS

score 1 · Answer 2 · answered Apr 20 '18 at 05:12

and DECODE(a.siebel_status, 
           'Suspended', 'Active',
           'Active'   ,'Active' ,
           'Inactive' ,'Inactive'
          ) <> b.STATUS;

It says:

if siebel_status is suspended, pretend it is active
if siebel_status is active, well - it is active anyway
if siebel_status is inactive, then it remains inactive

Then compare it to b.status value.

please someone explain what exactly last line is doing

2 Answers2