RE: writing oracle query

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 7 Feb 2006 12:47:29 -0500

Why do you want to replace the inline view with an external view?  The
statement is doing a join to a view.  Inline views are generally a very
effective way to code SQL statements.
IMHO -- Mark D Powell --


        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of laura pena
        Sent: Tuesday, February 07, 2006 12:18 PM
        To: oracle-l@xxxxxxxxxxxxx
        Subject: writing oracle query
        I have the following query that gets information from a
recorderid that is the last ones entered :
        select * 
        from stateofhealth soh, (
                                select type, typeidentifier,
max(lastmodified) lastmodified 
                                from stateofhealth where recorderId=14
                                       type='ATA-BACKLOGS' or type =
                                group by type, typeidentifier
                                          ) b 
        where soh.type = b.type 
                 and soh.typeidentifier = b.typeidentifier 
                 and soh.lastmodified = b.lastmodified 
        Wondering if there is a way to re-write without using select
statement in from clause.
        I guess I could create a view and join with a view. 
        Here is some information on this table.
        It's a ranged partitioned table by lastmodified.
        Primary Key is stateofhealthid.
        I want all information from a recorderid that is the last
entered (ie. lastmodified desc). type, typeidentifier, max(lastmodified)
gives me this information I just need the rest of the columns.
        SQL> desc stateofhealth;
        Name                                      Null?    Type
        ----------------------------------------- --------
        STATEOFHEALTHID                           NOT NULL NUMBER(10)
Primary Key
        RECORDERID                                NOT NULL NUMBER(10)
        TYPEIDENTIFIER                            NOT NULL NUMBER(5)
        LASTMODIFIED                              NOT NULL DATE
partitioned by month
        TYPE                                             NOT NULL
        STATUS                                             VARCHAR2(5)
        MESSAGE                                            VARCHAR2(256)
        Just wondering if there was any other way to re-write.
        As always your comments/questions are appreciated.
        Many Thanks,


        Brings words and photos together (easily) with
<*http://photomail.mail.y> - it's free and works with Yahoo! Mail.

Other related posts: