1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select query not working in Subtypes

Discussion in 'SQL PL/SQL' started by nidhichutani, Apr 16, 2009.

  1. nidhichutani

    nidhichutani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I have the select query at the bottom, that doesn't work. It should be simple but I dont get it....anyone knows?

    Code (SQL):

    DROP TYPE p_type;
    DROP TABLE pro;
    DROP TYPE d_type;

    CREATE OR REPLACE TYPE p_type AS OBJECT (
       pid   INT
    )
    NOT FINAL NOT INSTANTIABLE;
    /

    CREATE TABLE pro OF p_type (pid PRIMARY KEY);

    CREATE OR REPLACE TYPE d_type UNDER p_type (
       ctr   NUMBER
    )
    ;
    /

    INSERT INTO pro
         VALUES (d_type (101, 4));

    INSERT INTO pro
         VALUES (d_type (102, 5));

    ------below given select query is NOT WORKING
    SELECT ctr
      FROM pro p
     WHERE p.pid = 101
    Thanks,
    -Nidhi
     
  2. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hmmm you are selecting ctr, but in your table the column identifier is PID, how can it work?
     
  3. nidhichutani

    nidhichutani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    ctr is in the subtype d_type.
    Look at the insert table commands.

    I want to access the subtype attribute and I want to use the select statement. Can anyone help?

    Thanks,
    -Nidhi
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    when selecting data from substitutable tables and columns, you have to use built-in functions and conditions like TREAT and SYS_TYPEID , and "IS OF type ".

    In your case you have to use the TREAT function

    Do this
    Code (Text):

    SELECT TREAT(VALUE(p) AS d_type).ctr ctr
       FROM pro p;
    This will give u your ctr column
     
  5. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
  6. nidhichutani

    nidhichutani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks a lot, that worked.

    Another related question, how do now update ctr to 1 where pid is 101.
    Does TREAT work there? Can you show how it works in my example.

    Thanks,
    -Nidhi