Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-09-2006
09:51 PM

01-09-2006
09:51 PM

Hi,

I am new to Teradata, and I need to do a SQL update. Based on the Syntax given in the Manual, I did write this SQL, but somehow this doesn't seem to work. Please let me know where I am going wrong?

Thanks in Advance.

UPDATE MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST

from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,

a11.PRD_GRP_NBR PRD_GRP_NBR,

a12.CMA_LCT_CD CMA_LCT_CD,

sum(a11.TOT_SAL_AMT) SALES,

sum(a11.TOT_CST_AMT) COST

from ABC.PGP_SMY a11

join ABC.LCT_HST a12

on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and

a11.LCT_NBR = a12.LCT_NBR)

group by a12.FSC_WK_END_DT,

a11.PRD_GRP_NBR,

a12.CMA_LCT_CD

) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b

set TBL_PERF_HIST_2_TEST.CMA_LCT_CD = a.cma_lct_cd, TBL_PERF_HIST_2_TEST.TOT_SAL_AMT = a.SALES, TBL_PERF_HIST_2_TEST.TOT_CST_AMT = A.COST

where b.fsc_wk_end_dt = a.fsc_wk_end_dt

and b.prd_grp_nbr = a.prd_grp_nbr

Regards

I am new to Teradata, and I need to do a SQL update. Based on the Syntax given in the Manual, I did write this SQL, but somehow this doesn't seem to work. Please let me know where I am going wrong?

Thanks in Advance.

UPDATE MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST

from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,

a11.PRD_GRP_NBR PRD_GRP_NBR,

a12.CMA_LCT_CD CMA_LCT_CD,

sum(a11.TOT_SAL_AMT) SALES,

sum(a11.TOT_CST_AMT) COST

from ABC.PGP_SMY a11

join ABC.LCT_HST a12

on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and

a11.LCT_NBR = a12.LCT_NBR)

group by a12.FSC_WK_END_DT,

a11.PRD_GRP_NBR,

a12.CMA_LCT_CD

) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b

set TBL_PERF_HIST_2_TEST.CMA_LCT_CD = a.cma_lct_cd, TBL_PERF_HIST_2_TEST.TOT_SAL_AMT = a.SALES, TBL_PERF_HIST_2_TEST.TOT_CST_AMT = A.COST

where b.fsc_wk_end_dt = a.fsc_wk_end_dt

and b.prd_grp_nbr = a.prd_grp_nbr

Regards

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-10-2006
04:32 AM

01-10-2006
04:32 AM

The following is the working version of the SQL:

UPDATE b

from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,

a11.PRD_GRP_NBR PRD_GRP_NBR,

a12.CMA_LCT_CD CMA_LCT_CD,

sum(a11.TOT_SAL_AMT) SALES,

sum(a11.TOT_CST_AMT) COST

from ABC.PGP_SMY a11

join ABC.LCT_HST a12

on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and

a11.LCT_NBR = a12.LCT_NBR)

group by a12.FSC_WK_END_DT,

a11.PRD_GRP_NBR,

a12.CMA_LCT_CD

) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b

set CMA_LCT_CD = a.cma_lct_cd,

TOT_SAL_AMT = a.SALES,

TOT_CST_AMT = A.COST

where b.fsc_wk_end_dt = a.fsc_wk_end_dt

and b.prd_grp_nbr = a.prd_grp_nbr

UPDATE b

from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,

a11.PRD_GRP_NBR PRD_GRP_NBR,

a12.CMA_LCT_CD CMA_LCT_CD,

sum(a11.TOT_SAL_AMT) SALES,

sum(a11.TOT_CST_AMT) COST

from ABC.PGP_SMY a11

join ABC.LCT_HST a12

on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and

a11.LCT_NBR = a12.LCT_NBR)

group by a12.FSC_WK_END_DT,

a11.PRD_GRP_NBR,

a12.CMA_LCT_CD

) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b

set CMA_LCT_CD = a.cma_lct_cd,

TOT_SAL_AMT = a.SALES,

TOT_CST_AMT = A.COST

where b.fsc_wk_end_dt = a.fsc_wk_end_dt

and b.prd_grp_nbr = a.prd_grp_nbr

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-10-2006
02:54 PM

01-10-2006
02:54 PM

Thank You.

Regards

Ravi

Regards

Ravi