Hi Team, Would you please help me to write a procedure which should pass the parameters of date values. I just wanted use the procedure for passing values rather than hard-coding values in my filter condition, How can I make this condition into procedure way.
Thanks in advance!
and a.dlvr_dt between current_date - 365 and current_date
Output should be:
and a.dlvr_dt between current_date – CALL PROCEDURE(example)
Hi Team, I have created the procedure and it was returning expected values. But How can i use the procedure in my where condition ( everything in BTEQ script) as I have mentioned previous post.
Would you please help us use this procedure in WHERE condition of update statement which was in there BTEQ script.
CREATE PROCEDURE TWO_WEEK_SPACE.PROC_TO_SEND_DATA (OUT DAYS INTEGER)
SEL TRIM (NO_OF_DAYS) (TITLE '') FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
CREATE TABLE TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
select * from dbc.tables
INSERT INTO TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
CALL TWO_WEEK_SPACE.PROC_TO_SEND_DATA(INP);--It returns 365 value
I wanted to use something like below..
you can't use an SP in a SELECT.
Why do you actually need it?
Your example can easily be rewritten without it.
Thank you for your confirmation, Actually that passing value(365) will be changed soon as per busniess requirement. I wnat to write a procedure which has to pass the value (Means once requirement changes then I have to insert new record into Procedure table, No need to do any coding changes at BTEQ level).
By the way, we can use the base table in that WHERE clause
AND A.DLVR_DT BETWEEN CURRENT_DATE – (SEL NO_OF_DAYS FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC) AND CURRENT_DATE
But instead of Base table(Not standard way), So I wnated to use a procedure.
why is using a SP more standard than using a table?
If you use this the optimizer can do Incremental Planning in TD14.10:
AND A.DLVR_DT BETWEEN CURRENT_DATE
– (SEL MIN(NO_OF_DAYS) FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC)