How to execute a custom SQL

amtkmr1990 — Tue, 22 Jan 2019 09:10:53 GMT

How can i execute the following MS SQL Query. This query contains multiple tables and "Over - Partition by " functions.

select a.vm_id, d.HOST_ID, a.SHUTDOWN_DAYS,a.DIFF_COLLECTION_DATE_TS from (

select [VM_ID], datediff(hour,min([REC_COLLECTION_DATE_TS]),max([REC_COLLECTION_DATE_TS]))/24.0 as SHUTDOWN_DAYS,
max([REC_COLLECTION_DATE_TS])-min([REC_COLLECTION_DATE_TS]) as DIFF_COLLECTION_DATE_TS
from ( SELECT [VM_FCT_ID]
,[VM_ID]
,[REC_COLLECTION_DATE_TS]
,[PowerState]
,sum([PowerState]) OVER ( partition by [VM_ID] ORDER BY [REC_COLLECTION_DATE_TS] desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rn
FROM [T_FCT_VCENTER_VM] where [PowerState] is not null and VM_ID in (SELECT [VM_ID]

FROM [T_DIM_VCENTER_VM] where UPDATED_FLAG =1 and PowerState='PoweredOff')
) a where rn=0
group by [VM_ID]

) as a

inner join T_DIM_VCENTER_VM d on a.VM_ID = d.VM_ID
order by ShutDown_Days desc

Re: How to execute a custom SQL

vapukov — Tue, 22 Jan 2019 09:17:41 GMT

Hi,

what is your problem?

you can use any sql code in tMSSQLInput component

topic Re: How to execute a custom SQL in Talend Studio

How to execute a custom SQL

Re: How to execute a custom SQL