Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Unanswered: Nested SQL-statements in oracle

    I'm trying to nest a statement in oracle. The first statement looks like this:

    Select DEREF(TREAT (REF(P) AS ref clubmember_ty)).passwd
    FROM persons_tab p; This works fine.

    This gives me the password from the clubmember_ty. But insted of typing the whole DEREF...statement for every atrribute i want to get, I would like to type something like this:

    select cm.username,cm.password,cm.E_mail,cm.surname from(
    Select DEREF(TREAT (REF(P) AS ref clubmember_ty))
    FROM persons_tab p) cm;

    The problem is that i get the error messege: "invalid columname" for cm.username,cm.password,cm.E_mail.

    Clubmember is a subtype of person(all created as types) and they are stored in a table that contains types.

    Anyone have any suggestions? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Nested SQL-statements in oracle

    Originally posted by hegullak
    I'm trying to nest a statement in oracle. The first statement looks like this:

    Select DEREF(TREAT (REF(P) AS ref clubmember_ty)).passwd
    FROM persons_tab p; This works fine.

    This gives me the password from the clubmember_ty. But insted of typing the whole DEREF...statement for every atrribute i want to get, I would like to type something like this:

    select cm.username,cm.password,cm.E_mail,cm.surname from(
    Select DEREF(TREAT (REF(P) AS ref clubmember_ty))
    FROM persons_tab p) cm;

    The problem is that i get the error messege: "invalid columname" for cm.username,cm.password,cm.E_mail.

    Clubmember is a subtype of person(all created as types) and they are stored in a table that contains types.

    Anyone have any suggestions? Thanks.
    I don't use this O-R stuff myself, but normally when you do:

    SELECT a,b,c FROM
    ( SELECT...
    )

    ... the selected column names (a,b,c) have to correspond to the names of values in the inner select. So is the answer something like:

    select cm.x.username,cm.x.password,cm.x.E_mail,cm.x.surna me from(
    Select DEREF(TREAT (REF(P) AS ref clubmember_ty)) as x
    FROM persons_tab p) cm;

    ? Just guessing.

    As a matter of interest, are you just playing around with the O-R features to see what they do, or are you already committed to using them in a real database? I sincerely hope it is the former, as I believe they have no place in a real business system (IMVHO!)

  3. #3
    Join Date
    Apr 2003
    Posts
    2
    Thank you for quick respond.

    I still get the same message.

    This is a scool-assignment, so it has nothing to do with a real database.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by hegullak
    I still get the same message.
    Sorry, I don't know then.

    Originally posted by hegullak
    This is a scool-assignment, so it has nothing to do with a real database.
    That's a relief!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •