Oracle Database Dynamic Filter

[aiubxh8]

I have an Oracle database connection that I am trying to dynamically filter in database. I would like to filter the data for current date minus 6 months. I cannot quite figure out the syntax. Any suggestions?

CURRENT QUERY (the bolded section is what I would like to dynamically filter):

Select DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_MO_YR_KEY_NO, Trunc(To_Date(DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_DT_KEY_NO, 'YYYY-MM-DD'), 'Day') Week_Date, To_Date(DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_DT_KEY_NO, 'YYYY-MM-DD') As "Order Date", DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.SHP_QTY, DWSAVR01.DWV03000_ITEM_DIM.GLBL_BASE_ITEM_NO, DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_DT_KEY_NO, DWSAVR01.AWV00090_WHSE_DIM.WHSE_CD, DWSAVR01.AWV00004_CNTRY_AFF_DIM.AMWAY_CNTRY_CD, DWSAVR01.DWV03004_ITEM_BOM_DIM.SHP_ITEM_NO, DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_QTY, DWSAVR01.DWV03000_ITEM_DIM.BASE_7_ITEM_NO, DWSAVR01.AWV00004_CNTRY_AFF_DIM.RGN_CD From DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT Inner Join DWSAVR01.AWV00004_CNTRY_AFF_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.OPER_CNTRY_KEY_NO = DWSAVR01.AWV00004_CNTRY_AFF_DIM.CNTRY_KEY_NO Inner Join DWSAVR01.DWV00160_MO_YR_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_MO_YR_KEY_NO = DWSAVR01.DWV00160_MO_YR_DIM.MO_YR_KEY_NO Inner Join DWSAVR01.DWV03000_ITEM_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_ITEM_KEY_NO = DWSAVR01.DWV03000_ITEM_DIM.ITEM_KEY_NO Inner Join DWSAVR01.DWV00710_ORD_CD_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_CD_KEY_NO = DWSAVR01.DWV00710_ORD_CD_DIM.ORD_CD_KEY_NO Inner Join DWSAVR01.AWV00090_WHSE_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.WHSE_KEY_NO = DWSAVR01.AWV00090_WHSE_DIM.WHSE_KEY_NO Inner Join DWSAVR01.DWV03004_ITEM_BOM_DIM On DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.SHP_ITEM_KEY_NO = DWSAVR01.DWV03004_ITEM_BOM_DIM.SHP_ITEM_KEY_NO Where DWSAVR01.DWV10611_FULFIL_ORD_DTL_FACT.ORD_DT_KEY_NO > 20190126 And (DWSAVR01.AWV00004_CNTRY_AFF_DIM.AMWAY_CNTRY_CD != '500' Or DWSAVR01.AWV00004_CNTRY_AFF_DIM.AMWAY_CNTRY_CD != '850' Or DWSAVR01.AWV00004_CNTRY_AFF_DIM.AMWAY_CNTRY_CD != '360' Or DWSAVR01.AWV00004_CNTRY_AFF_DIM.AMWAY_CNTRY_CD != '430') And DWSAVR01.DWV00710_ORD_CD_DIM.CANC_FLG = 'N' And DWSAVR01.DWV00710_ORD_CD_DIM.PAY_REQ_FLG <> 'N'

[CharlieS · Accepted solution]

Do you mean the syntax to do this within the Oracle query, or the Alteryx formula to create that date? The formula below will create the string (in the format you provided) which can be used by the Dynamic Input tool to update that WHERE clause within your query:

datetimeformat(DateTimeAdd(datetimetoday(),-6,"months"),"%Y%m%d")

Yields "20180813", which is the value for the Dynamic Input > Modify SQL Query > Add > SQL: Update WHERE clause.

[aiubxh8]

I am looking to do this within the query, as it is a very large amount of data to extract if I do it as a typical formula after pulling from the database. Are you suggesting using a Dynamic Input In-DB or a Dynamic Input? I am struggling to configure the Dynamic Input. Do you have any examples of this?