topic Re: Common Table Expressions using WITH in Desktop

Common Table Expressions using WITH

hp2328 — Wed, 01 May 2019 20:47:31 GMT

hello all,

I am attempting to use a Common Table Expression to query multiple tables in one query and output one table combining them. I can run this query using other SQL programs to query the database, but the query will not run in Power BI. The query looks like this:

WITH
a AS (SELECT * FROM ELEMENTS EL WHERE EL.LOCAL_ID LIKE 'F%'),
b AS (SELECT * FROM ENTITIES EN),
c AS (SELECT * FROM a JOIN b ON a.ent_key = b.ent_key),
pdc AS (SELECT fone.*,(PMPDCCHCCEUTIL_0 + PMPDCCHCCEUTIL_1 + PMPDCCHCCEUTIL_2 + PMPDCCHCCEUTIL_3 + PMPDCCHCCEUTIL_4 + PMPDCCHCCEUTIL_5 + PMPDCCHCCEUTIL_6 + PMPDCCHCCEUTIL_7 + PMPDCCHCCEUTIL_8 + PMPDCCHCCEUTIL_9 + PMPDCCHCCEUTIL_10 + PMPDCCHCCEUTIL_11 + PMPDCCHCCEUTIL_12 + PMPDCCHCCEUTIL_13 + PMPDCCHCCEUTIL_14 + PMPDCCHCCEUTIL_15 + PMPDCCHCCEUTIL_16 + PMPDCCHCCEUTIL_17 + PMPDCCHCCEUTIL_18 + PMPDCCHCCEUTIL_19) PMPDCCHCCEUTIL80_DEN, (PMPDCCHCCEUTIL_16 + PMPDCCHCCEUTIL_17 + PMPDCCHCCEUTIL_18 + PMPDCCHCCEUTIL_19) PMPDCCHCCEUTIL80_NUM FROM NRS.ERIL_EUCELLFDD1_DY fone WHERE TSTAMP >= SYSDATE-2),
pdu AS (SELECT pdc.*, ROUND(COALESCE(pdc.PMPDCCHCCEUTIL80_NUM/NULLIF(pdc.PMPDCCHCCEUTIL80_DEN,0),0),2) as PMPDCCHCCEUTIL80 FROM pdc pdc)
SELECT c.NW_LABEL, pdu.TSTAMP, pdu.BW_DL_MHZ, pdu.PMPDCCHCCEUTIL80_NUM, pdu.PMPDCCHCCEUTIL80_DEN, pdu.PMPDCCHCCEUTIL80 FROM c, pdu WHERE c.ELE_KEY = pdu.ELE_KEY AND c.ENT_ID = 'EUTRANCELL'

Re: Common Table Expressions using WITH

d_gosbell — Thu, 02 May 2019 22:48:58 GMT

What error message are you getting?

Can you put this CTE expression in a view and then just reference that view from PowerBI?

Re: Common Table Expressions using WITH

hp2328 — Fri, 03 May 2019 03:31:56 GMT

I was able to fix it. PBI did not like the query without a fully qualified reference to the table with schema.table reference. I did not know that limitation.

Re: Common Table Expressions using WITH

d_gosbell — Fri, 03 May 2019 03:54:18 GMT

hp2328 wrote:
I was able to fix it. PBI did not like the query without a fully qualified reference to the table with schema.table reference. I did not know that limitation.

It might depend on what your default schema is or what connection options Power BI is using. I'm glad your found a solution.