Advertisement
Advertisement


DECODE( ) function in SQL Server


Question

SELECT PC_COMP_CODE,
       'R',
       PC_RESUB_REF,
       DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR'),
       PC_DEPT_NO DEPT,
       '', --PC_DEPT_NO,
       PC_SL_LDGR_CODE + '/' + PC_SL_ACNO,
       SUM(DECODE(PC_SL_LDGR_CODE, '02', 1, -1) * PC_AMOUNT),
       PC_CHEQUE_NO CHQNO
  FROM GLAS_PDC_CHEQUES
 WHERE PC_RESUB_REF IS NOT NULL 
   AND PC_DISCD NOT IN ('d', 'D', 'T') 
GROUP BY PC_RESUB_REF, 
         PC_COMP_CODE, 
         'JJ', 
         PC_SL_LDGR_CODE + '/' + PC_SL_ACNO, 
         PC_DEPT_NO, 
         PC_CHEQUE_NO, 
         DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR')

Above is a Oracle query; how can I use DECODE() function in SQL Server 2005?

2016/12/08
1
32
12/8/2016 3:11:22 PM

Accepted Answer

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

2009/10/13
52
10/13/2009 10:04:28 AM


Just for completeness (because nobody else posted the most obvious answer):

Oracle:

DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR')

MSSQL (2012+):

IIF(PC_SL_LDGR_CODE='02', 'DR', 'CR')

The bad news:

DECODE with more than 4 arguments would result in an ugly IIF cascade

2020/03/17

Create a function in SQL Server as below and replace the DECODE with dbo.DECODE

CREATE FUNCTION DECODE(@CondField as nvarchar(100),@Criteria as nvarchar(100), 
                       @True Value as nvarchar(100), @FalseValue as nvarchar(100))
returns nvarchar(100)
begin
       return case when @CondField = @Criteria then @TrueValue 
                   else @FalseValue end
end
2012/08/07

It's easy to do:

select 
    CASE WHEN 10 > 1 THEN 'Yes'
    ELSE 'No'
END 
2015/05/21

join this "literal table",

select 
    t.c.value('@c', 'varchar(30)') code,
    t.c.value('@v', 'varchar(30)') val
from (select convert(xml, '<x c="CODE001" v="Value One" /><x c="CODE002" v="Value Two" />') aXmlCol) z
cross apply aXmlCol.nodes('/x') t(c)
2015/11/13

when I use the function

select dbo.decode(10>1 ,'yes' ,'no')

then say syntax error near '>'

Unfortunately, that does not get you around having the CASE clause in the SQL, since you would need it to convert the logical expression to a bit parameter to match the type of the first function argument:

create function decode(@var1 as bit, @var2 as nvarchar(100), @var3 as nvarchar(100))
returns nvarchar(100)
begin
return case when @var1 = 1 then @var2 else @var3 end;
end;

select dbo.decode(case when 10 > 1 then 1 else 0 end, 'Yes', 'No');
2019/08/07

Source: https://stackoverflow.com/questions/1559241
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]