Tuesday, May 5, 2015

Aggreagating rows in oracle per group using LISTAGG (version 11 and on)

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;
        }

No comments:

Post a Comment