All Forums

Topics related to the Teradata Database, excluding database connectivity (e.g. JDBC) and extensibility (e.g. UDFs) . Typical topics are about SQL syntax and usage (SELECT, GROUP BY etc), database performance, use of database functions, comparison with / migration from Oracle or DB2, and generally why the Teradata Database rocks. For articles, blogs and more, see the Database section of the site.

You must login to post to the forums.
PPI Topic by Rick-2304 19 Jan 2006

I'm trying to use " insert into ... select * " to populate a table with PPI and am getting an error saying "Invalid Date Supplied for tablename.columnname" where tablename.columnname is the target table. The source and target table structures are exactly alike, with the same PPI range specified. I'm just trying to copy existing data into a another table for some testing.

0 replies
renaming a column name.....? Topic by Mike 11 Jan 2006

Hi Everyone,This is more of a SQL question, but figured I would post here as this group has always been super-helpful : )I want to, in the course of the SQL code, rename a column depending on what is in the column. Specifically, I am doing a count of customers by month, and want the column name to reflect each particular month.

2 replies, 14 years ago
V2R6 Topic by fouttyp 10 Jan 2006

We are planning an upgrade to V2R6 and was wondering if anyone would tell me their experiences with their upgrade to V2R6. Philip Foutty

0 replies
SQL Update Topic by Ravindra Harve 09 Jan 2006

Hi,I am new to Teradata, and I need to do a SQL update. Based on the Syntax given in the Manual, I did write this SQL, but somehow this doesn't seem to work. Please let me know where I am going wrong?Thanks in Advance.UPDATE MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TESTfrom ( select a12.FSC_WK_END_DT FSC_WK_END_DT, a11.PRD_GRP_NBR PRD_GRP_NBR, a12.CMA_LCT_CD CMA_LCT_CD, sum(a11.TOT_SAL_AMT) SALES, sum(a11.TOT_CST_AMT) COST from ABC.PGP_SMY a11 join ABC.LCT_HST a12 on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and a11.LCT_NBR = a12.LCT_NBR) group by a12.FSC_WK_END_DT, a11.PRD_GRP_NBR, a12.CMA_LCT_CD ) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as bset TBL_PERF_HIST_2_TEST.CMA_LCT_CD = a.cma_lct_cd, TBL_PERF_HIST_2_TEST.TOT_SAL_AMT = a.SALES, TBL_PERF_HIST_2_TEST.TOT_CST_AMT = A.COSTwhere b.fsc_wk_end_dt = a.fsc_wk_end_dt and b.prd_grp_nbr = a.prd_grp_nbrRegards

2 replies, 14 years ago
Conversion of Primary to Secondary index Topic by 10 Jan 2006

Hi, Is there a way to convert Primary index to Secondary index ?Thanks and Regards

1 reply, 14 years ago
String Replacement Topic by 02 Jan 2006

Hi,Is there is a way to replace a particular in a column with another character ?Support a Column values likeYahoo and HotMailHotMail and YahooYahoo and GMailGMail and HotMailI would like to replace "and" word with any other word like 'or'.Thanks and Regards

8 replies, 14 years ago
redirect explain plan in a file Topic by phil1212 09 Jan 2006

Hello,I have to create a bteq script that automaticly get sql query done (in dbc.accesslog) for a user, and then do the explain plan of these queries.But i need to redirect theses explain plans in a file.WHat is the best way to do that ?In a bteq, how d oyou redirect the result of a query in an os file ?Thanks a lot.

1 reply, 14 years ago
Cancel online transaction recovery after break big delete operation Topic by tfader 04 Jan 2006

I'm looking for any issues for canceling online transaction recovery when TPA starts.I cannot drop, truncate or restore table because is locked by transaction recovery. The table is with no fallback option. Is any chance to start TPA without transaction recovery ?

4 replies, 14 years ago
Need to make SMP to MMP Topic by singleamp 05 Jan 2006

Hello,Can you please share your expriences with Teradata SMP systems(single node)? 1. What are the NCR machines (WorldMark 4800?) supporting SMP?2. Whats the next compatile Hardware to make it MPP?3. Whats the MAX size (in Terabytes) an SMP can store and MAX number of Nodes/AMPs?4. Did NCR published any white paper with Pros and Cons of SMP and its comparision with MPP.Thanks.Rao

0 replies
Gateway ports Topic by Nytewynd 29 Dec 2005

I'm not very familiar with Teradata, but I need to connect an Oracle DB link to Teradata over a network and through a firewall. I know what I need to do on every step except the connection to teradata with the OLEDB provider.What ports do I need to open to connect to Teradata? The default port of 1025 is what netstat shows me connecting to. Is that the only port I need opened to the server to connect, run a SQL command, and get results back?My machine seems to be initiated the connection from a range of ports in the 2200s so I am not sure if I can lock down the outgoing port.What are the primary ports I need opened to make the connection work through a firewall?Thanks for your help.

1 reply, 14 years ago
Passing the value for an IN clause to a stored procedure. Topic by Matt W. 21 Dec 2005

I'd like to create a stored procedure that can accept a string with multiple values as a parameter, and then put that into an IN clause in the query. Here's an example of what I'm trying to do:CREATE PROCEDURE dss_tables.mw_test_proc(varlist varchar(200), OUT valout smallint)BEGINselect count(branch_id) into :valoutfrom branchwhere mail_state in ( :varlist );END;The idea would be to pass in a list of states ('PA','OH','AL') and get back a total count of branches that are in those states.The stored proc will build OK, and if you pass in one branch it works fine:call dss_tables.mw_test_proc( 'PA', valout);>>36call dss_tables.mw_test_proc( 'AL', valout);>>6But if I try to pass in two or more branches, I always get 0 back. I've tried doubling-up the single quotes several different ways, but with no luck:call dss_tables.mw_test_proc( 'AL'',''PA', valout);call dss_tables.mw_test_proc( '''AL'',''PA''', valout);Any suggestions would be much appreciated.Thanks,

1 reply, 14 years ago
Union query 2616 error Topic by vinod_sugur 02 Jan 2006

select 127 from customerunionselect 131 from customerThe above query generates the subject mentioned the qorkaround is typecast the value 127 to Integer. the reason for the error is the value 127 is considered as Byte and 131 as Integer and the query optimizer tries to convert 131 to Byte, so it gives the subject mentioned error.Is this the only solution?Thanks,

1 reply, 14 years ago
Teradata Error 2621 - Bad character in format or data of {table nm} column 7 Topic by GRH65 10 Oct 2005

Can anyone explain to me what the actual definition of this error is? I have taken a look at the data and nothing looks out of the ordinary. I am creating a couple of volatile tables and updating one of them with info from the other. Again nothing from t2 looks erroneous in such a way that the error would not update t1.Code:CREATE Volatile TABLE B2B_Subs,no logAS (SELECTcal.Sor_id, m.area_desc AS Area, m.region_desc AS Region, cal.cust_id, cal.cust_line_seq_id, cal.acct_num, NULL AS NAICS_Nm , NULL AS emp_tot_num/* September 21, 2005 cals.segmt_value AS Segment */, sdct.sls_dist_chnl_type_desc AS Channel, ca.duns_loc_num, NULL AS CntofUltNum /* September 21, 2005 */, count( AS CntofMTNFROM cust_acct_line_v calINNER JOIN market_v m ON cal.sor_id = m.sor_id AND cal.mkt_cd = m.mkt_cdINNER JOIN cust_acct_v ca ON cal.sor_id = ca.sor_id AND cal.cust_id = ca.cust_id AND cal.acct_num = ca.acct_num AND ca.duns_conf_ind > 5INNER JOIN sales_dist_channel_type_V sdct ON cal.sor_id = sdct.sor_id AND cal.sls_dist_Chnl_type_cd = sdct.sls_Dist_chnl_type_cd AND sdct.sls_dist_Chnl_churn_ctgry = 'Outside'/* September 21, 2005 Pulled due to issue with Segment codes - Customer Segmentation LEFT OUTER JOIN cust_acct_line_segment_v cals ON cal.sor_id = cals.sor_id AND cal.cust_id = cals.cust_id AND cal.cust_line_seq_id = cals.cust_line_seq_id AND add_months(cast('2005-08-01' AS date),1)-1 BETWEEN cals.eff_dt AND cals.exp_dt AND segmt_type_cd = 'HRCHY_SEGMT' */WHERE (cal.line_act_dt BETWEEN 1050801 AND 1050831) AND (cal.line_term_dt is NULL OR cal.line_term_dt > (add_months(cast('2005-08-01' AS date),1)-1)) AND cal.sor_id IN ('I','V') AND cal.rev_gen_ind = 'Y'GROUP BY cal.sor_id, m.area_desc, m.region_desc, cal.cust_id, cal.cust_line_seq_id, cal.acct_num, naics_nm, emp_tot_num, CntofUltNum, sdct.sls_dist_chnl_type_desc, ca.duns_loc_num) WITH DATA PRIMARY INDEX ( Area, Sor_id, Cust_id, Cust_line_seq_id) ON Commit Preserve Rows ;CREATE Volatile TABLE duns,no log AS (SELECT duns1.duns_loc_num duns_loc_num1, duns1.bus_nm bus_nm1, duns1.domestic_emp_tot_num loc_emp_tot, duns2.bus_nm bus_nm2, CASE WHEN duns1.naics_cd1 like ANY ('42%','44%','45%') THEN 'Distribution' WHEN duns1.naics_cd1 like '62%' THEN 'Healthcare' WHEN duns1.naics_cd1 like '53%' THEN 'Real_Estate' WHEN duns1.naics_cd1 like '3254%' THEN 'Pharma' WHEN duns1.naics_cd1 like ANY ('31%' ,'321%' ,'322%' ,'323%' ,'334%' ,'3251%' ,'3252%' ,'3253%' ,'3255%' ,'3256%' ,'3259%', '326%' ,'327%', '33%') THEN 'Indust_Manu' WHEN duns1.naics_cd1 like '51%' THEN 'Media' WHEN duns1.naics_cd1 like '5411%' THEN 'ProfSvc_Legal' WHEN duns1.naics_cd1 like ANY ('5412%' ,'5413%' ,'5414%' ,'5415%' ,'5416%' ,'5417%','5418%' ,'5419%') THEN 'Prof_Svc' WHEN duns1.naics_cd1 like '524%' THEN 'Insurance' WHEN duns1.naics_cd1 like ANY ('521%','522%','523%','525%' )Then 'Invest_Bank' WHEN duns1.naics_cd1 like ANY ('492%','484%') THEN 'Transport' WHEN duns1.naics_cd1 like '22%' THEN 'Utilities' WHEN duns1.naics_cd1 like '61%' THEN 'Education' WHEN duns1.naics_cd1 like '23%' THEN 'Construction' WHEN duns1.naics_cd1 like '922%' THEN 'Safety_Law' WHEN duns1.naics_cd1 like ANY ('921%' ,'923%','924%' ,'925%','926%' ,'927%','928%') THEN 'Government' ELSE 'Other' END NAICS_Desc1 /* Oct 5, 2005 changed again to naics_cd1 Sep. 20, 2005 - changed from sic1_cd to sic1_master_grp_cd */, count(DISTINCT(duns1.duns_ult_num)) ult_num1FROM duns_v duns1LEFT OUTER JOIN duns_v duns2 ON coalesce(duns1.duns_ult_num, duns1.duns_hq_num, duns1.duns_loc_num) = duns2.duns_loc_numWHERE duns1.duns_loc_num IN (SELECT duns_loc_numFROM B2B_Subs) AND duns1.domestic_emp_tot_num BETWEEN 50 AND 499GROUP BY 1,2,3,4,5 )WITH DATA PRIMARY INDEX ( DUNS_LOC_NUM1) ON Commit Preserve Rows ;-----------------Error occurs in this code------------------------UPDATE B2B_SubsFROM (SELECT duns_loc_num1 ,bus_nm2, loc_emp_tot, naics_desc1,ult_num1FROM duns) xSET emp_tot_num = loc_emp_tot, NAICS_NM = NAICS_DESC1, CntofUltNum = ult_num1WHERE duns_loc_num = duns_loc_num1 AND bus_nm2 is NOT NULL;

1 reply, 14 years ago