Problem with a big Select

nschmied · Post by **nschmied** » Wed 31 Oct 2007 08:42

Hi,
We use two select in same query, a Select inside another. And the issue is that the refresh select generate by SmartQuery cross the filed owner.

The SQL is

Code: Select all

SELECT   tmp.crg_select, sta.rco_title, sta.per_short_name, sta.per_key1,
         sta.dic_type_partner_id, sta.dic_record2_id, sta.sac_bill_type,
         sta.sac_cus_comment, sta.sac_project_id, :mois_fact, 0 aquickop,
         tmp.ROWID
    FROM pro.ind_web_genfact_tmp_f tmp,
         (SELECT DISTINCT rec.rco_title, per.per_short_name, per.per_name,
                          per.per_key1, cus.dic_type_partner_id,
                          a.sac_project_id, rec.dic_record2_id,
                          DECODE (rec.dic_record2_id,
                                  'E', 'J',
                                  'H'
                                 ) sac_bill_type,
                          cus_comment sac_cus_comment
                     FROM pro.web_activity a,
                          pro.doc_record rec,
                          pro.pac_custom_partner cus,
                          pro.pac_person per,
                          pro.ind_web_activity_processed inv
                    WHERE a.web_activity_id = inv.web_activity_id(+)
                      AND inv.doc_position_id IS NULL
                      AND c_web_activity_state = ('2')
                      AND sac_task_code  'Remarque'
                      AND TO_CHAR (sac_date, 'MM.YYYY')  1123618
                      AND cus.dic_type_partner_id IN ('PCF', 'GIC')) sta
   WHERE sta.sac_project_id = tmp.sac_project_id
ORDER BY sta.rco_title

and the SQL Refresh after update genereted was

Code: Select all

SELECT TMP.CRG_SELECT AS "_0", TMP.RCO_TITLE AS "_1", TMP.PER_SHORT_NAME AS "_2", TMP.PER_KEY1 AS "_3", TMP.DIC_TYPE_PARTNER_ID 
AS "_4", TMP.DIC_RECORD2_ID AS "_5", TMP.SAC_BILL_TYPE AS "_6", TMP.SAC_CUS_COMMENT AS "_7", TMP.SAC_PROJECT_ID AS "_8" FROM 
PRO.IND_WEB_GENFACT_TMP_F TMP
WHERE
  ROWID = :Old_ROWID
Old_ROWID = 'AAAnlXAAHAABDQ/AAC'
Runtime error occurred: 904 (ORA-00904: "TMP"."SAC_CUS_COMMENT": invalid identifier)

the owner of SAC_CUS_COMMENT is not TMP, but SAC. I don't know why TSmartQuery cross the table Owner.

Have you a WorkArround ?
Thx

Plash · Post by **Plash** » Thu 01 Nov 2007 08:22

We have fixed this problem. The fix will be included in the next build of ODAC.

By default TSmartQuery generates SQLRefresh to refresh only fields that belong to the updating table. If you need to refresh all fields, set the FullRefresh option of TSmartQuery to True. In this case the problem should not occur.

If you need to refresh only fields from the updating table, assign the following statement to the SQLRefresh property of TSmartQuery:

Code: Select all

SELECT tmp.crg_select
FROM pro.ind_web_genfact_tmp_f tmp
WHERE 
  ROWID = :Old_ROWID

nschmied · Post by **nschmied** » Fri 02 Nov 2007 07:10

Ho nice
thank you for your reactivity.
It's a pleasure to work with you.