#DateForumTypeThreadPost
315323 Sep 2014 @ 10:06 PDTDatabaseReplySelecting all rows that have a unique columnUps, sorry, I wrote nonsense, mixing ROW_NUMBER and COUNT: select id, name, email from source_table qualify count(*) over (partition by id rows unbounded preceding) = 1  
315223 Sep 2014 @ 09:13 PDTDatabaseReplySelecting all rows that have a unique columnYou need to use a kind of ROW_NUMBER instead of a group COUNT: select id, name, email from source_table qualify row_number() over (partition by id order by rows unbounded preceding) = 1 But f...
315123 Sep 2014 @ 09:03 PDTAnalyticsReplyTeradata OLAP functions in SQLHi Sue, I'm not sure, but you might do the 2nd sum in a Derived Table and then cross join to prdlkup and use a SUM OVER like this, fully untested :-) select a.prd_id, a.prd_name, ...
315021 Sep 2014 @ 10:53 PDTDatabaseReplyDBC.DATABASESVXCurrentperm and maxperm are stored in dbc.DiskSpaceV for each AMP/database.
314921 Sep 2014 @ 10:51 PDTDatabaseReplyhelp with recursive sqlYou must define WITH first and then SELECT from it: WITH RECURSIVE parse_list (input_date, site_id, delim_pos, item_num, element, remainder) AS ( SELECT input_date, site_i...
314821 Sep 2014 @ 03:54 PDTDatabaseReplyNO PRIMARY INDEX table in TeradataLOAD in a NoPI table is approx. 30-40% faster. Insert/Select from NoPI to PI is slower than tables with matching PIs because there must be a redistribution/sort instead of a direct merge.   ...
314721 Sep 2014 @ 03:17 PDTGeneralReplyStored ProcedureSimply create the Volatile table first and then the SP.   Of course you should have some code within your SP which deals with an existing VT, otherwise you can't CALL it a 2nd time withi...
314621 Sep 2014 @ 03:13 PDTDatabaseReplyProfiles and Account StringsNo DBC table needs to be updated (in fact you can't update those tables directly). You simply run a  MODIFY PROFILE myProfile AS ACCOUNT = ('aaa','bbb','bbb'); ...
314520 Sep 2014 @ 04:47 PDTAsterReplygraphGen output tableInteresting option, never heard of it... I don't have access to an Aster system right now, but your quote seems to imply you can simply set the output_format to 'nodes_and_edges'. If t...
314420 Sep 2014 @ 04:28 PDTDatabaseReplyHow to verify Data Pattern in a ColumnHi Hara, what's your TD release? In TD14 there are Regular Expressions which can easily do what you need.
314320 Sep 2014 @ 04:27 PDTDatabaseReplyGrants needed for a triggerThe owner of an object is the database where it's created. This should fix the error: GRANT INSERT ON "DEV_DB" TO "DEV_DB" WITH GRANT OPTION;
314219 Sep 2014 @ 10:50 PDTGeneralReplyConvert to HexThere's no built-in way to cast from/to bytes besides those new functions in TD14. You need to write a C-UDF.
314119 Sep 2014 @ 10:32 PDTAnalyticsReplycan we have Rank function using rows between 1 preceding 1 preceding You can utilize the RESET WHEN option. Untested: select ..., row_number() over (partition by userid order by date reset when date - case when channel type = ...
314019 Sep 2014 @ 10:26 PDTAnalyticsReplyTeradata OLAP functions in SQLHi Sue, the p query is strange, do you really need a cross join between a and b? Is b.prdname actually including wildcards? Can you show some input/expected result set?
313919 Sep 2014 @ 10:24 PDTAsterReplyHistogram_Map function - need helpHi Wendy, the histogram_map/histogram_reduce combination only creates tabular output. As Aster Lens only supports NpathViz and CFilterViz output you need a visualization tool to get graphical out...
313819 Sep 2014 @ 10:20 PDTDatabaseReplyall-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation DifferenceThis is usually caused by a bad Primary Index on a SET table resulting in a huge amount of duplicate row checks. Check the number of rows per PI value of the target table.  
313719 Sep 2014 @ 02:34 PDTDatabaseReplyNo room available in Database testdb.Hi Parth, there's no way to increase the permspace of a TDE, next time download the 40GB or 1TB version, not the 4GB version.   Btw, "analyse DBC.TableSize & DBC.DiskSpace"...
313617 Sep 2014 @ 12:54 PDTGeneralReplyConvert to HexI didn't know you can apply TO_CHAR to a CHAR :) Hopefully you're on TD14, TO_BYTES returns BYTE which can be used as input to FROM_BYTES to get a VARCHAR again: SELECT FROM_BYTES(TO_BY...
313517 Sep 2014 @ 10:29 PDTGeneralReplyConvert to HexYour example will not work as-is in Oracle. What is the datatype of your input, VarChar, VarByte or any INT? And what do you want as output, VarChar or VarByte?   In TD14 you might utilize...
313417 Sep 2014 @ 04:09 PDTAnalyticsReplyOutlier Treatment in TeradataHi Abhinav, seems like you have to talk to your DBA to get a higher spool limit.
313316 Sep 2014 @ 11:25 PDTAnalyticsReplyOutlier Treatment in TeradataHi Abhinav, this is not going to work, you must change the count to COUNT(*) OVER (). And you can probably remove the GROUP BY, too. What are the datatypes of those columns? 
313216 Sep 2014 @ 12:16 PDTDatabaseReplyThe Quartile splitup Which trying to approach in Query Level .I didn't read all that text, but it seems like your narration doesn't fit the definition of a quantile. Might be a equally sized buckets instead, check WIDTH_BUCKET: WIDTH_BUCKET(salary...
313116 Sep 2014 @ 11:32 PDTDatabaseReplyUse While loop in Stored Procedure in TeradataThere's an END missing: CREATE PROCEDURE proc ( IN i INTEGER, IN j INTEGER) BEGIN DECLARE ii integer; set ii=i; while (ii<j) do begin set i...
313016 Sep 2014 @ 11:27 PDTDatabaseReplyTIme Difference Hi Jugal, you cast current_time to a CHARs and then substract two CHARs, what do you expect?   If you want the result to be a CHAR: CAST(CAST(CURRENT_TIME AS FORMAT 'hh:') AS CHA...
312916 Sep 2014 @ 11:09 PDTDatabaseReplySQLYou might simply utilize Teradata's sys_calendar (or yor own calendar implementation): WHERE (Year_No, Month_No) IN (SELECT year_of_calendar, month_of_year FROM sys_calendar.cale...

Pages