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
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
Tuesday, May 5, 2015
Aggreagating rows in oracle per group using LISTAGG (version 11 and on)
Subscribe to:
Posts (Atom)