oracle select count from union selection

Question

Simply I need to get total comments. So my query needs select and count. Here is my sql. I have problem with total_comments

SELECT customid, 
       dt, 
       customer_name, 
       e_mail, 
       phone, 
       TYPE, 
       message, 
       crm_status_id,
       total_comments
FROM   (SELECT f.ROWID                                AS customid, 
               f.dt                                   AS d, 
               To_char(f.dt, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
               f.first_name 
               || ' ' 
               || f.middle_name 
               || ' ' 
               || f.last_name                         AS customer_name, 
               f.e_mail, 
               f.phone, 
               Decode(f.feedback_type, 'CALLBACK', 'CALLBACK', 
                                       'FEEDBACK')    AS TYPE, 
               f.feedback_message                     AS message, 
               f.crm_status_id                        AS crm_status_id,
               (SELECT COUNT(*) FROM CRM_FEEDBACK_COMMENTS WHERE CRM_FEEDBACK_COMMENTS.FEEDBACK_ROW_ID = f.customid) as total_comments
        FROM   crm_feedback f 
        UNION ALL 
        SELECT g.ROWID                                       AS customid, 
               create_date                                   AS d, 
               To_char(create_date, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
               c.customer_name1                              AS customer_name, 
               p.email, 
               p.phone, 
               'MOBILSHOBE FEEDBACK'                         AS TYPE, 
               g.message_body                                AS message, 
               g.crm_status_id                               AS crm_status_id,
               (SELECT COUNT(*) FROM CRM_FEEDBACK_COMMENTS WHERE FEEDBACK_COMMENTS.FEEDBACK_ROW_ID = g.customid) as total_comments             
        FROM   feedback g 
               inner join sttm_customer@fcc_dblink c 
                       ON c.customer_no = g.customer_no 
               inner join pg2_customer@fcc_dblink p 
                       ON p.cif = g.customer_no) 

I got this error: [Err] ORA-00904: "F"."CUSTOMID": invalid identifier

Answer 1

Your table CRM_FEEDBACK_COMMENTS uses CRM_FEEDBACK.rowid as a foreign key. This is poor design for several reasons.

1. It's not a real foreign key because we can't build an actual constraint referencing the parent ROWID.
2. ROWID is not a guaranteed value: rows can move (albeit rarely) in which case your child records are orphaned.
3. It's a pain in the neck to write joins, as you're discovering.

ORA-00904: "F"."CUSTOMID": invalid identifier

You get this error message because we cannot pass a column alias into a scalar cursor. To solve your original problem you need to bring CRM_FEEDBACK.rowid into the same scope was CRM_FEEDBACK_COMMENTS.FEEDBACK_ROW_ID. You can do this with an inline view (that is, a subquery in the FROM clause):

   SELECT g.ROWID                                       AS customid, 
           create_date                                   AS d, 
           To_char(create_date, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
           c.customer_name1                              AS customer_name, 
           p.email, 
           p.phone, 
           'MOBILSHOBE FEEDBACK'                         AS TYPE, 
           g.message_body                                AS message, 
           g.crm_status_id                               AS crm_status_id,
    FROM   feedback g 
           left outer join 
               (SELECT FEEDBACK_ROW_ID, COUNT(*) as total_comments  
                FROM CRM_FEEDBACK_COMMENTS 
                group by FEEDBACK_ROW_ID) comm
                on chartorowid(comm.FEEDBACK_ROW_ID) = g.ROWID       
           inner join sttm_customer@fcc_dblink c 
                   ON c.customer_no = g.customer_no 
           inner join pg2_customer@fcc_dblink p 
                   ON p.cif = g.customer_no)