Databases
StackOverflow2013
Postsdbo
POReturning relevant date from multiple tables including additional table info
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturning relevant date from multiple tables including additional table info
    primarykey
    data
    text
    <p>This is continuing on from a query that was solved previously via stackoverflow (<a href="https://stackoverflow.com/questions/8433465/returning-nearest-date-to-date-in-a-different-table-oracle">Returning nearest date to date in a different table</a>), however, I now wish to develop it a little further.</p> <p>The SQL I have is this:</p> <pre><code>SELECT * FROM (SELECT O_ASSESSMENTS.ASM_SUBJECT_ID as "P Number", O_ASSESSMENTS.ASM_ID as "Assessment ID", O_ASSESSMENTS.ASM_START_DATE as "Assessment Start", O_ASSESSMENTS.ASM_END_DATE as "Assessment End", O_SERVICE_EVENTS.SEV_ID as "Event ID", O_SERVICE_EVENTS.SEV_ACTUAL_DATE as "Event Start", O_SERVICE_EVENTS.SEV_OUTCOME_DATE as "Event End", ROUND(O_ASSESSMENTS.ASM_START_DATE -O_SERVICE_EVENTS.SEV_ACTUAL_DATE,0) as "Likely", row_number() over(PARTITION BY O_ASSESSMENTS.ASM_ID ORDER BY abs(O_ASSESSMENTS.ASM_START_DATE - O_SERVICE_EVENTS.SEV_ACTUAL_DATE))as "Row Number" FROM O_ASSESSMENTS JOIN O_SERVICE_EVENTS ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID Where O_SERVICE_EVENTS.SEV_CODE IN ('ICS_E3','CPINVEST') AND O_ASSESSMENTS.ASM_QSA_ID IN ('AA1329','AA521') ) WHERE "Row Number" = 1 </code></pre> <p>Basically, we have two tables - o_assessments and o_service_events, and this SQL is returning the nearest service event to the assessment. Now I want to include some more contexual information into the query to make it more helpful for the end user - namely the team name and the worker name.</p> <p>Unfortunately, team and worker are in a different table again (o_responsibilities), and are linked to the o_assessments by asm_id to res_rec_id.</p> <p>Problem is, I'm really not sure how to call this table into the above sql - so any advice would really be appreciated!</p> <p>I was also wondering if it would be possible to tweak the existing query to only return corresponding events when the 'likely' field had a difference of 0 (as anything greater is probably not related to that particular assessment). I know I can add it into the where, but if I add the line</p> <pre><code>and "Likely" = 0 </code></pre> <p>it only returns assessments with an event, which wouldn't highlight any issues (i.e. assessments without corresponding events). </p> <p>I'm learning all the time with SQL, but there are a lot of things that just seem really beyond me at the moment, so any advice is much appreciated! I wasn't sure whether to amend my original query or to start a new one, so I hope I haven't infringed on any rules inadvertently.</p> <p>Edit:</p> <p>Okay, following Mark's solution, this is what I have working. </p> <pre><code>SELECT * FROM (SELECT OAS.ASM_SUBJECT_ID as "P Number", OAS.ASM_ID as "Assessment ID", OAS.ASM_START_DATE as "Assessment Start", OAS.ASM_END_DATE as "Assessment End", OAS.ASM_AUTH_DATETIME as "Authorisation Date", nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') as "Outcome", nvl(olm_bo.get_org_name(ORE.RES_PARTY_OUN_ID),'') as "Team", nvl(olm_bo.get_per_name(ORE.RES_PARTY_ID),'') as "Worker", OSE.SEV_ID as "Event ID", OSE.SEV_ACTUAL_DATE as "Event Start", OSE.SEV_OUTCOME_DATE as "Event End", ROUND(OAS.ASM_START_DATE -OSE.SEV_ACTUAL_DATE,0) as "Likely", row_number() over(PARTITION BY OAS.ASM_ID ORDER BY abs(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE))as "Row Number" FROM O_ASSESSMENTS OAS INNER JOIN O_RESPONSIBILITIES ORE ON OAS.ASM_ID = ORE.RES_REC_ID AND ORE.RES_PARTY_OUN_ID = 'TEAM' LEFT JOIN O_SERVICE_EVENTS OSE ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID AND OSE.SEV_CODE IN ('EVENT') AND ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) &gt;= -7 AND ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) &lt;= 7 Where OAS.ASM_QSA_ID IN ('ACODE') AND nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') &lt;&gt; 'Abandon' ) WHERE "Row Number" = 1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USbawpie
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReturning relevant date from multiple tables including additional table info
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POReturning relevant date from multiple tables including additional table info
      1. This table or related slice is empty.
      1. VTUpMod
    1. COis something missing from the inner select? Specifically around the join... it has an "AND" followed by a "WHERE" which should result in a SYNTAX error
      singulars
      1. POReturning relevant date from multiple tables including additional table info
      1. USxQbert
    2. COSorry, was fiddling with the code and copied the wrong copy across...fixed now!
      singulars
      1. POReturning relevant date from multiple tables including additional table info
      1. USbawpie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload