Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

transform variables to sql

Posted on 2012-03-12
4
Medium Priority
?
366 Views
Last Modified: 2012-03-30
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')))
0
Comment
Question by:sam2929
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37710608
could you explain a bit more ?
it's not clear what you are trying to do
0
 

Author Comment

by:sam2929
ID: 37711468
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')))
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37720218
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
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 37724804
Hi Sam,

The end time is built from the current Julian date and current time.  Current Timestamp should be a suitable substitute and not require the hoops that the code is jumping through.

The start time is read from the database (aaSTG_DELTA_TIMESTAMPS).  Simply substituting that query into the filter is equivalent to computing a variable and using the var.

WHERE (((aa.new.GEO IN ('aa', 'bb')
  AND (aaSTG_MATCHES.MATCH_TIMESTAMP >= (SELECT starttime FROM aaSTG_DELTA_TIMESTAMPS
                                         WHERE PROCESSKEY = 'Export Responses to MII')))
  AND (aaSTG_MATCHES.MATCH_TIMESTAMP < current_timestamp))
  AND (aaSTG_MATCHES.MATCH_TYPE <> 'cc')))

Open in new window


Does that solve things?
Kent
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question