Home » SQL & PL/SQL » SQL & PL/SQL » MEMBER OF operator
MEMBER OF operator [message #272036] Wed, 03 October 2007 13:59 Go to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Damn my eyes! I am working with (trying to pick up) some more knowledge on 10g collections and I'm playing around with MULTISET OPERATORS and the like. I'm havening (bonus points for whoever gets the reference) a problem with the MEMBER OF operator.
Here is the setup:
SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

DROP TABLE new_orders PURGE;
DROP TYPE billdate;

CREATE TYPE billdate IS TABLE OF DATE;
/

CREATE TABLE new_orders(ord_id NUMBER
                        ,notice billdate
                        ,payments billdate
                        ,receipt billdate
                        )
NESTED TABLE notice STORE AS notice_store_tab
NESTED TABLE payments STORE AS payments_store_tab
NESTED TABLE receipt STORE AS receipt_store_tab;

INSERT INTO new_orders VALUES(2458
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2459
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2460
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('21-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('22-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2461
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('21-Feb-2006','dd-mon-yyyy'),to_date('21-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('21-Feb-2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2462
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2463
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'))
                              , billdate(to_date('23-Jan-2006','dd-mon-yyyy'),to_date('17-Feb-2006','dd-mon-yyyy'))
                              );

INSERT INTO new_orders VALUES(2464
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('21-Feb-2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'),to_date('21-Feb-2006','dd-mon-yyyy'))
                              );

INSERT INTO new_orders VALUES(2464
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2465
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'))
                              , billdate(to_date('17-Jan-2006','dd-mon-yyyy'))
                              );
                              
INSERT INTO new_orders VALUES(2466
                              , billdate(to_date('15-Jan-2006','dd-mon-yyyy'),to_date('15-Feb-2006','dd-mon-yyyy'),to_date('15-Mar2006','dd-mon-yyyy'))
                              , billdate(to_date('21-Jan-2006','dd-mon-yyyy'))
                              , billdate(to_date('22-Jan-2006','dd-mon-yyyy'))
                              );


Now based on the above set of records, I'd say that TO_DATE('15-FEB-2006','DD-MON-YYYY') is definitely a member of each of the rows created above. Oracle seems to disagree:
SQL> SELECT notice, payments
  2  FROM new_orders
  3  WHERE TO_DATE('15-Feb-2006','dd-mon-yyyy') MEMBER OF notice;

no rows selected

Have I missed something fundamental? Am I trying to compare dates with small ducks?...(*sigh* I don't know, it's what first popped into my mind), am I being dumb and simply not seeing something really obvious? Anyway, all (well, most) input welcome.
Cheers
Re: MEMBER OF operator [message #272037 is a reply to message #272036] Wed, 03 October 2007 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I'll admit that I've never used data types as is used here & I really don't know what I'm doing, but I find the following "interesting":

  1  SELECT notice, payments
  2      FROM new_orders
  3*     WHERE notice = TO_DATE('15-Feb-2006','dd-Mon-yyyy')
SQL> /
    WHERE notice = TO_DATE('15-Feb-2006','dd-Mon-yyyy')
                   *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected DBADMIN.BILLDATE got DATE

The behavior above could/might explain why zero rows are selected.
I suspect implicit data type conversion is happening and it is not happening as expected to occur.
Re: MEMBER OF operator [message #272040 is a reply to message #272037] Wed, 03 October 2007 14:25 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Cheers ana, but I think that this is expected and the way that you would normally handle this would be
 SELECT notice, payments
         FROM new_orders
        WHERE notice = billdate(TO_DATE('15-Feb-2006','dd-Mon-yyyy'))


(Note, for the supplied data set, this would still return no rows as there is no row with just this date, however, I was thinking the same in terms of conversion, which had me move to
  1  SELECT notice, payments
  2          FROM new_orders
  3*        WHERE  billdate(TO_DATE('15-Feb-2006','dd-Mon-yyyy')) member of notice
SQL> /

But alas. Still no luck (but then, that was a bit ready, FIRE aim
no rows selected

Cheers

[Updated on: Wed, 03 October 2007 14:26]

Report message to a moderator

Re: MEMBER OF operator [message #272081 is a reply to message #272040] Thu, 04 October 2007 00:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It has to be something about the dates.. if you change the datatype of the billdate type to a table of varchar2(100), it works.
Re: MEMBER OF operator [message #272106 is a reply to message #272081] Thu, 04 October 2007 01:21 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Thanks Frank,
I tested that last night (with numbers too). Thankfully this isn't an actual requirement and I'm just 'playing'.
Thanks for the input guys.
Re: MEMBER OF operator [message #272133 is a reply to message #272106] Thu, 04 October 2007 01:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A slight modification of your example:
DROP TABLE new_orders PURGE;
DROP TYPE billdate;
DROP TYPE mydate;

CREATE TYPE mydate IS OBJECT (mycol DATE);
/

CREATE TYPE billdate IS TABLE OF mydate;
/

CREATE TABLE new_orders( ord_id NUMBER
                        ,notice billdate
                        ,payments billdate
                        ,receipt billdate
                        )
NESTED TABLE notice STORE AS notice_store_tab
NESTED TABLE payments STORE AS payments_store_tab
NESTED TABLE receipt STORE AS receipt_store_tab;

INSERT INTO new_orders
VALUES      (2458
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
            );

INSERT INTO new_orders
VALUES      (2459
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
            );

INSERT INTO new_orders
VALUES      (2460
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('21-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('22-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
            );

INSERT INTO new_orders
VALUES      (2461
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('21-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('21-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('21-Feb-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2462
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2463
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('23-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('17-Feb-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2464
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('21-Feb-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('21-Feb-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2464
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')), mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2465
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('17-Jan-2006', 'dd-mon-yyyy')))
            );

INSERT INTO new_orders
VALUES      (2466
           , billdate (mydate(TO_DATE ('15-Jan-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Feb-2006', 'dd-mon-yyyy'))
                     , mydate(TO_DATE ('15-Mar-2006', 'dd-mon-yyyy'))
                      )
           , billdate (mydate(TO_DATE ('21-Jan-2006', 'dd-mon-yyyy')))
           , billdate (mydate(TO_DATE ('22-Jan-2006', 'dd-mon-yyyy')))
            );

SELECT notice
FROM   new_orders
WHERE  mydate(TO_DATE('15-Feb-2006','dd-mon-yyyy')) member of notice
/
It seems to work just fine...

MHE
Re: MEMBER OF operator [message #272152 is a reply to message #272133] Thu, 04 October 2007 02:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, that is about the same as pablolee and I found out. There is something about dates that doesn't (seem to) make them fit for object-types..
Re: MEMBER OF operator [message #272285 is a reply to message #272036] Thu, 04 October 2007 07:52 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Once again, thanks for the input folks. It definitely helped.
Previous Topic: Formatting the sql query results using shell scripts
Next Topic: Can you join these 2 queries into 1?
Goto Forum:
  


Current Time: Sun Dec 11 02:41:35 CST 2016

Total time taken to generate the page: 0.05597 seconds