StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can just join <code>o_responsibilities</code> to your inline view on the respective IDs and then include its columns in the query.</p> <p>Be aware that you would need to put the <code>where likely = 0</code> bit in the outer query (with the row number criterion), rather than the inline view, because <code>likely</code> is an alias. If you want to highlight assessments without events, then you should do a <code>left join</code> and also add the condition that the <code>sev_id is null</code>.</p> <p>Something like this should do the trick:</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", o_responsibilities.[WHATEVER] as "Team/Worker Stuff", 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 rn from o_assessments left join o_service_events on o_service_events.sev_subject_id = o_assessments.asm_subject_id left join o_responsibilities on o_responsibilities.res_rec_id = o_assessments.asm_id where o_service_events.sev_code in ('ICS_E3','CPINVEST') and o_assessments.asm_qsa_id in ('AA1329','AA521')) where rn = 1 and (likely = 0 or sev_id is null); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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