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)

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