0

I know it is possible to filter a query from a list as in Select * from table where ID in (1,2,3,4), but is it possible to select from a list?

For example, I'd like to select date and offset date from a list of offset hours: 

select sysdate, sysdate - offset / 24 as offsetSysdate
from dual, (1,2,3,4) as offset

I know I can create a table and populate it with values to do this, but I was looking for a way to do it on the fly directly in the query.

Brino
  • 2,442
  • 1
  • 21
  • 36

3 Answers3

6

You can use a database "table of number" type - either create one or use one that handily exists like SYS.KU$_OBJNUMSET:

select sysdate, sysdate - offset.column_value / 24 as offsetSysdate
from TABLE (KU$_OBJNUMSET(1,2,3,4)) offset

Note that above offset is now an alias for the TABLE, and there is a pseudo-column called column_value for table types like this. Also no need for DUAL table.

If you prefer to create your own type:

create type num_tab as table of number;
/

select sysdate, sysdate - offset.column_value / 24 as offsetSysdate
from TABLE(num_tab(1,2,3,4)) offset;
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • thanks, this solution is the simplest and doesnt require selecting from a predefined set or using unions. – Brino May 11 '15 at 17:10
  • This solution is the most flexible because it also allows for non-contiguous numbers and non-integer numbers. – Brino May 11 '15 at 17:17
  • @Tony Andrews: Is there an equivalent of KU$_OBJNUMSET in oracle for other types (e.g. strings or datetimes)? Or would you simply use your 2nd example and make it of the desired type? – clesiemo3 May 11 '15 at 17:42
  • 1
    There is `SYS.KU$_VCNT` (TABLE OF VARCHAR2(4000)) for strings. I don't know of one for dates. – Tony Andrews May 11 '15 at 17:50
1

Sure, use a subselect for that

select sysdate, sysdate - offset / 24 as offsetSysdate
from
(
    select 1 as offset from dual
    union
    select 2 from dual
    union
    select 3 from dual
    union
    select 4 from dual
)

For a more sophisticated way to generate the number sequence, see e.g. here.

EDIT: Brino figured out the suggested code improvement, here it is in readable format and slightly improved:

select sysdate, sysdate - offset.value / 24, offset.value as offsetSysdate
from (select r as value
      from (select level r from dual connect by level <= 4)
     ) offset;
Community
  • 1
  • 1
davidhigh
  • 14,652
  • 2
  • 44
  • 75
  • thanks, using your link i was able to create a simpler query: `select sysdate, sysdate - offset.value / 24, offset.value as offsetSysdate from (select r as value from (select level r from dual connect by level <= 100) where r in (1,3,4,5)) offset;` – Brino May 11 '15 at 17:08
  • @Brino: Good, I'll copy that into my answer. – davidhigh May 11 '15 at 17:09
  • @Brino: ... and made some slight improvement – davidhigh May 11 '15 at 17:12
1

Use the with clause to create a table of offsets:

with offset_table(offset) as
( select level lvl
  from dual
  connect by level <= 4
)
select sysdate, sysdate - offset / 24 as offsetSysdate
from offset_table;
Gary_W
  • 9,933
  • 1
  • 22
  • 40