RE: writing oracle query

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
and      
                                       type='ATA-BACKLOGS' or type =
'ATA-CALLCOUNTS' 
                                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
VARCHAR2(30)
        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,
        -Lizz
        

        
________________________________

        Brings words and photos together (easily) with
        PhotoMail
<http://us.rd.yahoo.com/mail_us/taglines/PMDEF3/*http://photomail.mail.y
ahoo.com> - it's free and works with Yahoo! Mail.

Other related posts: