Numeric Value is not recognized when INSERT data

asked 2020-09-22T00:30:42.277+00:00
bk 436 Reputation points

Hi All
I am trying to INSERT data into a table using the format
INSERT INTO TABLE (COLUMNS)
WITH CTE AS
(SELECT STATEMENT)
SELECT *
FROM CTE
When i execute i get the following error
Numeric Value 'Week 5 of Fiscal Month' is not recognized.
Please see the attached SQL. I looked up and my SQL doesn't have that field. Please advice
INSERT INTO FISCAL_WEEK_NEW (

               FISCAL_WEEK_ID,

               FISCAL_WEEK_DATE_ID,

               FISCAL_MONTH_ID,

               FISCAL_QUARTER_ID,

               FISCAL_SEASON_ID,

               FISCAL_YEAR_ID,

               FIRST_DATE_IN_FISCAL_MONTH,

               LAST_DATE_IN_FISCAL_MONTH,

               FISCAL_MONTH_LAST_YEAR,

               FISCAL_MONTH_2_YEAR_AGO,

               FIRST_DATE_IN_FISCAL_QUARTER,

               LAST_DATE_IN_FISCAL_QUARTER,

               FISCAL_QUARTER_LAST_YEAR,

               FISCAL_QUARTER_2_YEAR_AGO,

               FIRST_DATE_IN_FISCAL_SEASON,

               LAST_DATE_IN_FISCAL_SEASON ,

               FISCAL_SEASON_LAST_YEAR,

               FISCAL_SEASON_2_YEAR_AGO,

               FIRST_DATE_IN_FISCAL_YEAR,

               LAST_DATE_IN_FISCAL_YEAR,

               FISCAL_YEAR_LAST_YEAR,

               FISCAL_YEAR_2_YEAR_AGO,

               FISCAL_WEEK_SEQUENCE_NUM,

               FISCAL_WEEK_DISPLAY_NUM,

               FISCAL_WEEK_DESC,

               FISCAL_WEEK_SHORT_DESC,

               FIRST_DATE_IN_FISCAL_WEEK,

               LAST_DATE_IN_FISCAL_WEEK,

               FISCAL_WEEK_ID_LAST_YEAR,

               FISCAL_WEEK_ID_2_YEAR_AGO,

               FISCAL_WEEK_ID_LAST_WEEK,

               FISCAL_WEEK_ID_2_WEEK_AGO,

               FISCAL_WEEK_ID_3_WEEK_AGO,

               FISCAL_WEEK_ID_4_WEEK_AGO,

               FISCAL_MONTH_SEQUENCE_NUM,

               FISCAL_WEEK_IN_YEAR_NUM,

               FISCAL_WEEK_IN_MONTH_NUM,

               FISCAL_WEEK_IN_MONTH_NAME,

               FISCAL_WEEK_IN_QUARTER_NUM,

               FISCAL_WEEK_IN_QUARTER_NAME,

               FISCAL_WEEK_CURRENT_IND,

               LAST_COMPLETE_1_FISCAL_WEEK_IND,

               LAST_COMPLETE_2_FISCAL_WEEK_IND,

               LAST_COMPLETE_3_FISCAL_WEEK_IND,

               LAST_COMPLETE_4_FISCAL_WEEK_IND,

               LAST_COMPLETE_5_FISCAL_WEEK_IND,

               LAST_COMPLETE_6_FISCAL_WEEK_IND,

               LAST_COMPLETE_7_FISCAL_WEEK_IND,

               LAST_COMPLETE_8_FISCAL_WEEK_IND,

               LAST_COMPLETE_9_FISCAL_WEEK_IND,

               LAST_COMPLETE_10_FISCAL_WEEK_IND,

               LAST_COMPLETE_11_FISCAL_WEEK_IND,

               LAST_COMPLETE_12_FISCAL_WEEK_IND,

               FISCAL_WEEK_ID_5_WEEK_AGO,

               FISCAL_WEEK_ID_6_WEEK_AGO,

               FISCAL_WEEK_ID_7_WEEK_AGO,

               FISCAL_WEEK_ID_8_WEEK_AGO,

               FISCAL_WEEK_ID_9_WEEK_AGO,

               FISCAL_WEEK_ID_10_WEEK_AGO,

               FISCAL_WEEK_ID_11_WEEK_AGO,

               FISCAL_WEEK_ID_12_WEEK_AGO,

               FISCAL_WEEK_ID_13_WEEK_AGO,

               FISCAL_WEEK_ID_14_WEEK_AGO,

               FISCAL_WEEK_ID_15_WEEK_AGO,

               FISCAL_WEEK_ID_16_WEEK_AGO,

               FISCAL_WEEK_ID_17_WEEK_AGO,

               FISCAL_WEEK_ID_18_WEEK_AGO,

               FISCAL_WEEK_ID_19_WEEK_AGO,

               FISCAL_WEEK_ID_20_WEEK_AGO,

               FISCAL_WEEK_ID_21_WEEK_AGO,

               FISCAL_WEEK_ID_22_WEEK_AGO,

               FISCAL_WEEK_ID_23_WEEK_AGO,

               FISCAL_WEEK_ID_24_WEEK_AGO,

               FISCAL_WEEK_ID_25_WEEK_AGO,

               FISCAL_WEEK_ID_26_WEEK_AGO,

               FISCAL_WEEK_ID_27_WEEK_AGO,

               FISCAL_WEEK_ID_28_WEEK_AGO,

               FISCAL_WEEK_ID_29_WEEK_AGO,

               FISCAL_WEEK_ID_30_WEEK_AGO,

               FISCAL_WEEK_ID_31_WEEK_AGO,

               FISCAL_WEEK_ID_32_WEEK_AGO,

               FISCAL_WEEK_ID_33_WEEK_AGO,

               FISCAL_WEEK_ID_34_WEEK_AGO,

               FISCAL_WEEK_ID_35_WEEK_AGO,

               FISCAL_WEEK_ID_36_WEEK_AGO,

               FISCAL_WEEK_ID_37_WEEK_AGO,

               FISCAL_WEEK_ID_38_WEEK_AGO,

               FISCAL_WEEK_ID_39_WEEK_AGO,

               FISCAL_WEEK_ID_40_WEEK_AGO,

               FISCAL_WEEK_ID_41_WEEK_AGO,

               FISCAL_WEEK_ID_42_WEEK_AGO,

               FISCAL_WEEK_ID_43_WEEK_AGO,

               FISCAL_WEEK_ID_44_WEEK_AGO,

               FISCAL_WEEK_ID_45_WEEK_AGO,

               FISCAL_WEEK_ID_46_WEEK_AGO,

               FISCAL_WEEK_ID_47_WEEK_AGO,

               FISCAL_WEEK_ID_48_WEEK_AGO,

               FISCAL_WEEK_ID_49_WEEK_AGO,

               FISCAL_WEEK_ID_50_WEEK_AGO,

               FISCAL_WEEK_ID_51_WEEK_AGO,

               FISCAL_WEEK_ID_52_WEEK_AGO,

               FISCAL_WEEK_ID_3_YEAR_AGO,

               FISCAL_WEEK_ID_4_YEAR_AGO,

               FISCAL_WEEK_ID_5_YEAR_AGO,

               FISCAL_WEEK_ID_6_YEAR_AGO,

               FISCAL_WEEK_ID_7_YEAR_AGO,

               FISCAL_WEEK_ID_8_YEAR_AGO,

               FISCAL_WEEK_ID_9_YEAR_AGO,

               FISCAL_WEEK_ID_10_YEAR_AGO,

               FISCAL_WEEK_ID_1_QUARTER_AGO,

               FISCAL_WEEK_ID_2_QUARTER_AGO,

               FISCAL_WEEK_ID_3_QUARTER_AGO,

               FISCAL_WEEK_ID_4_QUARTER_AGO,

               FISCAL_WEEK_ID_5_QUARTER_AGO,

               FISCAL_WEEK_ID_6_QUARTER_AGO,

               FISCAL_WEEK_ID_7_QUARTER_AGO,

               FISCAL_WEEK_ID_8_QUARTER_AGO,

               FISCAL_WEEK_ID_9_QUARTER_AGO,

               FISCAL_WEEK_ID_10_QUARTER_AGO,

               FISCAL_WEEK_ID_1_MONTH_AGO,

               FISCAL_WEEK_ID_2_MONTH_AGO,

               FISCAL_WEEK_ID_3_MONTH_AGO,

               FISCAL_WEEK_ID_4_MONTH_AGO,

               FISCAL_WEEK_ID_5_MONTH_AGO,

               FISCAL_WEEK_ID_6_MONTH_AGO,

               FISCAL_WEEK_ID_7_MONTH_AGO,

               FISCAL_WEEK_ID_8_MONTH_AGO,

               FISCAL_WEEK_ID_9_MONTH_AGO,

               FISCAL_WEEK_ID_10_MONTH_AGO,

               FISCAL_WEEK_ID_11_MONTH_AGO,

               FISCAL_WEEK_ID_12_MONTH_AGO,

               FISCAL_WEEK_ID_13_MONTH_AGO,

               FISCAL_WEEK_ID_LAST_YEAR_2_WEEK_AGO,

               FISCAL_WEEK_ID_LAST_YEAR_3_WEEK_AGO,

               FISCAL_WEEK_ID_LAST_YEAR_4_WEEK_AGO,

               FISCAL_WEEK_ID_LAST_YEAR_5_WEEK_AGO,

               CREATE_PROCESS_CD,

               CREATE_USER,

               CREATE_TS,

               UPDATE_PROCESS_CD,

               UPDATE_USER,

               UPDATE_TS

)

with cte as

(



SELECT DISTINCT A.fiscal_week_id,

                A.fiscal_week_date_id,

                A.fiscal_month_id,

                A.fiscal_quarter_id,

                A.fiscal_season_id,

                A.fiscal_year_id,

                a.first_date_in_fiscal_month,

                A.last_date_in_fiscal_month,

                A.fiscal_month_last_year,

                A.fiscal_month_2_year_ago,

                A.first_date_in_fiscal_quarter,

                A.last_date_in_fiscal_quarter,

                A.fiscal_quarter_last_year,

                A.fiscal_quarter_2_year_ago,

                A.first_date_in_fiscal_season,

                A.last_date_in_fiscal_season,

                A.fiscal_season_last_year,

                A.fiscal_season_2_year_ago,

                A.first_date_in_fiscal_year,

                A.last_date_in_fiscal_year,

                A.fiscal_year_last_year,

                A.fiscal_year_2_year_ago,

                A.fiscal_week_sequence_num,

                A.fiscal_week_display_num,

                A.fiscal_week_desc,

                A.fiscal_week_short_desc,

                A.first_date_in_fiscal_week,

                A.last_date_in_fiscal_week,

                A.fiscal_month_sequence_num,

                A.fiscal_week_in_year_num,

                A.fiscal_week_in_month_num,

                A.fiscal_week_in_month_name,

                A.fiscal_week_in_quarter_num,

                A.fiscal_week_in_quarter_name,

                A.fiscal_week_current_ind,

                A.last_complete_1_fiscal_week_ind,

                A.last_complete_2_fiscal_week_ind,

                A.last_complete_3_fiscal_week_ind,

                A.last_complete_4_fiscal_week_ind,

                A.last_complete_5_fiscal_week_ind,

                A.last_complete_6_fiscal_week_ind,

                A.last_complete_7_fiscal_week_ind,

                A.last_complete_8_fiscal_week_ind,

                A.last_complete_9_fiscal_week_ind,

                A.last_complete_10_fiscal_week_ind,

                A.last_complete_11_fiscal_week_ind,

                A.last_complete_12_fiscal_week_ind,

                COALESCE(LW1.fiscal_week_id, 0)  fiscal_week_id_last_week,

                COALESCE(LW2.fiscal_week_id, 0)  fiscal_week_id_2_week_ago,

                COALESCE(LW3.fiscal_week_id, 0)  fiscal_week_id_3_week_ago,

                COALESCE(LW4.fiscal_week_id, 0)  fiscal_week_id_4_week_ago,

                COALESCE(LW5.fiscal_week_id, 0)  fiscal_week_id_5_week_ago,

                COALESCE(LW6.fiscal_week_id, 0)  fiscal_week_id_6_week_ago,

                COALESCE(LW7.fiscal_week_id, 0)  fiscal_week_id_7_week_ago,

                COALESCE(LW8.fiscal_week_id, 0)  fiscal_week_id_8_week_ago,

                COALESCE(LW9.fiscal_week_id, 0)  fiscal_week_id_9_week_ago,

                COALESCE(LW10.fiscal_week_id, 0) fiscal_week_id_10_week_ago,

                COALESCE(LW11.fiscal_week_id, 0) fiscal_week_id_11_week_ago,

                COALESCE(LW12.fiscal_week_id, 0) fiscal_week_id_12_week_ago,

                COALESCE(LW13.fiscal_week_id, 0) fiscal_week_id_13_week_ago,

                COALESCE(LW14.fiscal_week_id, 0) fiscal_week_id_14_week_ago,

                COALESCE(LW15.fiscal_week_id, 0) fiscal_week_id_15_week_ago,

                COALESCE(LW16.fiscal_week_id, 0) fiscal_week_id_16_week_ago,

                COALESCE(LW17.fiscal_week_id, 0) fiscal_week_id_17_week_ago,

                COALESCE(LW18.fiscal_week_id, 0) fiscal_week_id_18_week_ago,

                COALESCE(LW19.fiscal_week_id, 0) fiscal_week_id_19_week_ago,

                COALESCE(LW20.fiscal_week_id, 0) fiscal_week_id_20_week_ago,

                COALESCE(LW21.fiscal_week_id, 0) fiscal_week_id_21_week_ago,

                COALESCE(LW22.fiscal_week_id, 0) fiscal_week_id_22_week_ago,

                COALESCE(LW23.fiscal_week_id, 0) fiscal_week_id_23_week_ago,

                COALESCE(LW24.fiscal_week_id, 0) fiscal_week_id_24_week_ago,

                COALESCE(LW25.fiscal_week_id, 0) fiscal_week_id_25_week_ago,

                COALESCE(LW26.fiscal_week_id, 0) fiscal_week_id_26_week_ago,

                COALESCE(LW27.fiscal_week_id, 0) fiscal_week_id_27_week_ago,

                COALESCE(LW28.fiscal_week_id, 0) fiscal_week_id_28_week_ago,

                COALESCE(LW29.fiscal_week_id, 0) fiscal_week_id_29_week_ago,

                COALESCE(LW30.fiscal_week_id, 0) fiscal_week_id_30_week_ago,

                COALESCE(LW31.fiscal_week_id, 0) fiscal_week_id_31_week_ago,

                COALESCE(LW32.fiscal_week_id, 0) fiscal_week_id_32_week_ago,

                COALESCE(LW33.fiscal_week_id, 0) fiscal_week_id_33_week_ago,

                COALESCE(LW34.fiscal_week_id, 0) fiscal_week_id_34_week_ago,

                COALESCE(LW35.fiscal_week_id, 0) fiscal_week_id_35_week_ago,

                COALESCE(LW36.fiscal_week_id, 0) fiscal_week_id_36_week_ago,

                COALESCE(LW37.fiscal_week_id, 0) fiscal_week_id_37_week_ago,

                COALESCE(LW38.fiscal_week_id, 0) fiscal_week_id_38_week_ago,

                COALESCE(LW39.fiscal_week_id, 0) fiscal_week_id_39_week_ago,

                COALESCE(LW40.fiscal_week_id, 0) fiscal_week_id_40_week_ago,

                COALESCE(LW41.fiscal_week_id, 0) fiscal_week_id_41_week_ago,

                COALESCE(LW42.fiscal_week_id, 0) fiscal_week_id_42_week_ago,

                COALESCE(LW43.fiscal_week_id, 0) fiscal_week_id_43_week_ago,

                COALESCE(LW44.fiscal_week_id, 0) fiscal_week_id_44_week_ago,

                COALESCE(LW45.fiscal_week_id, 0) fiscal_week_id_45_week_ago,

                COALESCE(LW46.fiscal_week_id, 0) fiscal_week_id_46_week_ago,

                COALESCE(LW47.fiscal_week_id, 0) fiscal_week_id_47_week_ago,

                COALESCE(LW48.fiscal_week_id, 0) fiscal_week_id_48_week_ago,

                COALESCE(LW49.fiscal_week_id, 0) fiscal_week_id_49_week_ago,

                COALESCE(LW50.fiscal_week_id, 0) fiscal_week_id_50_week_ago,

                COALESCE(LW51.fiscal_week_id, 0) fiscal_week_id_51_week_ago,

                COALESCE(LW52.fiscal_week_id, 0) fiscal_week_id_52_week_ago,

                COALESCE(B.fiscal_week_id, 0)    FISCAL_WEEK_ID_LAST_YEAR,

                /*FWIDLY*/

                COALESCE(C.fiscal_week_id, 0)    FISCAL_WEEK_ID_2_YEAR_AGO,

                /*FWID2Y*/

                COALESCE(D.fiscal_week_id, 0)    FISCAL_WEEK_ID_3_YEAR_AGO,

                /* FWI3Y*/

                COALESCE(E.fiscal_week_id, 0)    FISCAL_WEEK_ID_4_YEAR_AGO,

                /*  FWI4Y*/

                COALESCE(F.fiscal_week_id, 0)    FISCAL_WEEK_ID_5_YEAR_AGO,

                /* FWI5Y*/

                COALESCE(G.fiscal_week_id, 0)    FISCAL_WEEK_ID_6_YEAR_AGO,

                /* FWI6Y*/

                COALESCE(H.fiscal_week_id, 0)    FISCAL_WEEK_ID_7_YEAR_AGO,

                /* FWI7Y*/

                COALESCE(I.fiscal_week_id, 0)    FISCAL_WEEK_ID_8_YEAR_AGO,

                /* FWI8Y*/

                COALESCE(J.fiscal_week_id, 0)    FISCAL_WEEK_ID_9_YEAR_AGO,

                /* FWI9Y*/

                COALESCE(K.fiscal_week_id, 0)    FISCAL_WEEK_ID_10_YEAR_AGO,

                /* FWI10Y,*/

                ----COALESCE(LW52.fiscal_week_id,0) fiscal_week_id_2_week_ago,

                COALESCE(LQ1.fiscal_week_id, 0)  fiscal_week_id_1_quarter_ago,

                COALESCE(LQ2.fiscal_week_id, 0)  fiscal_week_id_2_quarter_ago,

                COALESCE(LQ3.fiscal_week_id, 0)  fiscal_week_id_3_quarter_ago,

                COALESCE(LQ4.fiscal_week_id, 0)  fiscal_week_id_4_quarter_ago,

                COALESCE(LQ5.fiscal_week_id, 0)  fiscal_week_id_5_quarter_ago,

                COALESCE(LQ6.fiscal_week_id, 0)  fiscal_week_id_6_quarter_ago,

                COALESCE(LQ7.fiscal_week_id, 0)  fiscal_week_id_7_quarter_ago,

                COALESCE(LQ8.fiscal_week_id, 0)  fiscal_week_id_8_quarter_ago,

                COALESCE(LQ9.fiscal_week_id, 0)  fiscal_week_id_9_quarter_ago,

                COALESCE(LQ10.fiscal_week_id, 0) fiscal_week_id_10_quarter_ago,

                COALESCE(LM1.fiscal_week_id, 0)  fiscal_week_id_1_month_ago,

                COALESCE(LM2.fiscal_week_id, 0)  fiscal_week_id_2_month_ago,

                COALESCE(LM3.fiscal_week_id, 0)  fiscal_week_id_3_month_ago,

                COALESCE(LM4.fiscal_week_id, 0)  fiscal_week_id_4_month_ago,

                COALESCE(LM5.fiscal_week_id, 0)  fiscal_week_id_5_month_ago,

                COALESCE(LM6.fiscal_week_id, 0)  fiscal_week_id_6_month_ago,

                COALESCE(LM7.fiscal_week_id, 0)  fiscal_week_id_7_month_ago,

                COALESCE(LM8.fiscal_week_id, 0)  fiscal_week_id_8_month_ago,

                COALESCE(LM9.fiscal_week_id, 0)  fiscal_week_id_9_month_ago,

                COALESCE(LM10.fiscal_week_id, 0) fiscal_week_id_10_month_ago,

                COALESCE(LM11.fiscal_week_id, 0) fiscal_week_id_11_month_ago,

                COALESCE(LM12.fiscal_week_id, 0) fiscal_week_id_12_month_ago,

                COALESCE(LM13.fiscal_week_id, 0) fiscal_week_id_13_month_ago,

                COALESCE(LY2W.fiscal_week_id, 0)

                fiscal_week_id_last_year_2_week_ago,

                COALESCE(LY3W.fiscal_week_id, 0)

                fiscal_week_id_last_year_3_week_ago,

                COALESCE(LY4W.fiscal_week_id, 0)

                fiscal_week_id_last_year_4_week_ago,

                COALESCE(LY5W.fiscal_week_id, 0)

                fiscal_week_id_last_year_5_week_ago,

                'SQL_SCRIPT' AS CREATE_PROCESS_CD

,'BKANDALA' AS  CREATE_USER

,CURRENT_TIMESTAMP AS CREATE_TS

,NULL AS UPDATE_PROCESS_CD

,NULL AS UPDATE_USER

,NULL AS UPDATE_TS



FROM   fiscal_week A

       LEFT OUTER JOIN fiscal_week B

                    ON A.fiscal_week_id_last_year = B.fiscal_week_id

                       AND A.fiscal_week_id > B.fiscal_week_id

       LEFT OUTER JOIN fiscal_week C

                    ON A.fiscal_week_id_2_year_ago = C.fiscal_week_id

       LEFT OUTER JOIN fiscal_week D

                    ON A.fiscal_week_id_3_year_ago = D.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week E

                    ON A.fiscal_week_id_4_year_ago = E.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week F

                    ON A.fiscal_week_id_5_year_ago = F.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week G

                    ON A.fiscal_week_id_6_year_ago = G.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week H

                    ON A.fiscal_week_id_7_year_ago = H.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week I

                    ON A.fiscal_week_id_8_year_ago = I.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week J

                    ON A.fiscal_week_id_9_year_ago = J.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week K

                    ON A.fiscal_week_id_10_year_ago = K.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW1

                    ON A.fiscal_week_id_last_week = LW1.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW2

                    ON

       A.fiscal_week_id_2_week_ago = LW2.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW3

                    ON

       A.fiscal_week_id_3_week_ago = LW3.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW4

                    ON

       A.fiscal_week_id_4_week_ago = LW4.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW5

                    ON

       A.fiscal_week_id_5_week_ago = LW5.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW6

                    ON

       A.fiscal_week_id_6_week_ago = LW6.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW7

                    ON

       A.fiscal_week_id_7_week_ago = LW7.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW8

                    ON

       A.fiscal_week_id_8_week_ago = LW8.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW9

                    ON

       A.fiscal_week_id_9_week_ago = LW9.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW10

                    ON

       A.fiscal_week_id_10_week_ago = LW10.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW11

                    ON

       A.fiscal_week_id_11_week_ago = LW11.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW12

                    ON

       A.fiscal_week_id_12_week_ago = LW12.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW13

                    ON

       A.fiscal_week_id_13_week_ago = LW13.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW14

                    ON

       A.fiscal_week_id_14_week_ago = LW14.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW15

                    ON

       A.fiscal_week_id_15_week_ago = LW15.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW16

                    ON

       A.fiscal_week_id_16_week_ago = LW16.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW17

                    ON

       A.fiscal_week_id_17_week_ago = LW17.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW18

                    ON

       A.fiscal_week_id_18_week_ago = LW18.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW19

                    ON

       A.fiscal_week_id_19_week_ago = LW19.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW20

                    ON

       A.fiscal_week_id_20_week_ago = LW20.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW21

                    ON

       A.fiscal_week_id_21_week_ago = LW21.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW22

                    ON

       A.fiscal_week_id_22_week_ago = LW22.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW23

                    ON

       A.fiscal_week_id_23_week_ago = LW23.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW24

                    ON

       A.fiscal_week_id_24_week_ago = LW24.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW25

                    ON

       A.fiscal_week_id_25_week_ago = LW25.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW26

                    ON

       A.fiscal_week_id_26_week_ago = LW26.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW27

                    ON

       A.fiscal_week_id_27_week_ago = LW27.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW28

                    ON

       A.fiscal_week_id_28_week_ago = LW28.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW29

                    ON

       A.fiscal_week_id_29_week_ago = LW29.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW30

                    ON

       A.fiscal_week_id_30_week_ago = LW30.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW31

                    ON

       A.fiscal_week_id_31_week_ago = LW31.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW32

                    ON

       A.fiscal_week_id_32_week_ago = LW32.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW33

                    ON

       A.fiscal_week_id_33_week_ago = LW33.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW34

                    ON

       A.fiscal_week_id_34_week_ago = LW34.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW35

                    ON

       A.fiscal_week_id_35_week_ago = LW35.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW36

                    ON

       A.fiscal_week_id_36_week_ago = LW36.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW37

                    ON

       A.fiscal_week_id_37_week_ago = LW37.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW38

                    ON

       A.fiscal_week_id_38_week_ago = LW38.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW39

                    ON

       A.fiscal_week_id_39_week_ago = LW39.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW40

                    ON

       A.fiscal_week_id_40_week_ago = LW40.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW41

                    ON

       A.fiscal_week_id_41_week_ago = LW41.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW42

                    ON

       A.fiscal_week_id_42_week_ago = LW42.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW43

                    ON

       A.fiscal_week_id_43_week_ago = LW43.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW44

                    ON

       A.fiscal_week_id_44_week_ago = LW44.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW45

                    ON

       A.fiscal_week_id_45_week_ago = LW45.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW46

                    ON

       A.fiscal_week_id_46_week_ago = LW46.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW47

                    ON

       A.fiscal_week_id_47_week_ago = LW47.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW48

                    ON

       A.fiscal_week_id_48_week_ago = LW48.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW49

                    ON

       A.fiscal_week_id_49_week_ago = LW49.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW50

                    ON

       A.fiscal_week_id_50_week_ago = LW50.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW51

                    ON

       A.fiscal_week_id_51_week_ago = LW51.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LW52

                    ON

       A.fiscal_week_id_52_week_ago = LW52.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LQ1

                    ON

       A.fiscal_week_id_1_quarter_ago = LQ1.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LQ2

                    ON

       A.fiscal_week_id_2_quarter_ago = LQ2.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LQ3

                    ON

       A.fiscal_week_id_3_quarter_ago = LQ3.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LQ4

                    ON

       A.fiscal_week_id_4_quarter_ago = LQ4.last_date_in_fiscal_week

       LEFT OUTER JOIN fiscal_week LQ5

                    ON

Dateadd(day, -10, A.fiscal_week_id_5_quarter_ago) = LQ5.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LQ6

             ON A.fiscal_week_id_6_quarter_ago = LQ6.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LQ7

             ON A.fiscal_week_id_7_quarter_ago = LQ7.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LQ8

             ON A.fiscal_week_id_8_quarter_ago = LQ8.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LQ9

             ON A.fiscal_week_id_9_quarter_ago = LQ9.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LQ10

             ON A.fiscal_week_id_10_quarter_ago = LQ10.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM1

             ON A.fiscal_week_id_1_month_ago = LM1.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM2

             ON A.fiscal_week_id_2_month_ago = LM2.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM3

             ON A.fiscal_week_id_3_month_ago = LM3.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM4

             ON A.fiscal_week_id_4_month_ago = LM4.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM5

             ON A.fiscal_week_id_5_month_ago = LM5.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM6

             ON A.fiscal_week_id_6_month_ago = LM6.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM7

             ON A.fiscal_week_id_7_month_ago = LM7.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM8

             ON A.fiscal_week_id_8_month_ago = LM8.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM9

             ON A.fiscal_week_id_9_month_ago = LM9.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM10

             ON A.fiscal_week_id_10_month_ago = LM10.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM11

             ON A.fiscal_week_id_11_month_ago = LM11.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM12

             ON A.fiscal_week_id_12_month_ago = LM12.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LM13

             ON A.fiscal_week_id_13_month_ago = LM13.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LY2W

             ON

A.fiscal_week_id_last_year_2_week_ago = LY2W.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LY3W

             ON

A.fiscal_week_id_last_year_3_week_ago = LY3W.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LY4W

             ON

A.fiscal_week_id_last_year_4_week_ago = LY4W.last_date_in_fiscal_week

LEFT OUTER JOIN fiscal_week LY5W

             ON

A.fiscal_week_id_last_year_5_week_ago = LY5W.last_date_in_fiscal_week

)

select *

from cte;

----COMMIT;
{count} votes

3 answers

Sort by: Most helpful
  1. answered 2020-09-22T06:32:54.593+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Hi @bk

    Dateadd(day, -10, A.fiscal_week_id_5_quarter_ago) = LQ5.last_date_in_fiscal_week may contain an error value, but we can’t help you find the error, could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

    Also, there seems to be a problem with your insert statement.You can use select into to insert data into a temporary table, example:

    ;with cte   
    as(select *,sum(balance) over(partition by project order by project) s  
    from test)  
    select * into test1 from cte  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Best Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

  2. answered 2020-09-22T12:33:28.997+00:00
    bk 436 Reputation points

    Thanks Echo i corrected the Dateadd(day, -10, A.fiscal_week_id_5_quarter_ago) = LQ5.last_date_in_fiscal_week and still getting the same error. I will post the CREATE and sample data later today.

    No comments

  3. answered 2020-09-22T13:06:43.8+00:00
    Olaf Helper 25,341 Reputation points

    Numeric Value 'Week 5 of Fiscal Month' is not recognized.

    That's not an error message from Microsoft SQL Server, it's from Snowflake cloud database

    https://copycoding.com/d/numeric-value-xyz-is-not-recognized-how-to-fix-the-snowflake-error-message

    No comments