#DateForumTypeThreadPost
105307 Jul 2011 @ 12:03 PDTUDAReplyDynamically update timezone.Hi Dave, all TIME/TIMEZONE/INTERVAL datatypes must be defined as (VAR)CHAR: .define ts varchar(xx) If the data doesn't match the format of the target column you have to typecast using an ap...
105205 Jul 2011 @ 01:10 PDTDatabaseReplyQry runs slow2 hours? You just have to wait another 382,436,214 hours :-) Just remove this part, its only confusing the optimizer: "(coalesce(null, NULL ) is null) OR " And whenever you mix AND and OR...
105105 Jul 2011 @ 09:47 PDTToolsReplyHow to use EXEC SQL in Bteq ?No and yes, yes ;-) dbc.sysExecSQL is only valid in SPs, so you could create an SP which CALLs dbc.sysExecSQL and then CALL this SP in Bteq. BUT, this is dangerous, keyword "SQL injection" O...
105005 Jul 2011 @ 09:36 PDTDatabaseReplySome Problem with Bteq !!!! You should open an incident with Teradata support. Dieter
104905 Jul 2011 @ 09:35 PDTDatabaseReplyNeed to monitor the Backups running on Teradata database.All the info which is available in the Performance Monitor can also be retrieved using the PMon/API SQL-UDFs in the syslib database. Check the "Workload Management API" manual for details. Di...
104805 Jul 2011 @ 09:30 PDTDatabaseReplyRedistribution to all AMPsHi Andrey, the redistribution is usually based on the join columns, you can see the actual columns in EXPLAIN. Rewriting the query might change the plan in some rare cases, e.g. if you join on ...
104705 Jul 2011 @ 09:25 PDTDatabaseReplySQL help in summing only selected records based on max value within groupYep, you're probably right. If the query without RANK doesn't return the expected (plus the unwanted) rows/data, you have to move it into a Derived Table: ... FROM (select ... from fint_view.sb...
104605 Jul 2011 @ 08:43 PDTDatabaseReplySQL help in summing only selected records based on max value within groupThe RANK is processed after the GROUP BY, it's similar when you try to user a.sbt_typ_cd in HAVING, the detail data doesn't exist anymore. Just use the alias "Line Items" in the PARTITION clause. ...
104502 Jul 2011 @ 01:21 PDTDatabaseReplySQL help in summing only selected records based on max value within groupIt's OLAP time :-) select month, tran_code, sum(qty) from tab group by 1,2 qualify rank() over (partition by trans_code order by month desc) = 1 Dieter
104401 Jul 2011 @ 04:01 PDTGeneralReplysource as table in utilities?Of course it's possible as part of the job preparation/cleanup. Before the BEGIN MLOAD and after the END MLOAD you can use almost any SLQ statement. Dieter
104327 Jun 2011 @ 06:34 PDTDatabaseReplyNeed to insert 39 digit integer value in teradata You can't insert an integer into a BYTE column, you need binary data, i.e. your MD5 function should return BYTE(16). INSERT INTO Smpl VALUES ('79B69E37F3713C6E6D27DAA6E6E278DD'xb) Dieter
104227 Jun 2011 @ 06:09 PDTDatabaseReplyNeed to insert 39 digit integer value in teradata There's a 128-bit decimal in Teradata, but it's precision is 38 digits. MD5 usually returns a 128 bit hex value, but no integer, so you better use BYTE(16) to store it. Dieter
104127 Jun 2011 @ 03:43 PDTDatabaseReplyUse of Multiset tables?No, of course we do :-) You talked about "business scenario where bussiness really need the duplicate records" But most multiset tables are created due to performance reasons. Primary Keys w...
104027 Jun 2011 @ 02:07 PDTDatabaseReplyUse of Multiset tables?As Teradata is a Relational DBMS and Relational Model requires a Primary Key, there should be no business reason for duplicates. The only place where duplicate rows are usefull is the staging area...
103927 Jun 2011 @ 01:55 PDTGeneralReplyTERADATA SQL QUERY REQUIRED (SCD TYPE 2)select * from tab qualify row_number() over (partition by empno order by column_which_indicates_the_change_order desc)
103827 Jun 2011 @ 01:53 PDTGeneralReplyDELETING DUPLICATES I NEED SQL QUERY IN TERADATARemove duplicates in SELECT? DISTINCT or GROUP BY Actually delete duplicates from table? - insert/select into a new SET table or - insert/select into a new MULTISET table using DISTINCT/GROUP ...
103724 Jun 2011 @ 08:55 PDTDatabaseReplyWhich one is better?Typecasting to a CHAR is worst case, every value in every row needs to be typecasted and all statistics are lost. And a DATE literal should always include the DATE keyword: DATE '2000-01-01' ...
103623 Jun 2011 @ 03:37 PDTDatabaseReplyRunning longThis must be a tool creating the SQL, no human mind will be so twisted. where F.ASGN_STS in ('EFG', 'HIJ','DEF') was for the CASE before the INSERT in your first post. And there was no "case ...
103523 Jun 2011 @ 03:17 PDTDatabaseReplyCase stmtThis seems to be the same as WHERE A.CD IN('a','b','u','t') OR A.CD NOT IN ('a','b','c','d','e','f','g','h','u','t') which can be further simplified to WHERE A.CD NOT IN ('c','d','e',...
103423 Jun 2011 @ 02:14 PDTDatabaseReplyRunning longWho is writing all those queries you post here? Most of the source code is (how shall i put this) "overly complex" :-) You probably don't need any of those CASEs, your example is exactly the sa...
103323 Jun 2011 @ 06:23 PDTDatabaseReplyConverting DECODE into CASE statementI didn't notice the 2nd table, so my answer was wrong. But your CASE doesn't match the original decode, too, there was no 'ISO Certified': CASE WHEN NOTES_QS_CODE.name = 'QS-9000 Certified' TH...
103223 Jun 2011 @ 06:14 PDTDatabaseReplyDate diferenceYou're right, this is how it's implemented. Standard SQL and common sense sometimes differ :-) If you want the age you can use a UDF, e.g. the existing Oracle MONTHS_BETWEEN typecasted to an IN...
103122 Jun 2011 @ 01:43 PDTDatabaseReplyInteger value conversion to Time format There's no automatic typecast for TIMEs, you have to write an explicit cast. Is 9552800 supposed to be 9:55:28? int_col / 100 (format '99:99:99') (char(11)) (time(0)) Dieter
103022 Jun 2011 @ 01:31 PDTDatabaseReplyTimestamp format on teradata retrievalselect cast(cast('03/07/2006 12:49:29 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22)) 'T' is used for 12-hour format and 'B' for blanks, check the "SQL Datatypes and Literals...
102922 Jun 2011 @ 01:25 PDTDatabaseReplyDate diferenceThis was a "bug" (according to Standard SQL) which has been fixed in V2R6.2. Now it's just like "Extract(year from date1) - extract(year from date2). Most people prefered the old way to do the ...

Pages