SQL*PLUS Question

From: <library.treasures_at_NOSPAMsaqnet.co.uk>
Date: Mon, 24 Sep 2001 19:20:49 GMT
Message-ID: <3baf7a2c.19595766_at_news.easynet.co.uk>

[Quoted] [Quoted] 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> Received on Mon Sep 24 2001 - 21:20:49 CEST