HELP: Tricky query.... 
Author Message
 HELP: Tricky query....

Does anyone know how I might approximate Oracle's DECODE function in
Sybase?  Say I have a numeric field that can take on the values 1-3, but
I want a select to return the strings 'one' through 'three'.  In Oracle,
I would use something like:

select decode(dummy_field, 1, 'one', 2, 'two', 3, 'three', 'zero') from
dummy_table;

I can't seem to find any way to accomplish this in Sybase other than
creating a lookup table.  I don't own the table I'm selecting from, and
I really need to get this done at the SQL level.  Any suggestions?

Thanks in advance,

  vcard.vcf
< 1K Download


Tue, 21 Nov 2000 03:00:00 GMT
 HELP: Tricky query....


Quote:

> Does anyone know how I might approximate Oracle's DECODE function in
> Sybase?  Say I have a numeric field that can take on the values 1-3, but
> I want a select to return the strings 'one' through 'three'.  In Oracle,
> I would use something like:

> select decode(dummy_field, 1, 'one', 2, 'two', 3, 'three', 'zero') from
> dummy_table;

> I can't seem to find any way to accomplish this in Sybase other than
> creating a lookup table.  I don't own the table I'm selecting from, and
> I really need to get this done at the SQL level.  Any suggestions?

One way is with characteristic functions.  Assuming your dummy_field has a
finite range of 1 to 3 as you describe, try this:

SELECT substring('one',  1-abs(sign(dummy_field-1)),3)+
       substring('two',  1-abs(sign(dummy_field-2)),3)+
       substring('three',1-abs(sign(dummy_field-3)),5)
FROM dummy_table

That should do it.



Tue, 21 Nov 2000 03:00:00 GMT
 HELP: Tricky query....

OK, I finally found the FAQ file and noted the entry for emulating the
DECODE expression.  I realize now that my simplified example might not
have been appropriate.  The field I'm using actually contains character
data.  It can take on the values 'L','U','M','E','B', but I need to
return 'Luxury', 'Upscale', 'Moderate', 'Economy', or 'Budget' in the
query.  Can the technique shown in the FAQ be modified to do this?  I'm
working on it, but I'm inexperienced with Sybase and don't have an
overabundance of time....

Thanks for any input,

  vcard.vcf
< 1K Download


Tue, 21 Nov 2000 03:00:00 GMT
 HELP: Tricky query....


Quote:

> This is a multi-part message in MIME format.
> --------------BC71A002B942F4D1EF82F947
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit

> OK, I finally found the FAQ file and noted the entry for emulating the
> DECODE expression.  I realize now that my simplified example might not
> have been appropriate.  The field I'm using actually contains character
> data.  It can take on the values 'L','U','M','E','B', but I need to
> return 'Luxury', 'Upscale', 'Moderate', 'Economy', or 'Budget' in the
> query.  Can the technique shown in the FAQ be modified to do this?  I'm
> working on it, but I'm inexperienced with Sybase and don't have an
> overabundance of time....

 In that case, ignore my last post and try this (I created a temp table to
illustrate)

CREATE TABLE #t1(class char(1))
INSERT #t1 Values('L')
INSERT #t1 Values('L')
INSERT #t1 Values('U')
INSERT #t1 Values('M')
INSERT #t1 Values('B')
INSERT #t1 Values('E')
INSERT #t1 Values('E')
INSERT #t1 Values('B')

SELECT   substring('Luxury',sign(charindex('L',substring(class,1,1))),6)+
         substring('Upscale',sign(charindex('U',substring(class,1,1))),7)+
         substring('Moderate',sign(charindex('M',substring(class,1,1))),8)+
         substring('Economy',sign(charindex('E',substring(class,1,1))),7)+
         substring('Budget',sign(charindex('B',substring(class,1,1))),6)
FROM  #t1

GO

(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)

 ----------------------------------
 Luxury
 Luxury
 Upscale
 Moderate
 Budget
 Economy
 Economy
 Budget

Having written this, I will now say that the long-term wiser thing to do
is have a table which de-references your 1 character "code" field.  

Create table classes(class char(1),
                     desc  char(20))

Insert classes values('L','Luxury')
Insert classes values('B','Budget')
...
...
etc

Then a simple join would provide the results you want and the code list
could be expanded without having to adjust the stored proc.

SELECT b.desc
FROM   dummy_table a,
       classes b
WHERE  a.class = b.class



Tue, 21 Nov 2000 03:00:00 GMT
 HELP: Tricky query....

I got it!  In the FAQ, 7.1 is focused on DECODE, but 7.2 (if-then-else)
had what I really needed.  Here's what I ended up with:

select isnull(substring('Luxury',charindex('L',Comp3Tier),255),
isnull(substring('Upscale',charindex('U',Comp3Tier),255),
isnull(substring('Moderate',charindex('M',Comp3Tier),255),
isnull(substring('Economy',charindex('E',Comp3Tier),255),
isnull(substring('Budget',charindex('B',Comp3Tier),255), ''))))) from
dummy_table

It ain't pretty, but it gets me where I need to go....

Thanks for the help!

  vcard.vcf
< 1K Download


Tue, 21 Nov 2000 03:00:00 GMT
 HELP: Tricky query....

I think you need to create temp lookup table like

#lookup or tempdb..lookup

in the stored procedure or query batch

Quote:

> Does anyone know how I might approximate Oracle's DECODE function in
> Sybase?  Say I have a numeric field that can take on the values 1-3, but
> I want a select to return the strings 'one' through 'three'.  In Oracle,
> I would use something like:

> select decode(dummy_field, 1, 'one', 2, 'two', 3, 'three', 'zero') from
> dummy_table;

> I can't seem to find any way to accomplish this in Sybase other than
> creating a lookup table.  I don't own the table I'm selecting from, and
> I really need to get this done at the SQL level.  Any suggestions?

> Thanks in advance,



Fri, 24 Nov 2000 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. A Tuff Tricky One - Please Help with Queries

2. tricky query - please help

3. Tricky Query help

4. Need help with a tricky query

5. Help with tricky SQL query

6. Help with a tricky SQL query

7. Tricky query for me, should be simple for you

8. Tricky Query Problem

9. tricky CASE WHEN query...

10. tricky query (for me atleast)

11. Tricky "most recent sales" query

12. Tricky query question.


 
Powered by phpBB® Forum Software