protected T GetExistingImprovementFullData(string policyOrAccountNo, bool isLifeTrack, string usf, int taskNumber, string taskSpecificTableName, bool discardAlreadyApproved = false) where T : OraBaseImprovement, new() { T completeExistingImprovementData = new T(); string query = string.Format(@" SELECT * FROM (SELECT imp.improvement_id, imp.record_status, imp.user_comments, imp.usf_number, imp.task_number, imp.policy_number, imp.provident_fund_account_number, imp.improver_status, imp.improver_sub_status, imp.manager_batchly_approved, imp.manager_manually_approved, imp.manager_approval_date, imp.usf_manager, imp.create_date, imp.update_date, imp.tv08_status, LISTAGG('[' || ADAdocs.document_id || ',' || TRIM(ADAdocs.Doc_Type)|| ',' || TO_CHAR(ADAdocs.Attachment_Date, 'dd/mm/yyyy hh24:mi:ss') || ']', ',') WITHIN GROUP(ORDER BY impDocs.Document_Id) as documents FROM pia_improvements imp LEFT JOIN pia_improvement_attachments impDocs ON impDocs.improvement_id = imp.improvement_id LEFT JOIN pia_ada_attached_documents ADAdocs ON ADAdocs.document_id = impDocs.document_id WHERE {0} = '{1}' AND imp.usf_number = '{2}' AND imp.task_number = {3} AND imp.record_status = 1 /* improvement is active */ {4} GROUP BY imp.improvement_id, imp.record_status, imp.user_comments, imp.usf_number, imp.task_number, imp.policy_number, imp.provident_fund_account_number, imp.improver_status, imp.improver_sub_status, imp.manager_batchly_approved, imp.manager_manually_approved, imp.manager_approval_date, imp.usf_manager, imp.create_date, imp.update_date, imp.tv08_status ) baseImp JOIN {5} task ON task.improvement_id = baseImp.improvement_id ", DetermineWhereColumnNameBasedOnTrack(isLifeTrack), policyOrAccountNo, usf, taskNumber, discardAlreadyApproved ? GetDiscardImprovementAlreadyApprovedSQL() : string.Empty, taskSpecificTableName); DataTable improvementDataTable = ExecuteDataTable(query); if (improvementDataTable.Rows.Count > 0) { completeExistingImprovementData = OracleMapper .MapDataRowToModel(improvementDataTable.Rows[0]); if (!string.IsNullOrEmpty(completeExistingImprovementData.USER_COMMENTS)) { completeExistingImprovementData.USER_COMMENTS = HebUtils.Ascii7BitToUnicode(completeExistingImprovementData.USER_COMMENTS); } } return completeExistingImprovementData; }
Keeping track of interesting places I put my feet on in the software development world.
Search This Blog
Tuesday, May 5, 2015
Aggreagating rows in oracle per group using LISTAGG (version 11 and on)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment