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 of over 48,000 articles and solutions.
Close
Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.