If . . . Then . . . Else in Sybase

building semantic layer
1

I’m creating a universe to go against our Remedy database (Sybase). I need an object that will convert the numeric values of the Status field in the database to the character values displayed in that field in Remedy. Everything I try gets a syntax error. Does anyone know the correct syntax for “if . . . then . . . else” in Sybase?

Michael Welter
Sr. Technical Analyst
Verizon Wireless

Listserv Archives (BOB member since 2002-06-25)

2

Michael

From past experiences against Sybase, you can achieve this by using a Case
statement

I can’t remeber the syntax but I think it is something like this

Case TABLENAME.COLUMNNAME
0 = no
1 = yes
2 = not sure
end

Have a look in you Sybase manuals for case and you will get the correct syntax.

Regards
Tarlock

Date: Wed, 30 Aug 2000 14:46:57 -0700

I’m creating a universe to go against our Remedy database (Sybase). I need an
object that will convert the numeric values of the Status field in the database
to the character values displayed in that field in Remedy. Everything I try gets
a syntax error. Does anyone know the correct syntax for “if . . . then . . .
else” in Sybase?

Michael Welter
Sr. Technical Analyst
Verizon Wireless

Web archives: listserv.aol.com/archives/busob-l.html To change service: Mail to listserv@listserv.aol.com with text in body of note

  • To Unsubscribe: ‘unsubscribe BUSOB-L’ - Vacation Options: ‘set BUSOB-L nomail’ ‘set BUSOB-L mail’

_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at http://profiles.msn.com.

Listserv Archives (BOB member since 2002-06-25)

3

Tarlock,

Yeah, I thought of that earlier, but Case isn’t listed in my Sybase manuals (I don’t know why). I just tried the syntax that you suggested and that didn’t work either. Any other ideas? Anyone?

Michael Welter
Sr. Technical Analyst
Verizon Wireless

From past experiences against Sybase, you can achieve this by using a Case
statement

I can’t remeber the syntax but I think it is something like this

Case TABLENAME.COLUMNNAME
0 = no
1 = yes
2 = not sure
end

Have a look in you Sybase manuals for case and you will get the correct syntax.

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 00-08-30 18:21:25 EDT, you write:

Yeah, I thought of that earlier, but Case isn’t listed in my Sybase manuals
(
I
don’t know why). I just tried the syntax that you suggested and that
didn’t
work
either. Any other ideas? Anyone?

Michael Welter

Do you have IIF() ? Some versions of Sybase have this function, which can be nested. Assuming you want to translate letter grades (A - F) into numeric values to calculate a grade point average (GPA). The following would do that:

iif ( grade = ‘A’, 4, iif (grade = ‘B’, 3, iif ( grade = ‘C’, 2, iif ( grade = ‘D’, 1, 0 ))))

Spaces are provided for readibility, not for syntax.

Try it and see if it works. If not, let us know… there are some other Sybase functions that you can use in combination to do something similar. If you can, try to provide some sample data.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

5

Michael

Try:

Case TABLENAME.COLUMNNAME
When ‘0’ Then ‘no’
When ‘1’ Then ‘yes’
When ‘2’ Then ‘not sure’
Else ‘unknown’
End

Mike

From: Michael Welter Michael.Welter@NOTES.AIRTOUCH.COM
Date: Wed, 30 Aug 2000 15:23:37 -0700

Tarlock,

Yeah, I thought of that earlier, but Case isn’t listed in my Sybase manuals (I
don’t know why). I just tried the syntax that you suggested and that didn’t work
either. Any other ideas? Anyone?

Michael Welter
Sr. Technical Analyst
Verizon Wireless

From past experiences against Sybase, you can achieve this by using a Case statement

I can’t remeber the syntax but I think it is something like this

Case TABLENAME.COLUMNNAME
0 = no
1 = yes
2 = not sure
end

Have a look in you Sybase manuals for case and you will get the correct syntax.

Web archives: listserv.aol.com/archives/busob-l.html To change service: Mail to listserv@listserv.aol.com with text in body of note

  • To Unsubscribe: ‘unsubscribe BUSOB-L’ - Vacation Options: ‘set BUSOB-L nomail’ ‘set BUSOB-L mail’

_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at http://profiles.msn.com.

Listserv Archives (BOB member since 2002-06-25)

6

Hi Michael,
What version of Sybase do you have? The CASE statement was new with v.11 I think. Hopefully only your manuals are out of date! Use the online manuals, Search for CASE in the Sybase IQ Language Reference at: http://manuals.sybase.com/onlinebooks/group- iq/iqg1121e/iq_lr/@Generic__BookView/665;cs=default;ts=default (Mike’s syntax looks correct)
If you have an older version of Sybase that doesn’t support CASE there are some tricks you can do with string functions if you are merely trying to translate a code. I am not sure what the original requirement was.

On Wed, 30 Aug 2000, Mike McErlain mikemcerlain@HOTMAIL.COM wrote:

Try:
Case TABLENAME.COLUMNNAME
When ‘0’ Then ‘no’
When ‘1’ Then ‘yes’
When ‘2’ Then ‘not sure’
Else ‘unknown’
End

Mike

From: Michael Welter Michael.Welter@NOTES.AIRTOUCH.COM
Date: Wed, 30 Aug 2000 15:23:37 -0700
but Case isn’t listed in my Sybase manuals(I don’t know why).

Listserv Archives (BOB member since 2002-06-25)

7

Michael,

I can’t remember the version of Sybase that offers If…else and Case statement, but I want to say its version 12 and above. For prior versions I’ve used the code below. I hope this is helps you.

//***************************************** //* If table_id = 1 then *
//* title = “Director” *
//* else *
//* If table_id = 2 then *
//* title = “Chairman” *
//* else *
//* If table_id = 3 then *
//* title = “President” *
//* else *
//* title = NULL *
//***************************************** title = substring(“Director”, (1 - abs(sign(table_id - 1))),10) +
substring(“Chairman”, (1 - abs(sign(table_id - 2))),10) + substring(“President”,(1 - abs(sign(table_id - 3))),10)

Shane Smith

Please respond to Business Objects Query Tool BUSOB-L@LISTSERV.AOL.COM

cc: (bcc: Shane A Smith/CHI/NTRS)

I’m creating a universe to go against our Remedy database (Sybase). I need an object that will convert the numeric values of the Status field in the database to the character values displayed in that field in Remedy. Everything I try gets a syntax error. Does anyone know the correct syntax for “if . . . then . . . else” in Sybase?

Michael Welter
Sr. Technical Analyst
Verizon Wireless

Listserv Archives (BOB member since 2002-06-25)

8

At 02:46 PM 8/30/2000 -0700, Michael Welterwrote:

I’m creating a universe to go against our Remedy database (Sybase). I need an object that will convert the numeric values of the Status field in the database
to the character values displayed in that field in Remedy. Everything I try gets
a syntax error. Does anyone know the correct syntax for “if . . . then . . . else” in Sybase?

Michael, starting with version 11.5.1, Sybase offers a CASE statement. E.g.:

CASE
WHEN column_name conditional-operator value
THEN value1
ELSE value2 or another When-then-else
END

You can have many "When – then"s before the final option of “else”.

Hope this helps,
Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)