SQL dummy query 1

[tful282]

This is probably very simple...I need a query that will return rows of a single column with certain hard-coded names. i.e. return should look like:
FRED
JOHN
BILL
MIKE
you get the idea.
I want to join this query to another.
Thanks

 

[SantaMufasa]

TFul,

Frankly, No, I don't get the idea. If you want multiple "hardcoded" names like:
FRED
JOHN
BILL
MIKE
...why don't you have them in a table (NameTab), in a column (Name), then "SELECT Name from NameTab;" ? Why would you want to "hardcode" them into software (which is lots more expensive to modify than entries in a table)?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:07 (15Jan04) GMT, 11:07 (15Jan04) Mountain Time)

 

[tful282]

You are probably right, i should not hard code this in my view.
Thanks

 

[nbateman]

I don't know; there are occasions where this is useful.

I have used something similar when there is logically a table, but I don't want a physical table. This is useful where the application relies on a fixed set of values and where a new or changed value would break the system (not sure your names example meets this criteria?).

I normally create a view and then include that in my query.
E.g. CREATE VIEW V_STATUS AS (
SELECT 1 STATUS_ID, "QUEUED" STATUS_NAME FROM DUAL
UNION
SELECT 2 STATUS_ID, "RUNNING" STATUS_NAME FROM DUAL
UNION
SELECT 3 STATUS_ID, "COMPLETED" STATUS_NAME FROM DUAL
UNION
SELECT 4 STATUS_ID, "FAILED" STATUS_NAME FROM DUAL );

I can then use it as follows:
SELECT JOB_ID, STATUS_NAME
FROM JOB, V_STATUS
WHERE JOB.STATUS_ID = V_STATUS.STATUS_ID;

 

[tful282]

I thought there was a way to do this, and for the limited number of names that I have, this seems to make sense.
Thanks for the tip!

 

[carp]

If you are going to use this approach, I might suggest using UNION ALL instead of UNION. This will avoid incurring any sorts (albeit they would be short sorts, they would nonetheless occur).