Forum Discussion
Common Table Expressions using WITH
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'
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.
3 Replies
What error message are you getting?
Can you put this CTE expression in a view and then just reference that view from PowerBI?
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.
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.
