topic Re: Recursive query in QlikView

Recursive query

Fri, 05 Sep 2014 17:04:55 GMT

I am running qlik sense desktop version and getting below error while running recursive query...

Started loading data

Connecting to ODData

Connected

table_entity Lines fetched: 1,673

Unknown statement with [CTE] as (select ID,name,PARENT_ID,TYPE_........

Re: Recursive query

JonnyPoole — Fri, 05 Sep 2014 17:35:02 GMT

Hi - can you copy/paste your script into your post ?

Re: Recursive query

Fri, 05 Sep 2014 18:40:54 GMT

Thanks Johathan,

Here it is....

with [CTE] as (select ID,name,PARENT_ID,TYPE_ID as 'Type' from VPX_ENTITY c where c.[Parent_Id] ='xxx' union all select c.ID,c.NAME,c.PARENT_ID, c.TYPE_ID from [CTE]p, VPX_ENTITY c where c.[Parent_Id] = p.[Id] and c.[Parent_Id] <> c.[Id]) Select a.power_state as 'Status','DC01'as 'Datacenter', UPPER('" & (rowVal) & "') as Acronym, a.id as ID, f.NAME as HostName,a.IP_ADDRESS as 'IPAddress',c.GUEST_FULL_NAME as OS, a.NUM_VCPU as CPU,cast(a.MEM_SIZE_MB as integer) as 'Memory', sum(round((((cast(b.HARDWARE_DEVICE_CAPACITY_IN as float)/1024)/1024)),3)) as 'Storage'

from vpx_vm a join vpx_virtual_disk b on a.id = b.vm_id,VPX_VM_CONFIG_INFO c, VPX_ENTITY f, [CTE] g where a.Is_template = 0 and c.ID = a.ID and

f.ID = a.ID and g.Type = '0' and g.ID = a.ID Group by a.DNS_NAME,a.IP_ADDRESS, a.NUM_VCPU,a.IP_ADDRESS, c.GUEST_FULL_NAME,f.NAME, a.MEM_SIZE_MB,a.power_state,a.id ORDER BY A.DNS_NAME

Re: Re: Recursive query

IAMDV — Sun, 07 Sep 2014 09:08:41 GMT

Hi Vishal,

Are you using ODBC/OLEDB? I'm not sure if either of them support CTEs. I assume that you have tried to run the CTE from the Management Studio and it works fine. Is this right? I've always used Views to generate the data while using the ODBC/OLEDB drivers. Can you move this logic to Views/Stored Procs?

Cheers,

www.QlikShare.com

Re: Re: Recursive query

Fri, 12 Sep 2014 18:38:56 GMT

Hello Deepak,

Ok, that make sense, Thanks I will try and let you know the updates.

Thanks again.