Solved

is there equivalent function mapping of oracle to sybase

Posted on 2001-07-04
4
1,265 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 50 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 50 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Let's recap what we learned from yesterday's Skyport Systems webinar.
Part Two of the two-part Q&A series with MalwareTech.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

635 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