DV "WITH [Table] AS" being sent to Datasource
Issue
- Running the below query, I receive table doesn't exist for the temp table created by the "WITH [tablename] AS" clause:
WITH MONTHEND AS (
SELECT CONVERT(REFERENCE_VALUE, DATE) AS REFERENCE_VALUE
FROM MLC_REFERENCE_DATA_T
WHERE REFERENCE_TYPE = 'MONTH_END_DATE' AND REFERENCE_SUB_TYPE = 'MONTH_END_DATE'
)
SELECT
RATES.FROM_CURRENCY,
MAX(CASE WHEN RATES.TO_CURRENCY = 'STG' THEN RATES.RATE END) GBP_RATE,
MAX(CASE WHEN RATES.TO_CURRENCY = 'ERB' THEN RATES.RATE END) EUR_RATE,
MAX(CASE WHEN RATES.TO_CURRENCY = 'USB' THEN RATES.RATE END) USD_RATE
FROM
FDM.DAILYRATES RATES
WHERE
RATES.TO_CURRENCY IN ('STG','ERB','USB')
AND RATES.CONVERSION_TYPE='Spot'
AND RATES.BUSINESS_DATE=(SELECT REFERENCE_VALUE FROM MONTHEND)
GROUP BY
RATES.FROM_CURRENCY
Environment
- Red Hat JBoss Data Virtualization (DV) 6.0, 6.1
- Source query being sent to the source contains the "WITH [tablename] AS" clause:
2015-05-21 15:52:54,713 DEBUG [org.teiid.CONNECTOR] (Worker11_QueryProcessorQueue43) Source-specific command: SELECT c_0, c_1, c_2, c_3 FROM (WITH MONTHEND A
S (SELECT to_date(g_0.REFERENCE_VALUE, 'YYYY-MM-DD') AS REFERENCE_VALUE FROM MLC_REFERENCE_DATA_T g_0 WHERE g_0.REFERENCE_TYPE = 'MONTH_END_DATE' AND g_0.REF
ERENCE_SUB_TYPE = 'MONTH_END_DATE') SELECT /*teiid sessionid:Qo0Bn2TvQVyQ, requestid:Qo0Bn2TvQVyQ.17.3*/ g_0.FROM_CURRENCY AS c_0, MAX(CASE WHEN g_0.TO_CURRE
NCY = 'STG' THEN g_0.RATE END) AS c_1, MAX(CASE WHEN g_0.TO_CURRENCY = 'ERB' THEN g_0.RATE END) AS c_2, MAX(CASE WHEN g_0.TO_CURRENCY = 'USB' THEN g_0.RATE E
ND) AS c_3 FROM WH_GL_DAILY_RATES_F g_0 WHERE g_0.TO_CURRENCY IN ('STG', 'ERB', 'USB') AND g_0.CONVERSION_TYPE = 'Spot' AND g_0.BUSINESS_DATE = {d '2014-06-3
0'} GROUP BY g_0.FROM_CURRENCY)
Subscriber exclusive content
A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.
