Common Table Expressions using WITH

[hp2328]

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'

[d_gosbell]

What error message are you getting?

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

[hp2328 · Accepted solution]

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.

[d_gosbell]

---

@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.