#DateForumTypeThreadPost
2826 May 2006 @ 12:13 PDTToolsReplyDate Comparisions - Differing formatsJust specify a matching format, if the day is missing it will be replaced by the 1st.where '200605' (date, format 'yyyymm') between date1 and date2Dieter
2726 May 2006 @ 02:58 PDTDatabaseReplyData Block sizeWhy don't you simply check the manuals?SQL Reference: Data Definition Statements, CREATE TABLEDieter
2626 May 2006 @ 02:54 PDTDatabaseReplyis it is it possible to display contents of a table through stored procedure?You can't return an answerset through a SP, just a single line using SELECT INTO.I don't understand, why you ever want to do that, but you could use dynamic sql within your SP, e.g.:create procedur...
2526 May 2006 @ 02:46 PDTDatabaseReplyComparing two tablesMINUS: spool both tables & redistribute -> sort distinct -> exclusion merge joinIs the table SET or MULTISET? If it's MULTISET you'll need MINUS ALL, if it's SET, then MINUS ALL is more efficient, ...
2419 May 2006 @ 11:54 PDTUDAReplySQL to retrieve datatype of a column> select distinct type(my_foo_col) from foobar;will be heavily skewed (all rows on a single AMP)Better use: select type(foobar.myfoocol);Don't ask me why, but this is not accessing the table: -> T...
2316 May 2006 @ 07:59 PDTDatabaseReplyQuery to get the last day of each month in a year.Hi Angel,where hire_date = add_months(hire_date,1) - extract(day from add_months(hire_date,1))Dieter
2212 Apr 2006 @ 12:14 PDTToolsReplyHelp required BTEQ IMPORTHi Musomasujust use a multistatement:.LOGON demotdat/dbc,123;.import vartext file=params.txt;using eno(varchar(1)) sel * from abc.emp where emp_no=:eno;using eno(varchar(1)) sel * from abc.manager ...
2111 Apr 2006 @ 01:02 PDTTrainingReplyHow many Certified Teradata MastersSorry not to be clear:750Dieter
2008 Apr 2006 @ 04:42 PDTTrainingReplyHow many Certified Teradata MastersHi Job_Agent,during last year's Partners the official count was announced to be about 750...Dieter
1904 Apr 2006 @ 02:00 PDTDatabaseReplyhow to find version of a teradata database?select * from dbc.dbcinfo;Dieter
1802 Apr 2006 @ 03:42 PDTToolsReplyTeradata SyntaxHi Vincent,in V2R5.1+ the easiest way would be a UDF.If a.chargeable_drtn is a decimal > 0:case when (x mod 1) = 0 then x else x + 1 - (x mod 1) endor:cast(x + 0.9 as int) -- for dec(x,1)cast(x + 0...
1702 Apr 2006 @ 03:06 PDTDatabaseReply3706 Syntax error with select statementHi Michael, try"and col2
1602 Apr 2006 @ 02:58 PDTDatabaseReplyError Using an Alias Name - Is CM a reserved keyword in TeraDataHi Sam,CM is an abbreviation for CREATE MACRO, so it's a keyword.You'll find a list of all keywords in:SQL Reference: FundamentalsAppendix B: Restricted Words for V2R6.1Dieter
1530 Mar 2006 @ 01:03 PSTDatabaseReplySyntax error in View definition (nested query)Hi Vasu,are you trying to port SQL from another DBMS to Teradata? Because you didn't run the select before: You can't use scalar subqueries (= subquery within column list) in Teradata, but they can...
1426 Mar 2006 @ 10:23 PSTDatabaseReplyNeed to identigy Primary key from a table using SelectHi Rishab,if the table was created with a Primary Key constraint, it has been replaced by a UPI/USI. There's a PrimaryKeyIndexId in dbc.tables, which indicates the original PK and relates to dbc.in...
1322 Mar 2006 @ 03:28 PSTDatabaseReplyselect first N recordsNon-Standard TOP syntax is supported since V2R6...It can be replaced by Standard SQL RANK/PERCENT_RANK/ROW_NUMBER in most cases.It's only useful for a "select top ... * from table" without any orde...
1217 Mar 2006 @ 10:05 PSTDatabaseReplyscript of teradata tablesHi dixon,there's a script in C:\Program Files\NCR\Tdat\LTDBMS\etc\perddl.bteq, but the sample db is quite small.It's described in the manuals:Basic Teradata Query Reference APPENDIX B Loading and U...
1117 Mar 2006 @ 09:53 PSTDatabaseReplyHow can I spot strings that are in UPPER case?select *from tabwhere col (casespecific) = upper(col)orwhere col (cs) = upper(col)Dieter
1013 Mar 2006 @ 08:35 PSTDatabaseReplywhere does TD store transient journal?In perm space -> dbc.transientjournalBut that special table can grow over dbc's perm limit until the whole system runs out of perm space.Dieter
907 Mar 2006 @ 10:53 PSTToolsReplyMLOAD acquisition phase countHi Vikas,you are wrong ;-)But you can access those variables only after the END MLOAD.If you need that info *during* the load you'll have to program a NOTIFY exit.Dieter
807 Mar 2006 @ 07:40 PSTToolsReplyMLOAD acquisition phase countHi Vikas,what version of MLoad are you using?MLoad supports that since several years:SYSAPLYCNTSYSNOAPLYCNTSYSRCDCNTSYSRJCTCNTCheck the manuals for details...Dieter
707 Mar 2006 @ 06:52 PSTDatabaseReplyTimestamp format on teradata retrievalHi Jas,select '03/07/2006 12:49:29' as ts, ts (timestamp(0), format 'MM/DD/YYYYBHH:MI:SS')Dieter
627 Jan 2006 @ 02:58 PSTDatabaseReplyGrant References...Hi TD_Arch,did you try that before? *** Failure 3706 Syntax error: Attempt to grant/revoke REFERENCES on database object.Hi dsearl,sorry, but you can't grant references on a database level.Why do y...
520 Jan 2006 @ 11:33 PSTDatabaseReplyCollect StatsHi Luke,sorry, but you won't find any documentation about it.Most DIAGNOSTIC statements are undocumented, but sometimes you can guess the meaning of a keyword :-)Dieter
420 Jan 2006 @ 07:50 PSTDatabaseReplyCollect StatsHi Luke,the global flag is set by dbscontrol -> internal field 46: CollectStatsSampleOn a session level it's:DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=xx" ON FOR SESSION;For a singe Collect Stats:DIAGNO...

Pages