|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/3/2006 8:25:00 AM
Posts: 2,
Visits: 1
|
|
Can anyone explain to me what the actual definition of this error is? I have taken a look at the data and nothing looks out of the ordinary. I am creating a couple of volatile tables and updating one of them with info from the other. Again nothing from t2 looks erroneous in such a way that the error would not update t1.
Code:
CREATE Volatile TABLE B2B_Subs
,no log
AS (
SELECT
cal.Sor_id
, m.area_desc AS Area
, m.region_desc AS Region
, cal.cust_id
, cal.cust_line_seq_id
, cal.acct_num
, NULL AS NAICS_Nm
, NULL AS emp_tot_num
/* September 21, 2005 cals.segmt_value AS Segment */
, sdct.sls_dist_chnl_type_desc AS Channel
, ca.duns_loc_num
, NULL AS CntofUltNum /* September 21, 2005 */
, count(cal.mtn) AS CntofMTN
FROM cust_acct_line_v cal
INNER JOIN market_v m
ON cal.sor_id = m.sor_id
AND cal.mkt_cd = m.mkt_cd
INNER JOIN cust_acct_v ca
ON cal.sor_id = ca.sor_id
AND cal.cust_id = ca.cust_id
AND cal.acct_num = ca.acct_num
AND ca.duns_conf_ind > 5
INNER JOIN sales_dist_channel_type_V sdct
ON cal.sor_id = sdct.sor_id
AND cal.sls_dist_Chnl_type_cd = sdct.sls_Dist_chnl_type_cd
AND sdct.sls_dist_Chnl_churn_ctgry = 'Outside'
/* September 21, 2005 Pulled due to issue with Segment codes - Customer Segmentation
LEFT OUTER JOIN cust_acct_line_segment_v cals
ON cal.sor_id = cals.sor_id
AND cal.cust_id = cals.cust_id
AND cal.cust_line_seq_id = cals.cust_line_seq_id
AND add_months(cast('2005-08-01' AS date),1)-1 BETWEEN cals.eff_dt
AND cals.exp_dt
AND segmt_type_cd = 'HRCHY_SEGMT' */
WHERE (cal.line_act_dt BETWEEN 1050801
AND 1050831)
AND (cal.line_term_dt is NULL
OR cal.line_term_dt > (add_months(cast('2005-08-01' AS date),1)-1))
AND cal.sor_id IN ('I','V')
AND cal.rev_gen_ind = 'Y'
GROUP BY cal.sor_id, m.area_desc, m.region_desc, cal.cust_id, cal.cust_line_seq_id, cal.acct_num, naics_nm, emp_tot_num,
CntofUltNum, sdct.sls_dist_chnl_type_desc, ca.duns_loc_num)
WITH DATA
PRIMARY INDEX ( Area, Sor_id, Cust_id, Cust_line_seq_id)
ON Commit Preserve Rows
;
CREATE Volatile TABLE duns
,no log
AS (
SELECT duns1.duns_loc_num duns_loc_num1, duns1.bus_nm bus_nm1, duns1.domestic_emp_tot_num loc_emp_tot, duns2.bus_nm bus_nm2
,
CASE
WHEN duns1.naics_cd1 like ANY ('42%','44%','45%') THEN 'Distribution'
WHEN duns1.naics_cd1 like '62%' THEN 'Healthcare'
WHEN duns1.naics_cd1 like '53%' THEN 'Real_Estate'
WHEN duns1.naics_cd1 like '3254%' THEN 'Pharma'
WHEN duns1.naics_cd1 like ANY ('31%' ,'321%' ,'322%' ,'323%' ,'334%' ,'3251%' ,'3252%' ,'3253%' ,'3255%' ,'3256%' ,'3259%', '326%' ,'327%', '33%') THEN 'Indust_Manu'
WHEN duns1.naics_cd1 like '51%' THEN 'Media'
WHEN duns1.naics_cd1 like '5411%' THEN 'ProfSvc_Legal'
WHEN duns1.naics_cd1 like ANY ('5412%' ,'5413%' ,'5414%' ,'5415%' ,'5416%' ,'5417%','5418%' ,'5419%') THEN 'Prof_Svc'
WHEN duns1.naics_cd1 like '524%' THEN 'Insurance'
WHEN duns1.naics_cd1 like ANY ('521%','522%','523%','525%' )Then 'Invest_Bank'
WHEN duns1.naics_cd1 like ANY ('492%','484%') THEN 'Transport'
WHEN duns1.naics_cd1 like '22%' THEN 'Utilities'
WHEN duns1.naics_cd1 like '61%' THEN 'Education'
WHEN duns1.naics_cd1 like '23%' THEN 'Construction'
WHEN duns1.naics_cd1 like '922%' THEN 'Safety_Law'
WHEN duns1.naics_cd1 like ANY ('921%' ,'923%','924%' ,'925%','926%' ,'927%','928%') THEN 'Government'
ELSE 'Other'
END NAICS_Desc1 /* Oct 5, 2005 changed again to naics_cd1 Sep. 20, 2005 - changed from sic1_cd to sic1_master_grp_cd */
, count(DISTINCT(duns1.duns_ult_num)) ult_num1
FROM
duns_v duns1
LEFT OUTER JOIN duns_v duns2
ON coalesce(duns1.duns_ult_num, duns1.duns_hq_num, duns1.duns_loc_num) = duns2.duns_loc_num
WHERE duns1.duns_loc_num IN (
SELECT duns_loc_num
FROM B2B_Subs)
AND duns1.domestic_emp_tot_num BETWEEN 50
AND 499
GROUP BY 1,2,3,4,5
)
WITH DATA PRIMARY INDEX ( DUNS_LOC_NUM1)
ON Commit Preserve Rows
;
-----------------Error occurs in this code------------------------
UPDATE B2B_Subs
FROM (
SELECT duns_loc_num1 ,bus_nm2, loc_emp_tot, naics_desc1,ult_num1
FROM duns) x
SET emp_tot_num = loc_emp_tot, NAICS_NM = NAICS_DESC1, CntofUltNum = ult_num1
WHERE duns_loc_num = duns_loc_num1
AND bus_nm2 is NOT NULL
;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/23/2006 10:44:00 PM
Posts: 3,
Visits: 1
|
|
Hi,
I hope that following modified query would work fine, because you missed out the reference name of the table.
UPDATE B2B_Subs
FROM (
SELECT duns_loc_num1 ,bus_nm2, loc_emp_tot, naics_desc1,ult_num1
FROM duns) x
SET emp_tot_num = x.loc_emp_tot, NAICS_NM = x.NAICS_DESC1, CntofUltNum = x.ult_num1
WHERE B2B_Subs.duns_loc_num = x.duns_loc_num1
AND B2B_Subs.bus_nm2 is NOT NULL
;
Please revert and confirm it.
Thanks
Vijay
Thanks and Regards Vijay
|
|
|
|