• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1271
  • Last Modified:

is there equivalent function mapping of oracle to sybase

Is there any equivalent functions in sybase for the following oracle functions

1. Decode
2. to_char

2 Solutions
                      To answer Q2, CONVERT(CHAR(20),column_name)
                       To answer Q1, something from from Sybase FAQ.

                       6.2.1: How to emulate the Oracle decode function/crosstab


                       There is a neat way to use boolean logic to perform cross-tab or rotation queries easily, and very efficeintly.
                       Using the aggregate 'Group By' clause in a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX()
                       functions, you can create queries and views to perform all kinds of summarizations.

                       This technique does not produce easily understood SQL statements.

                       If you want to test a field to see if it is equal to a value, say 100, use the following code:

                       SELECT (1- ABS( SIGN( ISNULL( 100 - <field>, 1))))
                       The innermost function will return 1 when the field is null, a positive value if the field < 100, a
                       negative value if the field is > 100 and will return 0 if the field = 100. This example is for Sybase
                       or Microsoft SQL server, but other servers should support most of these functions or the COALESCE()
                       function, which is the ANSI equivalent to Isnull.

                       The SIGN() function returns zero for a zero value, -1 for a negative value, 1 for a positive value The
                       ABS() function returns zero for a zero value, and 1 for any non-zero value.

                       Put it all together and you get '0' if the value match, and '1' if they don't. This is not that useful,
                       so we subtract this return value from '1' to invert it, giving us a TRUE value of '1' and a false value
                       of '0'. These return values can then be multiplied by the value of another column, or used within the
                       parameters of another function like SUBSTRING() to return a conditional text value.

                       For example, to create a grid from a student registration table containing STUDENT_ID and COURSE_ID
                       columns, where there are 5 courses (101, 105, 201, 210, 300) use the following query:

                       SELECT STUDENT_ID,
                             (1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101,
                             (1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105,
                             (1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201,
                             (1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210,
                             (1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300
                       GROUP BY STUDENT_ID
                       ORDER BY STUDENT_ID
Richard Coffree-commerce Product ManagerCommented:

I think that you may use the instruction CASE ... WHEN ... END as described below.

     d2.description_text AS product_line, d.product_line_id, d1.description_text AS discount,
     WHEN r.rate_class IS NULL THEN 'Non defini'
     WHEN r.rate_class = 1 THEN 'Standard'
       WHEN r.rate_class = 2 THEN 'Plan tarifaire v3.0'
     WHEN r.rate_class = 3 THEN 'v4 RENEGO'
       WHEN r.rate_class = 6 THEN 'Plan tarifaire v4.0'
       WHEN r.rate_class = 10 THEN 'Plan tarifaire v5.0'
     ELSE CONVERT(VARCHAR(3), r.rate_class) + ' : nouvelle rate_class'
     AS rate_class,
     r.range_origin, r.range_terminus,

Hope it helps
     WHEN r.billing_frequency = 3 THEN 'Mensuel'
     WHEN r.billing_frequency = 6 THEN 'Annuel'
     ELSE CONVERT(VARCHAR(3), r.billing_frequency) + ' : nouvelle periodicite'
     END AS billing_frequency,
     r.discount_percent, r.discount_amount, CONVERT(VARCHAR(10), r.date_active, 103) AS 'date_active (jj/mm/aaaa)'
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: amitpagarwal & pyxos

Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now