Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
singbittoo
Creator
Creator

run sql script "with" statement in qlikview

i want to run this oracle sql script from qlik script is there any way to do it, i have created connection and i am able to run simple select queries but have no idea how to run the below one,

with
  function get_tstz(p_date in date) return timestamp with time zone is
    dst_exception exception;
    pragma exception_init(dst_exception, -1878);
  begin
    return from_tz(cast(p_date as timestamp), 'US/Eastern');
    exception
      when dst_exception then
        return from_tz(cast(p_date as timestamp), '-04:00');
  end get_tstz;
select date_time,
  get_tstz(date_time) as date_time_converted,
  get_tstz(date_time) at time zone 'UTC' as date_time_utc
from table1

 

Labels (4)
2 Replies
Gysbert_Wassenaar

I'm not sure you can get Qlikview to send that to the sql server. Normally you put SQL in front of a statement and it will be send to the odbc/oledb driver etc. But statements are terminated with a semicolon. And those are embedded in your statement too.

But you don't need to use the SQL database to convert timestamps to  other timezones. Qlikview has the ConvertToLocalTime function which allows you to convert any timestamp to a timestamp in the time zone of your choice. See https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/Da...


talk is cheap, supply exceeds demand
singbittoo
Creator
Creator
Author

how to read the data into US/Eastern time zone and convert to UTC after it using the above function, i am in india and the date which i am loading are in US/Eastern time, how will qlik know that the dates are in US/Eastern time