Re: SQL*PLUS Question

On Mon, 24 Sep 2001 15:00:09 -0500, TurkBear <noone_at_nowhere.com> wrote:

>
>A couple of ways to do this:
>1:
>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..

A great many thanks indeed!

I am going to employ your first suggestion, I am sure it will work quite well.
I have only started using SQL and I have now learned a very useful method from you.

Regards,

Thomas
>
>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 Tue Sep 25 2001 - 17:02:08 CEST