topic Re: run sql script "with" statement in qlikview in QlikView

run sql script "with" statement in qlikview

singbittoo — Sat, 16 Nov 2024 05:00:10 GMT

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

Re: run sql script "with" statement in qlikview

Gysbert_Wassenaar — Tue, 11 Dec 2018 06:50:40 GMT

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/DateAndTimeFunctions/converttolocaltime.htm

Re: run sql script "with" statement in qlikview

singbittoo — Tue, 11 Dec 2018 09:53:07 GMT

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