Select Statement for Oracle DB in Qlik

[malkiewicz]

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

[Zapparoli · Accepted solution]

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

Like:

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

...

[malkiewicz]

That worked, thank you!