Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

is there equivalent function mapping of oracle to sybase

Posted on 2001-07-04
4
Medium Priority
?
1,269 Views
Last Modified: 2008-02-26
Is there any equivalent functions in sybase for the following oracle functions

1. Decode
2. to_char

Sridhar
0
Comment
Question by:sriven3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 200 total points
ID: 6251619
                      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
0
 
LVL 1

Assisted Solution

by:Richard Coffre
Richard Coffre earned 200 total points
ID: 6254517
Hi,

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

SELECT
     d2.description_text AS product_line, d.product_line_id, d1.description_text AS discount,
     d.discount_id,
     CASE
     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'
     END
     AS rate_class,
     r.range_origin, r.range_terminus,

Hope it helps
     CASE
     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)'
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9882525
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ChrisKing
EE Cleanup Volunteer
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question