Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

transform variables to sql

Posted on 2012-03-12
4
Medium Priority
?
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 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