TM1 Forum

Hello All,

I am trying to execute the below statement in Teradata interface it works fine, but when I try to exceute in TI it says ' SQL Statement Failed', ODBC connection is tested.. works fine. Any syntax need to be changes so that TI accepts ..?


SELECT
SSF.RPT_MTH
,EP.ECPD_PROFILE_TYPE_CD
,EP.ECPD_LIABILITY_TYPE_CD
,CASE
WHEN C.AREA_CD = 'National' THEN M.AREA_DESC
WHEN C.AREA_CD IS NULL THEN M.AREA_DESC
ELSE C.AREA_CD
END AS HQ_AREA
,CASE
WHEN M.AREA_DESC = 'Midwest' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' AND EPC.ECPD_CONTRACT_ID <> '49295' THEN 'GSC - Midwest'
WHEN M.AREA_DESC = 'Midwest' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' AND EPC.ECPD_CONTRACT_ID ='49295' THEN 'GSC - Midwest - State of Illinois'
WHEN M.AREA_DESC = 'Midwest' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' THEN 'GSC - Midwest'
WHEN M.AREA_DESC = 'Northeast' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' THEN 'GSC - Northeast'
WHEN M.AREA_DESC = 'South' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' THEN 'GSC - South'
WHEN M.AREA_DESC = 'West' AND EP.ECPD_PROFILE_TYPE_CD = 'Government Acct Corporate' THEN 'GSC - West'
ELSE M.AREA_DESC
END AS CUST_AREA
,CASE
WHEN m.prime_mkt_state_cd='HI' AND m.REGION_CD = 'CN' THEN 'Hawaii'
WHEN m.prime_mkt_state_cd<>'HI' AND m.REGION_CD = 'CN' THEN m.region_desc || '(Excl Hawaii)'
WHEN m.prime_mkt_state_cd='AK' and m.REGION_CD = 'NW' THEN 'Alaska'
WHEN m.prime_mkt_state_cd<>'AK' and m.region_cd = 'NW' THEN m.region_desc || '(Excl Alaska)'
ELSE m.region_desc
END AS REGION_DESC
,CASE WHEN COALESCE(SSF.LINE_TYPE_CD,'NM2M')='T' THEN 'Y' ELSE 'N' END AS M2M_FLAG
,SUM(SSF.ENDING_CUSTOMERS) AS SUBS

from ntl_prd_allvm.subs_sum_fact_v ssf

/* Added */
JOIN NTL_PRD_ALLVM.MARKET_V M -- 0..1
ON SSF.MKT_CD = M.MKT_CD

JOIN NTL_PRD_ALLVM.ECPD_PROFILE_V EP
ON SSF.ECPD_PROFILE_ID = EP.ECPD_PROFILE_ID

LEFT
JOIN NTL_PRD_ALLVM.ECPD_CENTER_V C
ON EP.CENTER_ID = C.CENTER_ID

/* This comment line starts the updates requested on */
JOIN NTL_PRD_ALLVM.ECPD_PROFILE_CONTRACT_V EPC
ON EP.ECPD_PROFILE_ID = EPC.ECPD_PROFILE_ID

JOIN NTL_PRD_QMVM.ECPD_CURR_CNTRCT_DTS EPCDT1
ON EPCDT1.ECPD_PROFILE_ID = EPC.ECPD_PROFILE_ID
AND EPCDT1.START_DT = EPC.START_DT
AND EPCDT1.END_DT = EPC.END_DT
/*This comment line ends the udpates requested on */

where coalesce(ssf.rev_gen_ind,'Y') = 'Y'

and COALESCE(EP.ecpd_liability_type_cd,'C') = 'C'
and ssf.ending_customers > 0
and ssf.rpt_mth = ((DATE - INTERVAL '1' DAY) - EXTRACT(DAY FROM (DATE - INTERVAL '1' DAY)) + INTERVAL '1' DAY)
group by 1,2,3,4,5,6,7

Thanks,
Radhika

I'm pretty sure you've exceeded the number of characters allowed in TM1 for the SQL string (and don't ask me what the limit is, search for yourself). You should create a stored query/procedure in Teradata to bundle up all the tables, columns and joins to reduce the string. Then you can just pass in the WHERE conditions.

This issue is resolved.

Modifications to query:

• Included Inner Join - TM1 does not recognize Join
• Include comma in the same line
• Included ( ) for case statements
• Inserted ‘ ; ‘End of the query
• Comments need to be added either before or after the statement but not along with the statement.

Thanks,
Radhika

Radhika wrote: • Included Inner Join - TM1 does not recognize Join
• Include comma in the same line
• Included ( ) for case statements
• Inserted ‘ ; ‘End of the query
• Comments need to be added either before or after the statement but not along with the statement.

These settings have nothing to do with TM1. TM1 does not "recognize" or "not recognize" anything in SQL. When you run a query in a TI with an ODBC data source TM1 simply passes your string along. It's the ODBC driver that is rejecting it. All the things you have done above, at least as far as I know, are required by most ofl the SQL languages I've ever used.