cast, the and joins

From: Jason King <jhking_at_airmail.net>
Date: Wed, 05 Oct 2005 17:40:21 -0500
Message-ID: <11k8lim9ovcne01@corp.supernews.com>

Environment Oracle 10g Release 1 on Win 2003. From USA where weeks run Sun-Sat.
I have a table day_types which has columns like this. dayy_id number not null -- pk
day_of_week_mask varchar2(7) -- 1 for day counts, 0 for day doesn't dayy_desc varchar2(20)
It has data like this
dayy_id day_of_week_mask dayy_desc

1          0111110        Weekday
2          1000001        Week-end
3          0001000        Hump-day

...
Users can set up different day_types so these aren't fixed.

I have a type defined as
CREATE OBJ_NUMBER_T AS TABLE OF NUMBER
I have a function f_daymask that returns an obj_number_t with one row for every 1 in a day_of_week_mask type string, e.g. myvar := f_daymask('1000001') ;
myvar(1) = 1
myvar(2) = 7

I can turn the day_of_week_mask into a set of "on" numbers using my function as well as Oracle's THE and CAST.   select dowm.column_value doy
    from the ( select cast( f_daymask(dayy.day_of_week_mask) AS OBJ_NUMBER_T )

                 from dual,
                      day_types dayy
                where dayy_id = 1


                 ) dowm

This returns a result set of
2
3
4
5
6
Which is correct as far as it goes but what I'd like is to be able to remove the "where dayy_id = 1" and run a query so that I get dayy_id doy

1         2
1         3
1         4
1         5
1         6
2         1
2         7
3         4

If I remove the where I get:
ORA-01427: single-row subquery returns more than one row

If I add dayy_id inside the THE like this: select dowm.column_value doy

    from the ( select dayy_id , cast( f_daymask(dayy.day_of_week_mask) AS OBJ_NUMBER_T )

                 from dual,
                      day_types dayy
                where dayy_id = 31


                 ) dowm

I get:
ORA-02324: more than one column in the SELECT list of THE subquery

How can I get a result set of the type I'm looking for? day_types has a small number of rows (<10) so a self-join or pl/sql based solution would work in this case.

Hints or tips would be appreciated, I know you folks are busy too. Received on Wed Oct 05 2005 - 17:40:21 CDT