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

User · Answer

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

User · Answer

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

User · Answer

when I use the function   select dbo decode 10 gt 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  gt  1 then 1 else 0 end   Yes    No

User · Answer

join this  literal table    select      t c value   c    varchar 30    code      t c value   v    varchar 30    val from  select convert xml    lt x c  CODE001  v  Value One    gt  lt x c  CODE002  v  Value Two    gt    aXmlCol  z cross apply aXmlCol nodes   x   t c

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It s easy to do   select      CASE WHEN 10  gt  1 THEN  Yes      ELSE  No  END

[sql] DECODE( ) function in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to oracle