asked on

transform variables to sql

Hi,
i have two variables which are not passed in sql right now i want to get rid of that variables and pass them direct to sql how can i do that

v= DATE_STRING(CURRENT_JULIAN()-7, YYYYMMDD);

UserVar.DELTA_EndTime = STRING_concat(
substr(v,1,4), '-',
substr(v, 5,2), '-',
substr(v,7,2), ' ',
CURRENT_TIME())

UserVar.DELTA_StartTime is
select starttime from aaSTG_DELTA_TIMESTAMPS
where PROCESSKEY = 'Export Responses to MII'
with ur;

sql is

WHERE (((aa.new.GEO IN ('aa', 'bb')
AND (aaSTG_MATCHES.MATCH_TIMESTAMP >=
UserVar.DELTA_StartTime))
AND (aaSTG_MATCHES.MATCH_TIMESTAMP < UserVar.DELTA_EndTime))
AND (aaSTG_MATCHES.MATCH_TYPE <> 'cc')))

momi_sabag

could you explain a bit more ?
it's not clear what you are trying to do

sam2929

ASKER

I want to replace the varaible

currently WHERE (((aa.new.GEO IN ('aa', 'bb')
AND (aaSTG_MATCHES.MATCH_TIMESTAMP >=
UserVar.DELTA_StartTime))
AND (aaSTG_MATCHES.MATCH_TIMESTAMP < UserVar.DELTA_EndTime))
AND (aaSTG_MATCHES.MATCH_TYPE <> 'cc')))

wants

WHERE (((aa.new.GEO IN ('aa', 'bb')
AND (aaSTG_MATCHES.MATCH_TIMESTAMP >=
select starttime from aa.STG_DELTA_TIMESTAMPS
where PROCESSKEY = 'Export Responses to MII'))
AND (aaSTG_MATCHES.MATCH_TIMESTAMP < UserVar.DELTA_EndTime))
AND (aaSTG_MATCHES.MATCH_TYPE <> 'cc')))

momi_sabag

you want to use dynamic sql then?
something like
set var = 'WHERE (((aa.new.GEO IN ('aa', 'bb')
AND (aaSTG_MATCHES.MATCH_TIMESTAMP >= ' ||
UserVar.DELTA_StartTime || '))
AND (aaSTG_MATCHES.MATCH_TIMESTAMP < UserVar.DELTA_EndTime))
AND (aaSTG_MATCHES.MATCH_TYPE <> 'cc')))'

execute immediate var

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial