Solved

is there equivalent function mapping of oracle to sybase

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now