#DateForumTypeThreadPost
420323 Feb 2016 @ 03:12 PSTDatabaseReplyHow Compression WorksHi Vlcik, nope, the NULL is already included :-) See Number of Presence Bits Required to Represent Compressed Values in the Database Design manual: http://www.info.teradata.com/HTMLPubs/DB_TTU_1...
420218 Feb 2016 @ 11:35 PSTDatabaseReplyAggregating records within a set time period that resets from first occurrenceWhy are there only two rows for customer 'D'? When the 14 day period starts on Dec 1. then period #2 should be Dec 15. - 28. and #3 starts on Dec 29., so the last two rwos should be in sep...
420118 Feb 2016 @ 01:20 PSTDatabaseReplyDelete query - performance hitHow many rows are in Table1? What's the PI, is the table Partitioned, Secondary Indexes, etc.? Can you post the DDL & Explain?   And what's exactly "facing performance iss...
420018 Feb 2016 @ 12:02 PSTDatabaseReplyHow loop through table columns to determine distinct valuesThe easiest way is to create the Selects dynamically by joining to dbc.ColumsV top get all column names: SELECT 'SELECT vw_TPDDP.System_NME ,vw_TDAD.Date_YYYY_FMT ,vw_TDAD.Date_M...
419917 Feb 2016 @ 11:45 PSTDatabaseReplyCalculation of prescription drug adherenceHi Brent, you're welcome, I'm currently writing a book on advanced SQL, always looking for real-world examples :)   in fact, this is much easier because there's no shifting...
419817 Feb 2016 @ 11:10 PSTDatabaseReplyHelp with SQL for reporting month totalsFinally I double checked logic & syntax :-) SELECT -- maybe add the year to the FORMAT 'yyyyBmmmmm' -- or simply keep the date as-is, then you don't need the outer Select...
419717 Feb 2016 @ 03:07 PSTDatabaseReplyHelp with SQL for reporting month totalsMaybe I should test before I write :-) Of course mon is a number, no date format. SELECT mon, TM_Loc_cd, shpmnts FROM ( SELECT EXTRACT(YEAR FROM isrt_tmstp) AS yr, ...
419617 Feb 2016 @ 02:37 PSTDatabaseReplyHelp with SQL for reporting month totalsArrrrgh, I should read before I post: TRIM(mon (FORMAT 'mmmm')),   Finally...
419517 Feb 2016 @ 01:55 PSTDatabaseReplyHow to identify and select ROW, where there was a change in value in specific columnHi Galad, I don't think there's a way without nested OLAP, e.g. SELECT * FROM ( SELECT * FROM tab QUALIFY -- start with the latest row and go on as long as the TYPE_CD d...
419417 Feb 2016 @ 01:23 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?There's alway a regex :-)   REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) RETURNS VARCHAR(255) LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DE...
419317 Feb 2016 @ 12:56 PSTDatabaseReplyHelp with SQL for reporting month totalsToo much cut & paste... It's the EXTRACT(MONTH FROM DATE) AS mon, of course this should be EXTRACT(MONTH FROM isrt_tmstp) AS mon, instead. DATE = CURRENT_DATE = February 2016 :-)
419217 Feb 2016 @ 08:50 PSTDatabaseReplyHelp with SQL for reporting month totalsLook at your WHERE-condition, in the query you posted it was limiting to rows from february, my version uses multiple months.
419117 Feb 2016 @ 07:58 PSTDatabaseReplyHelp with SQL for reporting month totalsSeems you run a pre-TD14 version. Replace it with TO_CHAR(mon, 'Month') with TRIM(DATE (FORMAT 'mmmm'))
419017 Feb 2016 @ 07:51 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?Oops, of course, this is a regex meta character, forgot to escape it: REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) RETURNS VARCHAR(255) LANGUAGE SQL CONTAINS SQL...
418917 Feb 2016 @ 06:37 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?No declared variables, no steps. In fact I'm glad that no complex things are allowed, otherwise people would start writing stupid UDFs with loops/Selects, etc. and then try to use them on that...
418817 Feb 2016 @ 06:07 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?The function I posted does both parts, the REGEXP_REPLACE replaces multiple occurrences of a character with a single. You could do the same in Oracle.
418717 Feb 2016 @ 05:00 PSTDatabaseReplyHow to make an anonymous piece of code to execute it once?You can only write, but not read an OUT parameter. You need to declare a variable holding the intermediate result: REPLACE PROCEDURE F_QUITA_REPES (IN cCadena VARCHAR(252), IN delchar CHAR, OUT...
418617 Feb 2016 @ 03:39 PSTDatabaseReplyHow to make an anonymous piece of code to execute it once?You're calling the SP within another SP? Then you need to declare resul.   Standalone (outside of an SP) there will be no error when you simply call it.
418517 Feb 2016 @ 03:17 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?SPs only return OUT parameters (or result sets), but not a single value like a function, so you can't use it directly and your proposed alternative is the only way.
418417 Feb 2016 @ 02:52 PSTToolsReplyUpload files .dat in Teradata Hi Valeria, of course Teradata's load tools support fixed length data, depending on your actual data & tool it's e.g. Format = 'Text' in TPT for all character fixed l...
418317 Feb 2016 @ 02:45 PSTDatabaseReplyHelp with SQL for reporting month totalsGet the year/month using EXTRACT SELECT TO_CHAR(mon, 'Month'), TM_Loc_cd, shpmnts FROM ( SELECT EXTRACT(YEAR FROM isrt_tmstp) AS yr, EXTRACT(MONTH FROM D...
418217 Feb 2016 @ 02:31 PSTDatabaseReplyHow to make an anonymous piece of code to execute it once?You simply CALL the procedure, no need to declare the result: CALL TESTPROC('XXXblablablaXXX', 'X', Resul); This returns a result row with "Resul"
418117 Feb 2016 @ 02:29 PSTDatabaseReplyHow to create a function similar to Oracle (or a procedure with a return value)?SQL functions in Teradata are very limited, no SELECT, loops, etc. For complex logic you need C or Java.   But in your case the logic can easily be rewritten using regex, now it's one co...
418015 Feb 2016 @ 09:57 PSTDatabaseReplyjoin conditions have different format If the cast to an INT fails there are some characters outside of '0' to '9'. You can check which rows fail using SELECT account_id FROM A WHERE TO_NUMBER(account_id) IS NULL; ...
417915 Feb 2016 @ 07:55 PSTGeneralReplyDirty Reads <Requesting Guidance>The actual number is not known in advance, Dirty Read means reading the data as-is, this will return the rows inserted before the Select starts and maybe some of the rows inserted after.

Pages