The issue arises from the need to return values from need to return a single-row result set that will be used for a web service.
For example, if I have a select statement which currently returns a result set with 3 columns and 1 row, is there a way to "append" a value to it in its own column? The new value could be the result of any arbitrary select which returns exactly one row.
select a, b, c from d where a = 'id' gives
col1 col2 col3
a b c
The new select should give:
col1 col2 col3 col4
a b c *NEW*
Arbitrary in this case is used to mean not necessarily related to any of the values or tables in the original select statement.
I want to know how to do this in Oracle, but hopefully there is a solution which uses standard SQL features so most databases will handle it.
NEW I don't mean the string literal 'NEW', but I mean the result of the new select statement that I want to "append".
If you want a new select statement, you can do that in two ways. One is a
select within a
select col1, col2, col3, (select 'NEW' from d limit 1) as col4 from d
The other is a
select d.col1, d.col2, d.col3, const.col4 from d cross join (select 'NEW' as col4 from d limit 1) const
I am using a simple subquery just as an example. I assume your subquery is a bit more complicated.
this type of structure will work. note you will get a cross join when there are more than one row.
select a,b,c,d from ( select a,b,c from dual ) , (select d from dual )
select a, b, c, (select smth from table2 limit 1) as col4 as col4 from d where a = 'id'
The second query should return exactly one row and one column