Select Statement for Oracle DB in Qlik

malkiewicz — Wed, 21 Jun 2023 22:28:15 GMT

Hi All,

I have a query with multiple aliases. How can I put this in Qlik Cloud? I keep getting errors trying with different load statements, quotations, etc. How do I alias in Qlik?

 
 
select t1.CO, t1.MACH, t1.srdate,
(case when t2.downtm is null then round((t1.Schedtime - 0) / 60)
else round((t1.Schedtime - t2.downtm) / 60) end) as Value
FROM
(
select company_code as CO, Machine_number as MACH, shift_reporting_date AS srdate,
sum(case when finish_time - start_time < 0 then (finish_time - start_time + 1440)
else (finish_time - start_time) end) as Schedtime
from DWT_KIWI_DWNTIM_SHFT
where last_day(shift_reporting_date) = '31-mar-23'
and (machine_number between 3110 and 3159 or machine_number between 4510 and 4579 or machine_number between 6310 and 6319)
group by Company_code, machine_number, shift_reporting_date
) t1
left outer join
(
select t4.comp as co, t4.mach as mach, t4.srdate AS srdate,
sum(case when ((t4.dtsm between t4.schsm and t4.schfm) and (t4.dtfm between t4.schsm and t4.schfm)) then (t4.dtfm - t4.dtsm)
else (case when (t4.dtsm between t4.schfm and 99999999999) then 0
else (case when (t4.dtfm between 0 and t4.schsm) then 0
else (case when (t4.dtsm between t4.schsm and 99999999999) then (t4.schfm - t4.dtsm)
else (case when (t4.dtfm between 0 and t4.schfm) then (t4.dtfm - t4.schsm)
Else (t4.schfm - t4.schsm) end) end) end) end) end) as Downtm
from
(select t5.company_code as comp, t5.machine_number as mach, t5.shift_reporting_date as srdate,
t5.start_date as strdt, t5.start_time as strtm, t5.finish_date as findt, t5.finish_time as fintm,
t6.start_date as strdate, t6.start_time as strtime, t6.finish_date as findate, t6.finish_time as fintime,
(((to_number(to_char(to_date(t5.finish_date),'yy')) * 525600) + (to_number(to_char(to_date(t5.finish_date),'ddd')) * 1440)) + t5.finish_time) as dtfm,
(((to_number(to_char(to_date(t5.start_date),'yy')) * 525600) + (to_number(to_char(to_date(t5.start_date),'ddd')) * 1440)) + t5.start_time) as dtsm,
(((to_number(to_char(to_date(t6.finish_date),'yy')) * 525600) + (to_number(to_char(to_date(t6.finish_date),'ddd')) * 1440)) + t6.finish_time) as schfm,
(((to_number(to_char(to_date(t6.start_date),'yy')) * 525600) + (to_number(to_char(to_date(t6.start_date),'ddd')) * 1440)) + t6.start_time) as schsm
from dwt_kiwi_dwntim_dt t5
inner join dwt_kiwi_dwntim_shft t6 on (t5.company_code = t6.company_code and t5.machine_number = t6.machine_number and (t5.shift_reporting_date between (t6.shift_reporting_date - 1 ) and (t6.shift_reporting_date + 1)))
where (
trunc(LAST_DAY(t5.shift_reporting_date)) = '31-mar-23' and t5.downtime_code in ('PL0','PL1','PL2','PL3','PL4','PL5','PL6','S3','S4'))
)t4
group by t4.comp, t4.mach, t4.srdate
) t2 on t1.CO = t2.CO and t1.MACH = t2.MACH AND t1.srdate = t2.srdate
 
 
 
 
 

Re: Select Statement for Oracle DB in Qlik

Zapparoli — Thu, 22 Jun 2023 01:52:34 GMT

Can you try adding "SQL" in your first select ?

Like:

SQL select t1.CO, t1.MACH, t1.srdate

...

Re: Select Statement for Oracle DB in Qlik

malkiewicz — Fri, 23 Jun 2023 17:41:20 GMT

That worked, thank you!

topic Select Statement for Oracle DB in Qlik in Connectivity & Data Prep

Select Statement for Oracle DB in Qlik

Re: Select Statement for Oracle DB in Qlik

Re: Select Statement for Oracle DB in Qlik