Hi, Everyone... I'm trying to return results of a query but in addition to those results, I'd like to return a value from a temporary table.
with t as (
select 'red' color from dual union all
select 'blue' from dual union all
select 'yellow' from dual union all
select 'green' from dual
)
The SQL query would be basic (e.g. select number from dual), the table contains just a number and I want each number to be assigned a color from the temporary table. So the results would look something like...
NUMBER COLOR
-------- ----------
1 red
2 blue
1 red
3 yellow
4 green
I don't know the numbers beforehand - I'm only creating the temporary table with about 15 colors in it. Once it uses all colors, it'd be bonus if it just assigned a default color (like black).
The question seems odd but it's a simplified version of a larger issue I'm up against. Thanks for any help! Running Oracle 11g.