Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

is there equivalent function mapping of oracle to sybase

Posted on 2001-07-04
4
Medium Priority
?
1,270 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Did you know there are services out there that can turn an Instagram feed into an RSS feed? I found some interesting exclusive Instagram content which I wanted to follow without signing up for yet another social media account. RSS to the rescue!
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month12 days, 11 hours left to enroll

972 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