Code:
QUERY=`echo "Select Severity,Dupl_count,Creation_Time,Last_Received,Node_Name,Node_Name,Object,Message_Group,Message_Text,Last_Annotation from " \
" ( SELECT " \
" decode(msg.severity,1,'Unknown',2,'Normal',4,'Warning',8,'Critical',16,'Minor',32,'Major') as Severity " \
" , msg.dupl_count as Dupl_count " \
" , TO_CHAR(msg.local_creation_time,'DD-Mon-YYYY HH24:MI:SS') as Creation_Time " \
" , TO_CHAR(msg.local_last_time_received,'DD-Mon-YYYY HH24:MI:SS') as Last_Received " \
" , node.node_name as Node_Name " \
" , msg.application as Application " \
" , msg.object as Object " \
" , msg.message_group as Message_Group " \
" , msgt.text_part as Message_Text " \
" , ann.anno_text as Last_Annotation " \
" FROM opc_hist_messages msg " \
" , opc_hist_msg_text msgt " \
" , opc_node_names node " \
" , (SELECT distinct ng.NODE_ID, realm.MSG_GROUP_NAME from OPC_OP_REALM realm, OPC_NODES_IN_GROUP ng where ng.NODE_GROUP_ID = realm.NODE_GROUP_ID AND realm.USER_ID IN (select USER_ID from OPC_USER_DATA where NAME Like '%ST_API%' )) resp " \
" , (SELECT anno.message_number message_number, atxt.text_part as anno_text " \
" FROM (SELECT message_number, MAX(anno_number) as anno_number " \
" FROM opc_hist_annotation " \
" GROUP BY message_number) manno, " \
" opc_hist_annotation anno, " \
" opc_hist_anno_text atxt " \
" WHERE manno.message_number = anno.message_number " \
" AND manno.anno_number = anno.anno_number " \
" AND anno.anno_text_id = atxt.anno_text_id " \
" AND atxt.order_number = 1) ann " \
" WHERE msg.node_id = node.node_id AND node.node_id = resp.NODE_ID " \
" AND msg.Message_Group = resp.MSG_GROUP_NAME AND msg.message_number = msgt.message_number " \
" AND msg.message_number = ann.message_number(+) " \
" AND msgt.order_number = 1 " \
" AND msg.ackn_user NOT IN ('OpC')" \
" AND msg.local_creation_time >= (sysdate - 7) " \
" UNION " \
" SELECT decode(msg.severity,1,'Unknown',2,'Normal',4,'Warning',8,'Critical',16,'Minor',32,'Major') as Severity " \
" , msg.dupl_count as Dupl_count " \
" , TO_CHAR(msg.local_creation_time,'DD-Mon-YYYY HH24:MI:SS') as Creation_Time " \
" , TO_CHAR(msg.local_last_time_received,'DD-Mon-YYYY HH24:MI:SS') as Last_Received " \
" , node.node_name as Node_Name " \
" , msg.application as Application " \
" , msg.object as Object " \
" , msg.message_group as Message_Group " \
" , msgt.text_part as Message_Text " \
" , ann.anno_text as Last_Annotation " \
" FROM OPC_ACT_MESSAGES msg " \
" , OPC_MSG_TEXT msgt " \
" , opc_node_names node " \
" , (SELECT distinct ng.NODE_ID, realm.MSG_GROUP_NAME from OPC_OP_REALM realm, OPC_NODES_IN_GROUP ng where ng.NODE_GROUP_ID = realm.NODE_GROUP_ID AND realm.USER_ID IN (select USER_ID from OPC_USER_DATA where NAME Like '%ST_API%')) resp " \
" , (SELECT anno.message_number message_number, atxt.text_part as anno_text " \
" FROM (SELECT message_number, MAX(anno_number) as anno_number " \
" FROM OPC_ANNOTATION " \
" GROUP BY message_number) manno, " \
" OPC_ANNOTATION anno, " \
" OPC_ANNO_TEXT atxt " \
" WHERE manno.message_number = anno.message_number " \
" AND manno.anno_number = anno.anno_number " \
" AND anno.anno_text_id = atxt.anno_text_id " \
" AND atxt.order_number = 1) ann " \
" WHERE msg.node_id = node.node_id " \
" AND node.node_id = resp.NODE_ID " \
" AND msg.Message_Group = resp.MSG_GROUP_NAME " \
" AND msg.message_number = msgt.message_number " \
" AND msg.message_number = ann.message_number(+) " \
" AND msgt.order_number = 1 " \
" AND msg.local_creation_time >= (sysdate - 7) " \
" ) order by Creation_Time "`
