Re: SQL*PLUS Question

A couple of ways to do this:
1:
[Quoted] use a DECODE statement for the Fruit column like: select decode(code,'1','Apple','2','Pear','3','Banana',...) fruit from basket; Since you know the values for the codes and there are not a lot of them this may be the simplest way.

2:
create a db_link to the remote table called, for example, rbasket.. Then build a view that joins the remote table with the local one similar to this: create or replace view fruitnames as select r.code,l.name from basket_at_rbasket r,mylocaltable l where r.code = l.code

then you can
select name as fruit from fruitnames..

library.treasures_at_NOSPAMsaqnet.co.uk wrote:

>Could anyone tell me, please, how can I achieve the following.
>
>Suppose I want to query a table called "Basket", at the remote server.
>In this table there is a field called "Code", which I'd select as
>"Fruit", because each code corresponds to one definite fruit:
>
>SELECT Code AS Fruit
>FROM Basket;
>
>Now, in the "Fruit" column of my result set the code numbers will
>appear from the original "Code" field of "Basket":
>
>
>FRUIT
>_ _ _ _ _ _ _ _ _
>1
>3
>1
>1
>3
>2
>1
>
>I know that 1 is the code for Apple, 2 is for Pear, and 3 stands for
>Banana.
>
>However, although there are no more than a dozen kinds of fruit, for
>an easier analysis of the result I would like to read the fruit names
>directly, rather than their hard to scan numeric codes:
>
>FRUIT
>_ _ _ _ _ _ _ _ _
>Apple
>Banana
>Apple
>Apple
>Banana
>Pear
>Apple
>
>The trouble is there is no column in the table "Basket" that I could
>use for getting the fruit names directrly. I cannot alter that table,
>"Basket", on the server, and I only have a local table on the C: drive
>that lists codes and corresponding fruit names.
>
>I would not know how to join a local table with a remote table,
>anyway, while connected to the remote DB via the network.
>
>Is there a way to command in SQL language the substitution of fruit
>names for the appropriate codes?
>
>If there is, and, if I do not push my lack, can I order the result set
>alphabetically, or will it retain the masked order of the code numbers
>at the ordering statement?
>(For in the example, the order "1, 2, 3" would not match the order
>"Apple, Banana, Pear")
>
>Many thanks in advance,
>
>Thomas <library.treasures_at_NOSPAMsaqnet.co.uk>
>
>
>
>
>

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Mon Sep 24 2001 - 22:00:09 CEST