RE: writing oracle query

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 7 Feb 2006 12:47:29 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410505EA5941@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of laura pena

	Sent: Tuesday, February 07, 2006 12:18 PM
	To: oracle-l_at_freelists.org
	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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 07 2006 - 11:47:29 CST