#DateForumTypeThreadPost
397817 Sep 2015 @ 07:21 PDTDatabaseReplyCase Statement select case Status when 3 then 'Approved' when 4 then 'Declined' else 'In Progress' end, count(*) ... group by 1  
397711 Sep 2015 @ 08:33 PDTDatabaseReplyWhich DBS control settings affect Teradata Performance?In most environments both Prod and Dev will have exactly the same settings.
397611 Sep 2015 @ 08:31 PDTDatabaseReplyPartitioning IssueA single row table can be replaced by a view without accessing any table. In that case the optimizer always knows the actual value, similar to the dynamic plan in TD14.10, but without cost threshol...
397511 Sep 2015 @ 08:20 PDTViewpointReplyUnderstanding REQ CPU in Query SpotlightCPU seconds <> clock seconds There's more than one CPU in a your system, e.g. if you got 100 CPUs there are 100 CPU-seconds per second.
397410 Sep 2015 @ 01:15 PDTPrivate Forum
397310 Sep 2015 @ 12:54 PDTDatabaseReplyWhich DBS control settings affect Teradata Performance?DBS control settings regarding performance (there's just a small number) usually default to a proper value. Besides increasing DictionaryCacheSize and maybe MaxParseTreeSegs you...
397210 Sep 2015 @ 12:37 PDTDatabaseReplyTeradata Query to handle previous day amount select ID, Date, Amount, Amount + coalesce(max(amount) over (partition by ID order by Date rows between 1 prece...
397110 Sep 2015 @ 12:33 PDTDatabaseReplyNewbie to TeradataAll metadata is found in dbc-views, details can be found in the Data Dictionary manual.   Access rights for all users/databases: dbc.AllRightsV Access rights based on roles: dbc.AllRoleRigh...
397009 Sep 2015 @ 10:50 PDTDatabaseReplyPartitioning IssueAssuming that TMP_RUNDATE is a single row table this should work as expected if you replace it with a View: replace view TMP_RUNDATE as select date '2014-10-01' as RUN_DATE  
396908 Sep 2015 @ 10:12 PDTDatabaseReplyConvert scientific notation to decimalCAST(col AS NUMBER) should work
396808 Sep 2015 @ 10:12 PDTDatabaseReplyPartitioning IssueIn TD14.10+ "Incremental Planning and Execution" should be switched on, so settting the Queryband will not help.  But your test data seems to be small (based on estimated rows), so ...
396708 Sep 2015 @ 01:29 PDTDatabaseReplySQL Warning 5815 Function is not ANSIFlagging SQL compliance was a SQL-92 feature, CAST is Standard SQL, but not in Entry Level. A copy of SQL-92 can be found at: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
396606 Sep 2015 @ 01:38 PDTDatabaseReplyStored Procedure (SELECTION FORM) EXPLAIN/SHOW/HELPThe previous page: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1148_112A/ch03.149.062.html Invocation Nonexecutable preprocessor declaration. ...
396505 Sep 2015 @ 08:34 PDTDatabaseReplyStored Procedure (SELECTION FORM) EXPLAIN/SHOW/HELPThe manual is for both Embedded SQL and Stored Procedures and clearly states that you cannot use EXPLAIN/SHOW/HELP in a SP: http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_R...
396405 Sep 2015 @ 05:05 PDTGeneralReplyHow to find CONSECUTIVE values coming in a column(TERADATA)There's no "original sequence" in a table in a relational DBMS. You need to add an ORDER BY to get a reliable & repeatable result, if there's no ordering column you're ou...
396331 Aug 2015 @ 01:30 PDTGeneralReplyconvert julian 5 digit number to regular dateThis behaviour is controlled by a global setting (CenturyBreak in dbccontrol). Simply add '20' before casting: CAST('20'||trim(Date_Created) AS DATE FORMAT 'yyyyddd') &...
396231 Aug 2015 @ 01:24 PDTGeneralReplySpoil Table information in Data Dictionary"We spoil the parser's dictionary cache for the table" removes all chached metadata about this table, e.g. after a ALTER TABLE or a CREATE INDEX, or after DELETE/UPDATE/INSERT of...
396131 Aug 2015 @ 01:19 PDTGeneralReplyHow to create a dynamic concatenationMultiplying by 1000000 shifts the BR_NO seven digits to the left before adding the seven digits of ACCT_NO.
396031 Aug 2015 @ 10:59 PDTDatabaseReplyAggregationHi Gnana Reddy, you want a moving sum: sum(col4) over (partition by col1,col2 order by col3 rows between 3 preceding and current row)  
395931 Aug 2015 @ 10:34 PDTDatabaseReplyNeed help to write an SQL to validate mutiple date ranges combined from more than one SCD type2 tables.Your INSERTs don't match your example. If your step #2 returns the correct data you can simply add a LAST_VALUE: WITH cte ( hid ,strt_dttm ,end_dttm ,h1name ,h2name ) AS ( SE...
395831 Aug 2015 @ 08:45 PDTDatabaseReplyNeed help to write an SQL to validate mutiple date ranges combined from more than one SCD type2 tables.What's your Teradata release?
395731 Aug 2015 @ 08:43 PDTGeneralReplyHow to create a dynamic concatenationYou didn't tell that those columns were numeric.  Concatenation on numerics results in an automatic typecast, which results in leading blanks. You get rid of them when you TRIM:  ...
395631 Aug 2015 @ 12:44 PDTDatabaseReplycast varchar value YYYY-MMM-DD HH:MI:SS to dateAny invalid data will result in "Invalid Timestamp". Why is this column defined as a VarChar if it's supposed to be a Timestamp? This should be casted during load. Instead of trying...
395529 Aug 2015 @ 09:32 PDTGeneralReplyHow to create a dynamic concatenationThere's an LPAD function: cast(LPAD(BR_NO || ACCT_NO, 12, '0') as char(12)) || 'TDA'  
395429 Aug 2015 @ 09:25 PDTDatabaseReplyTRIM Leading letters then Leading 0The easiest way to get what you want is probably a regular expression, find the first digit which is not a zero: regexp_substr(col, '([1-9].*)')  

Pages