Home » Developer & Programmer » Reports & Discoverer » SQL HELP (10g)
SQL HELP [message #511290] Fri, 10 June 2011 14:25 Go to next message
russromei
Messages: 6
Registered: June 2011
Location: Charlotte
Junior Member
Seems simple enough, yet I am unable to achieve it
Below is current working scripts with hard coded values
Anywhere you see "offender_book_id = '0000450364'"
Needs to be replaced with a list of offender_book_id's
from anywhere except ost1 and ost2
The script currently selects a min and a max record to compare date fields but I can only get it to work with the hard codes

THANKS FOR ANY HELP IN ADVANCE




Select lu.description
,lu.agy_loc_id
,bk.booking_no
,o.last_name || ', ' || o.first_name name
,o.offender_id_display
,ost1.offender_book_id ofbid_MIN
,ost1.living_unit_id MIN
,ost1.assignment_time AT_MIN
,ost2.offender_book_id ofbid_MAX
,substr(ost2.living_unit_id,1,2) LUMAX
,ost2.assignment_time AT_MAX

FROM
-- bed_assignment_histories bh,
living_units lu,
offender_bookings bk,
offenders o
,(select * from bed_assignment_histories c where assignment_time = (select min(assignment_time) from bed_assignment_histories d where offender_book_id = '0000450364' and substr(living_unit_id,1,2) = '85')) ost1
,(select * from bed_assignment_histories e where assignment_time = (select max(assignment_time) from bed_assignment_histories f where offender_book_id = '0000450364' )) ost2

WHERE bk.offender_id = o.offender_id
AND bk.active_flag = 'Y'
AND bk.offender_book_id = ost1.offender_book_id


----
AND ost1.offender_book_id = '0000450364'
AND substr(ost1.living_unit_id,1,2) = substr(ost2.living_unit_id,1,2)
AND ost1.living_unit_id = lu.living_unit_id
AND ost1.assignment_date < (sysdate - 34)
AND SUBSTR (LU.description, 6, 2) || SUBSTR (LU.description, 9, 2) = :ppod
AND LU.agy_loc_id = :pfac


Re: SQL HELP [message #511293 is a reply to message #511290] Fri, 10 June 2011 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post DDL for tables including relationship between them (PK/FK).

Regards
Michel
Re: SQL HELP [message #512815 is a reply to message #511293] Wed, 22 June 2011 06:58 Go to previous messageGo to next message
donosemihai
Messages: 2
Registered: June 2011
Location: Romania
Junior Member
you can use a GUI tool, personally i am using DbSchema
Re: SQL HELP [message #512819 is a reply to message #512815] Wed, 22 June 2011 07:17 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does this have to do with the original question?!?
Re: SQL HELP [message #512863 is a reply to message #512819] Wed, 22 June 2011 10:25 Go to previous messageGo to next message
russromei
Messages: 6
Registered: June 2011
Location: Charlotte
Junior Member
OK, this has been resolved, thanks all
Re: SQL HELP [message #512865 is a reply to message #512863] Wed, 22 June 2011 10:35 Go to previous message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How?

Regards
Michel
Previous Topic: report error
Next Topic: A new SQL Help Request
Goto Forum:
  


Current Time: Sat Sep 20 17:26:54 CDT 2014

Total time taken to generate the page: 0.08106 seconds