Re: Q: getting literal values into row output

You could use an inline view based on the DUAL table to generate the literals you want.

SELECT set_of_values.thing, items.item
FROM items,

     (SELECT 'CAR' thing FROM dual
      UNION
      SELECT 'TRAIN' FROM dual
      UNION
      SELECT 'BUS' FROM dual) set_of_values

Malcolm Dew-Jones wrote:

> Sometimes I want to duplicate each row a number of times for a set of
> values, sometimes numeric, sometimes not. This is fine if the set of
> values is in a table
>
> create table set_of_values ( thing varchar2(10) );
> insert into things values 'CAR';
> insert into things values 'TRAIN';
> insert into things values 'BUS';
> ... etc ...
>
> Now to repeat each item for every thing in the set
>
> select thing , item from set_of_values , items
>
> so if we have items abc and xyz then we get a display like
> CAR abc
> TRAIN abc
> BUS abc
> CAR xyz
> TRAIN xyz
> BUS xyz
>
> Is there a simple way to do this with out two tables? In particular is
> there a way to include the set of values (i.e. CAR, TRAIN, BUS) into the
> output by including them literally in an SQL statement somehow?
>
> e.g. (doesn't work of course)
>
> select thing , item
> from items , (select thing from ('CAR','BUS','TRAIN') ) ;
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> in this example I want this to return
> three rows, one thing per row.