When trying to display the detailed sql behind a view, TDW only shows the column names and a generic “
” place holder. It does not show the actual SQL that was used to create the view (ie logic used to caculate columns) or any of table(s) that were used in the FROM clause or any kind of WHERE clause. Anyone else experiencing this same thing … the defintiion is incorrect and essentially useless. Example Below
This is occurring in TDP 3.3 – using Netezza
the SQL that I used to create a view…
CREATE OR REPLACE VIEW TEST_DB…VW_TEST_VIEW as
SELECT WRK1.EFFECTIVE_YEAR
, WRK1.SALE_DATE
, WRK1.ACCOUNT_NUM
, CASE WHEN (((WRK1.EFFECTIVE_YEAR - PL.CRT_YEAR) >= 0) AND ((WRK1.EFFECTIVE_YEAR - PL.CRT_YEAR) <= 2)) THEN 1 ELSE 0 END AS CREATED_IND
, CASE WHEN (((WRK1.EFFECTIVE_YEAR - PL.DLV_YEAR) >= 0) AND ((WRK1.EFFECTIVE_YEAR - PL.DLV_YEAR) <= 2)) THEN 1 ELSE 0 END AS DELIVERED_IND
FROM
DB1…PLAN_DATA PL
JOIN DB1…PLAN_ACCOUNT PL_ACCT
ON PL.PLAN_ID_NUM = PL_ACCT.PLAN_ID_NUM
JOIN WRK_DB…PLAN_SALES_DATA WRK1
ON WRK1.ACCOUNT_NUM = PL_ACCT.ACCOUNT_NUM
AND WRK1.SALE_DATE > CASE WHEN PL.PLAN_DLV_DTE = ‘1800-01-01’ THEN PL.PLAN_CRT_DTE ELSE PL.PLAN_DLV_DTE END
where effective_year = 2012
limit 100;
The view definition returned by TOAD:
CREATE VIEW VW_MFW_TEST_VIEW
AS
SELECT
EFFECTIVE_YEAR
,SALE_DATE
,ACCOUNT_NUM
,CREATED_IND
,DELIVERED_IND
FROM
< table_name>;
What kind of connection are you using? This is how is is for an ODBC connection. But if you are connection to oracle, SQL Server, DB2, mySQL or Sybase, you should not be using ODBC but our native providers. You will get the correct view definition when using one of these native providers