Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (oracle 7, sql navigator)
Query Help [message #355815] Mon, 27 October 2008 13:34 Go to next message
da1uwant2meet
Messages: 13
Registered: October 2008
Location: texax
Junior Member
Hello I am trying to filter on the QA date associated with the lot rather than the material....here is what i have so far

select distinct l.rgstry_id material_id,
rh.lot_id,
l.lot_label,
l.lot_typ_cd,
rm.qa_approval_date,
ore.order_reason_name order_reason,
l.location_spot_cd,
l.lot_state_cd,
l.crs_process_cd Sample_type,
rh.initial_rqust_date request_date

from lot l,
rgstrd_matl rm,
request_history rh,
order_reason ore
where rh.lot_id = l.lot_id
and l.rgstry_id = rm.rgstry_id
and l.location_spot_cd in ('Thurnall In Transit','Robot')
and rm.qa_approval_date is not null
and rh.sample_state_cd = 'Rqstd'
and rh.current_state = 'Y'
and rh.initial_rqust_date < sysdate - 2
and rh.order_reason_id = ore.order_reason_id
order by ore.order_reason_name,initial_rqust_date,l.lot_typ_cd,l. crs_process_cd, l.rgstry_id
Re: Query Help [message #355821 is a reply to message #355815] Mon, 27 October 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Why "distinct"?

Regards
Michel
Re: Query Help [message #355911 is a reply to message #355815] Tue, 28 October 2008 08:23 Go to previous messageGo to next message
da1uwant2meet
Messages: 13
Registered: October 2008
Location: texax
Junior Member
Hi...I was using distinct to to remove any possible duplicates..Show I have not used this?
Re: Query Help [message #355914 is a reply to message #355911] Tue, 28 October 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to it ONLY and ONLY if the model indicates there can be duplicates AND your business need requires to not show them.
Most often, it just indicates the query is not appropriate to answer the question.

Regards
Michel
Re: Query Help [message #355920 is a reply to message #355815] Tue, 28 October 2008 09:19 Go to previous messageGo to next message
da1uwant2meet
Messages: 13
Registered: October 2008
Location: texax
Junior Member
Ummm...I dont understand...Did I mention Im a noobie Embarassed
Re: Query Help [message #355923 is a reply to message #355920] Tue, 28 October 2008 09:34 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, don't understand what you don't understand.
I just try to make you think if you really need "distinct", as I don't know your environment, I can't answer this for you.

Regards
Michel
Previous Topic: How to reterive the newly updated record?
Next Topic: with and test
Goto Forum:
  


Current Time: Sat Dec 10 20:54:14 CST 2016

Total time taken to generate the page: 0.14180 seconds