Modifying IN DB SQL query by a formula tool

Question

Hi,

I am using IN DB Query for extracting data from a database. query is like this:-

Select _SYS_BIC."acs.OTFP.OTFPSummary/QV_OTFP".ITEM_CATEGORY,
_SYS_BIC."acs.OTFP.OTFPSummary/QV_OTFP".SALES_ORD_TYPE,
_SYS_BIC."acs.OTFP.OTFPSummary/QV_OTFP".CC_MAT_TYPE
from _SYS_BIC."acs.OTFP.OTFPSummary/QV_OTFP" ('PLACEHOLDER' = ('$$IP_DATE_FROM$$', '20200801'), 'PLACEHOLDER' = ('$$IP_DATE_TO$$', '20210131'), 'PLACEHOLDER' = ('$$IP_HFM_LOB$$', '''ALL'''), 'PLACEHOLDER' = ('$$IP_PLANT$$', '''ALL''')

I want to modify highlighted date placeholder by formula Such that it will pick From date as 180 days back from today and  TO$$ date as today's date. Thanks in Advance.

SeanAdams · Answer

Hey @RAHULMISHRA

If all you're doing is running a query inDB - you can do this with a normal input tool and then do much of this dynamically (either using an input tool hooked up to action tools; or using a dynamic input)

In-DB only really starts to make a big difference if you're doing joins; filters etc on your original select and you don't want to do them in a single SQL statement.   If you know the SQL - then an input tool should perform very similarly to an InDB tool on any given SQL Select.

RAHULMISHRA · Answer

Great, i would try that. But, believe using Interface tool it is possible. I want a solution using ALteryx.

ImadZidan · Answer

Hello @RAHULMISHRA ,

I see what you are trying to do and it is a good idea to make things dynamic. The question is why do you want to do it this specific way. Why not use the SQL date function to do that.

Example:

('PLACEHOLDER' = (ADD_DAYS(CURRENT_DATE(),-180), '20200801'), 'PLACEHOLDER' = (CURRENT_DATE(), '20210131')

What I am curious about is that this cold be done at the sql extraction level.

May be it is not possible?