set linesize 150
set pagesize 9999
set long 1000000

col table_name    for a32
col index_name    for a32
col column_name   for a32
col text          for a100
col last_analyzed for a30

spool sxs_output.out
select text from user_views where view_name = upper('hhs_problem_aud_vw');

desc  USR_TBL_SBART_B_PAT_RESULTS
desc  hhs_problem_aud_vw
desc  USR_TBL_SBART_B_PATIENTS

select    table_name, index_name, column_name, position 
from      user_ind_columns
where     table_name in ('USR_TBL_SBART_B_PAT_RESULTS','USR_TBL_SBART_B_PATIENTS')
;

select    table_name
         ,num_rows
         ,to_char(last_analyzed,'DD/MM/YYYY HH24:MI:SS') LAST_ANALYZED
from      user_tables
where     table_name in ('USR_TBL_SBART_B_PAT_RESULTS','USR_TBL_SBART_B_PATIENTS');

select    table_name
         ,index_name
         ,num_rows
         ,to_char(last_analyzed,'DD/MM/YYYY HH24:MI:SS') LAST_ANALYZED
from      user_indexes
where     table_name in ('USR_TBL_SBART_B_PAT_RESULTS','USR_TBL_SBART_B_PATIENTS')
;

select    table_name, index_name, column_name, position 
from      user_ind_columns
where     table_name in ('USR_TBL_SBART_B_PAT_RESULTS','USR_TBL_SBART_B_PATIENTS')
order by  table_name, index_name,position
;



set autotrace on
SELECT    PR.PAT_SEQ
         ,Q1.PROBLEM_DESCRIPTION
FROM      USR_TBL_SBART_B_PAT_RESULTS PR
         ,(  SELECT    P.PAT_SEQ
                      ,HVS.PROBLEM_DESCRIPTION
             FROM      hhs_problem_aud_vw HVS
                      ,USR_TBL_SBART_B_PATIENTS P
             WHERE     HVS.CPI_SEQ = P.CPI_SEQ 
          ) Q1
WHERE     Q1.PAT_SEQ(+) = PR.PAT_SEQ 
GROUP BY  PR.PAT_SEQ
         ,Q1.PROBLEM_DESCRIPTION
; 
spool off
set autotrace off
exit