1. ホーム
  2. select

[解決済み】snosqlステートメントで予期しないselectエラーが発生し続ける

2022-01-24 09:25:46

質問

snowsqlステートメントの61行目と64行目で、予期しないselectエラーと予期しないONエラーが発生し続けます。

なぜかわからないのですが、どなたか助けていただけると助かります。以下に私のsnowsql文の部分を追加しました。

where句の中でselect文を使おうとしているのですが、その方法はあるのでしょうか?

AS select
t1.sunday_date,
t1.sunday_year_month,
t1.sunday_month,
t1.dc,
t1.source_sku,
t1.Product_Family,
t1.Product_type,
t1.Product_Subtype,
t1.Material,
t1.Color,
t1.Size,
t1.EOL_Date,
t1.NPI_Date,
t1.period_start,
t1.period_month,
IIF( t4.period_start < t1.sunday_date, iif(ISNULL(ta.actual_quantity), 0, ta.actual_quantity),
    IIF(ISNULL(tfc.SOPFCSTOVERRIDE ), iif(ISNULL(tf.Period_Start), 0, tf.dc_forecast) , tfc.SOPFCSTOVERRIDE 
)) AS forecast_updated,
iif(ISNULL(tf.Period_Start),t4.period_start,tf.Period_Start) AS period_start_forecast,
iif(ISNULL(ti.VALUATED_UNRESTRICTED_USE_STOCK), 0, ti.VALUATED_UNRESTRICTED_USE_STOCK)  AS inventory_quantity,
iif(ISNULL(ti.HCI_DS_KEYFIGURE_QUANTITY), 0, ti.HCI_DS_KEYFIGURE_QUANTITY)  AS in_transit_quantity,
iif(ISNULL(ti.planned_quantity), 0, ti.planned_quantity) AS inbound_quantity,
iif(ISNULL(tbac.backlog_ecomm ), 0, tbac.backlog_ecomm) + iif(ISNULL(tbac_sap.backlog_sap_open), 0, tbac_sap.backlog_sap_open)   AS backlog_quantity,
iif(ISNULL(ta.actual_quantity), 0, ta.actual_quantity) AS actual_quantity,
iif(ISNULL(tso.open_orders), 0, tso.open_orders) AS open_orders,
iif(ISNULL(tf.Period_Start), 0, tf.dc_forecast) AS forecast,
tfc.SOPFCSTOVERRIDE AS forecast_consumption,
iif(ISNULL(tpc.SHIP_DATE), 0, tpc.SHIP_DATE) AS production_current_week,
iif(ISNULL(tpc.SHIP_DATE), 0, tpc.SHIP_DATE) AS production_next_week,
NOW() AS updated_timestamp 

FROM ( ( ( ( ( ( ( ( (

SELECT 
e.sunday_date,
e.sunday_month,
e.sunday_year_month,
d.dc,
c.SOURCE_SKU,
c.Product_Family,
c.Product_Type,
c.Product_Subtype,
c.Material,
c.Color,
c.Size,
c.EOL_Date,
c.NPI_Date,
b.period_start,
b.period_month


FROM 
(SELECT sunday_date, sunday_month, sunday_year_month FROM bas_report_date) AS e,
(SELECT distinct Week_Date AS period_start, DateSerial('445_Year','445_Month',1) AS period_month from inv_bas_445_Month_Alignment)  AS b, 
(SELECT source_sku AS source_sku, Product_Family, Product_Type, Product_Subtype, Material, Color, Size, EOL_Date, NPI_Date from inv_vw_product_dev )  AS c,
(SELECT dc AS dc FROM inv_bas_dc_site_lookup)  AS d

WHERE b.period_start >= 
( select
MIN(mt.Reference_Date )
FROM BAS_report_date tr
INNER JOIN inv_bas_445_Month_Alignment mt ON tr.sunday_month = DateSerial(mt.'445_Year',mt.'445_Month,1')
)

AND b.period_start <= DateAdd("ww", 26,e.sunday_date) 

) t1 

LEFT JOIN 
( 
    SELECT
    MATERIAL_NUMBER,
    CINT(LOCATION_NUMBER) AS Int_Location_ID,
    HCI_DS_KEYFIGURE_DATE,
    HCI_DS_KEYFIGURE_QUANTITY,
    PLANNED_QUANTITY,
    VALUATED_UNRESTRICTED_USE_STOCK

    FROM inv_vw_ibp_transit_inventorry_dev
) ti

解決方法は?

を置き換えることができます。 DateSerial() 機能
(マイクロソフトのVBA / MS Access / Excelのものです)
日付_from_parts() .

日付_from_parts() はまた DateSerial() :

DateSerial(2020, 1, 1 - 1) 大晦日~元旦の前日をゲット DATE_FROM_PARTS(2020, 1 - 1, 1 - 1) は元旦の前日の前月 DATE_FROM_PARTS(y, m + 1, 0) はEOM(End Of Month)です。
などなど。