Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unique Dates and TRUNC

Status
Not open for further replies.

jcs1953

IS-IT--Management
Nov 28, 2007
53
US
I have this query:

SELECT ( REQ_PANELS.RUN_DATE) "Start_Date" FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID = '10364.0') AND (REQ_PANELS.RUN_DATE IS NOT NULL) ORDER BY REQ_PANELS.RUN_DATE

This returns a list of dates and times (38 of them). If I modify the query with TRUNC like this:

SELECT TRUNC ( REQ_PANELS.RUN_DATE) "Start_Date" FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID = '10364.0') AND (REQ_PANELS.RUN_DATE IS NOT NULL) ORDER BY REQ_PANELS.RUN_DATE

I then get a list of just dates (again 38). If I modify the query with UNIQUE like this:

SELECT UNIQUE ( REQ_PANELS.RUN_DATE) "Start_Date" FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID = '10364.0') AND (REQ_PANELS.RUN_DATE IS NOT NULL) ORDER BY REQ_PANELS.RUN_DATE

I get 10 rows of unique Date/Time (although not unique dates).
What I need is a list of UNIQUE TRUNC dates but I can't fiqure out how to word the query.
Any ideas?
Thanks
 
Fiqured it out. I needed to change "ORDER BY REQ_PANELS.RUN_DATE" to "Order by 1
 
and you also could have used
order by trunc(REQ_PANELS.RUN_DATE)

Bill
Lead Application Developer
New York State, USA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top