SELECT DISTINCT
        cru_revpermaid.cru_proj_key || '-' || cru_revpermaid.cru_number AS "Review Key",
        TO_CHAR(TO_TIMESTAMP(cru_review.cru_create_date / 1000), 'MM-DD-YYYY') AS "Review Creation Date",
        cc.cru_comment_id AS "Comment ID",
        cru_user_comment_author.cru_user_name AS "Comment Author", 
        (CASE
                WHEN cru_comment_read_status.cru_read IS TRUE 
                THEN 'Read'
                ELSE 'Unread'
        END) AS "Comment Status",
        cc.cru_message AS "Comment Message",
        COALESCE (
                cru_stored_path_inline.cru_path, 
                cru_stored_path_frx.cru_path, 
                CASE
                        WHEN cc.cru_reply_to_comment_id IS NOT NULL 
                        THEN '<REPLY TO COMMENT ID: ' || cc.cru_reply_to_comment_id || '>' 
                        ELSE '<GENERAL COMMENTS>' END
                ) AS "File Path",
        cru_inline_comment_to_frx_rev.cru_line_range AS "Line Range",
        
        (SELECT (
                BTRIM(
                        XPATH(
                                '/n:metrics/n:comment/n:field[@name=''classification'']/n:select/n:option[@value='|| cru_field.cru_int_val ||']/text()', 
                                cru_metric_definition.cru_xml::xml, 
                                '{{n,http://www.cenqua.com/crucible/metrics-1}}'
                        )::text,
                '{""}'))
        FROM cru_metric_definition 
                JOIN cru_field ON cru_metric_definition.cru_metric_id = cru_field.cru_config_version
                JOIN cru_comment_field ON cru_field.cru_field_id = cru_comment_field.cru_field_id
                JOIN cru_comment c ON c.cru_comment_id = cru_comment_field.cru_comment_id
        WHERE c.cru_comment_id = cc.cru_comment_id
        AND cru_field.cru_name = 'classification'
        ) AS "Classification",
        
        (SELECT (
                BTRIM(
                        XPATH(
                                '/n:metrics/n:comment/n:field[@name=''rank'']/n:select/n:option[@value='|| cru_field.cru_int_val ||']/text()', 
                                cru_metric_definition.cru_xml::xml, 
                                '{{n,http://www.cenqua.com/crucible/metrics-1}}'
                        )::text, 
                '{""}'))
        FROM cru_metric_definition 
                JOIN cru_field ON cru_metric_definition.cru_metric_id = cru_field.cru_config_version
                JOIN cru_comment_field ON cru_field.cru_field_id = cru_comment_field.cru_field_id
                JOIN cru_comment c ON c.cru_comment_id = cru_comment_field.cru_comment_id
        WHERE c.cru_comment_id = cc.cru_comment_id
        AND cru_field.cru_name = 'rank'
        ) AS "Ranking" 
FROM cru_comment AS cc
        LEFT JOIN cru_comment_field ON cru_comment_field.cru_comment_id = cc.cru_comment_id
        LEFT JOIN cru_field ON cru_comment_field.cru_field_id = cru_field.cru_field_id
        LEFT JOIN cru_metric_definition ON cru_metric_definition.cru_metric_id = cru_field.cru_config_version
        LEFT JOIN cru_comment_read_status ON cru_comment_read_status.cru_comment = cc.cru_comment_id
        LEFT JOIN cru_user cru_user_comment_author ON cru_user_comment_author.cru_user_id = cc.cru_user_id
        LEFT JOIN cru_review ON cru_review.cru_review_id = cc.cru_review_id
        LEFT JOIN cru_revpermaid ON cru_revpermaid.cru_review_id = cru_review.cru_review_id
        LEFT JOIN cru_inline_comment ON cru_inline_comment.cru_comment_id = cc.cru_comment_id -- JOINs only if inline comment
        LEFT JOIN cru_inline_comment_to_frx_rev ON cru_inline_comment_to_frx_rev.cru_inline_comment_id = cru_inline_comment.cru_comment_id
        LEFT JOIN cru_frx_revision cru_frx_revision_inline ON cru_frx_revision_inline.cru_frx_rev_id = cru_inline_comment_to_frx_rev.cru_frx_rev_id
        LEFT JOIN cru_revision cru_revision_inline ON cru_revision_inline.cru_revision_id = cru_frx_revision_inline.cru_revision
        LEFT JOIN cru_stored_path cru_stored_path_inline ON cru_stored_path_inline.cru_path_id = cru_revision_inline.cru_path
        LEFT JOIN cru_frx_comment ON cru_frx_comment.cru_comment_id = cc.cru_comment_id -- JOINs only if frx comment
        LEFT JOIN cru_frx_revision cru_frx_revision_frx ON cru_frx_revision_frx.cru_frx_id = cru_frx_comment.cru_frx_id
        LEFT JOIN cru_revision cru_revision_frx ON cru_revision_frx.cru_revision_id = cru_frx_revision_frx.cru_revision
        LEFT JOIN cru_stored_path cru_stored_path_frx ON cru_stored_path_frx.cru_path_id = cru_revision_frx.cru_path
WHERE NOT cc.cru_deleted
        AND NOT cc.cru_draft
        AND TO_TIMESTAMP(cru_review.cru_create_date / 1000) >= '9-25-2020'
        AND cru_revpermaid.cru_proj_key LIKE 'CR' -- use this to restrict by project key
        AND cru_revpermaid.cru_number = 1 -- use this to restrict by review number
ORDER BY "Review Key"

Review Key  Review Creation Date  Comment ID  Comment Author  Comment Status  Comment Message                   File Path                 Line Range  Classification  Ranking  
----------  --------------------  ----------  --------------  --------------  --------------------------------  ------------------------  ----------  --------------  -------  
CR-1        10-12-2020            1           user1           Read            Comment in a single line          file2.txt                 24          Ambiguous       Major    
CR-1        10-12-2020            2           user2           Read            Comment in line range             file2.txt                 27-31       Inconsistent    Minor    
CR-1        10-12-2020            3           user3           Read            Comment in another file           file.txt                  2           Missing         Major    
CR-1        10-12-2020            4           user1           Read            General comment                   <GENERAL COMMENTS>        (null)      (null)          (null)   
CR-1        10-12-2020            5           user1           Read            Reply to comment in single line   <REPLY TO COMMENT ID: 1>  (null)      (null)          (null)   
CR-1        10-12-2020            6           user1           Read            Reply to comment in line range    <REPLY TO COMMENT ID: 2>  (null)      (null)          (null)