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
Numeric Value is not recognized when INSERT data
bk
441
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
-
Viorel 88,656 Reputation points2020-09-22T05:39:50.23+00:00 I am trying to INSERT data into a table using the format
INSERT INTO TABLE (COLUMNS)
WITH CTE AS
(SELECT STATEMENT)
SELECT *
FROM CTEI think that the format should be:
;WITH CTE AS ( SELECT … )
INSERT INTO TABLE (COLUMNS)
SELECT … FROM CTE
Sign in to comment
3 answers
Sort by: Newest
-
Olaf Helper 27,211 Reputation points2020-09-22T13:06:43.8+00:00
bk 441 Reputation points2020-09-22T12:33:28.997+00:00 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.
Sign in to answer
EchoLiu-MSFT 14,501 Reputation points2020-09-22T06:32:54.593+00:00 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 cteIf 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.
Do you have any updates? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best Regards
Echo