[sql] DECODE( ) function in SQL Server

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?

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');

If the value on which the selection depends is an integer, you can use the CHOOSE function:

CHOOSE funtion in TSQL documentation

CHOOSE ( index, val_1, val_2 [, val_n ] )  

Citing the documentation:

index

Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

val_1 ... val_n

List of comma separated values of any data type.

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

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)

In my Case I used it in a lot of places first example if you have 2 values for select statement like gender (Male or Female) then use the following statement:

SELECT CASE Gender WHEN 'Male' THEN 1 ELSE 2 END AS Gender

If there is more than one condition like nationalities you can use it as the following statement:

SELECT CASE Nationality 
WHEN 'AMERICAN'   THEN 1 
WHEN 'BRITISH'   THEN 2
WHEN 'GERMAN'    THEN 3 
WHEN 'EGYPT'     THEN 4 
WHEN 'PALESTINE' THEN 5 
ELSE 6 END AS Nationality 

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

It's easy to do:

select 
    CASE WHEN 10 > 1 THEN 'Yes'
    ELSE 'No'
END 

If I understand the question correctly, you want the equivalent of decode but in T-SQL

Select YourFieldAliasName =
CASE PC_SL_LDGR_CODE
    WHEN '02' THEN 'DR'
    ELSE 'CR'
END