#DateForumTypeThreadPost
340309 Jan 2015 @ 12:48 PSTDatabaseReplyFeature Request - It would be nice if we could join on null without having to do extra workWhat you're asking for will break any existing code and compatibility with Standard SQL. Is it so much work to add another condition to the join? ON (t1.col = t2.col OR (t1.col IS NULL AND ...
340209 Jan 2015 @ 12:36 PSTExtensibilityReplySQl UDF - beyond SCALAR UDFHi Ajay, SQL UDFs don't support SELECTs in 15, too. Of course you could write a C/Java UDF returning a result set, just don't ask me about the details, that's not my topic :-) But ar...
340109 Jan 2015 @ 12:27 PSTDatabaseReplyChange accout string of sessions outside of your ownChanging the sesssion priority is possible using the SetSessionAccount function, check: http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/Database_Management/B035_1090_015K/System_PMPC_APIs....
340009 Jan 2015 @ 12:19 PSTAnalyticsReplyPassing a string into a macro for use in a IN statementYou can't pass a list of values as a parameter, you need Dynamic SQL which is only available in SPs. But in TD14 you can utilize STRTOK_SPLIT_TO_TABLE: WHERE item_nbr IN ( SELEC...
339909 Jan 2015 @ 12:15 PSTDatabaseReplyAggregating activity from the Fact tableThis is quite similar to another post from today: create SDC from a history table   You can use the same approach, just simplified: SELECT S_ID, Activity_Dt ,COALESCE(MIN(Activity_Dt)...
339809 Jan 2015 @ 03:36 PSTDatabaseReplyBug in SQL Assistant 15.00According to the readme this issue has been fixed in SQL 15.0.0.3
339709 Jan 2015 @ 03:34 PSTAnalyticsReplycreate SDC from a history tableCombine rows if there's no gap and the status didn't change? SELECT col1 ,start ,COALESCE(MIN(prevEnd) -- next rows end OVER (PARTITION BY col1 ...
339609 Jan 2015 @ 01:49 PSTGeneralReplyRow Tagging With Additional Session Number ColumnHi Christoph, the example data doesn't match the query/your description, but what you want is the SESSIONIZE function in Aster :-) In Teradata you need nested OLAP: SELECT SUM(CASE WHE...
339508 Jan 2015 @ 11:53 PSTGeneralReplyUnable to load double quoted values using Multiload optionHi Karthick, check the QUOTE option of FORMAT VARTEXT: http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/Load_and_Unload_Utilities/B035_2409_071A/2409ch03.036.059.html
339406 Jan 2015 @ 05:10 PSTDatabaseReplyMload in with multiple delimiterThe STRTOK function is similar to one of the Ebay UDFs (eListGetValByIdx) found at  http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs   Ask your DBA...
339305 Jan 2015 @ 04:43 PSTDatabaseReplyMload in with multiple delimiterFIELD1 = :STRTOK(actvy_msg_txt,'|',1) is no valid syntax for an INSERT, should be something like STRTOK(:actvy_msg_txt, '|', 1)
339205 Jan 2015 @ 03:00 PSTAnalyticsReplyPlease help : Query to count number of voucher cards usedHi Teja, did you try a simple LEFT JOIN ON seq_nbr BETWEEN From_seq_nbr AND to_seq_nbr? Of course this is a product join, but CPU usage might be ok.   Otherwise: Is there a fixed ran...
339105 Jan 2015 @ 02:55 PSTDatabaseReplyDelete Query in PPI vs Alter Statement Drop Range faster and better .Hi Jithin, as I wrote, I prefer DELETE over DROP. But for a table with only 500k rows I would hardly consider adding partitioning, any way to delete should be fast enough :-)  You might sim...
339005 Jan 2015 @ 02:51 PSTDatabaseReplyRandom Number Generator based on inputs from column valuesHi Nihal, RANDOM only accepts hard-coded values. You might try SELECT A.*, 10 AS TEST, RANDOM(1,2147483647) MOD TEST + 1 FROM TABLE_NAME A;  
338904 Jan 2015 @ 09:44 PSTDatabaseReplyDelete Query in PPI vs Alter Statement Drop Range faster and better .Hi Jithin, in your example can't use DROP because you can only drop full partitions an not 12 days out if a months. Otherwise I prefer DELETE over DROP PARTITION: an empty partition has ...
338804 Jan 2015 @ 09:36 PSTDatabaseReplywe do a merge into table TABLE1 from spool 12345There's a 99% probabilty that the target is a SET table with a bad Primary Index (usually defaulted to 1st column) and there are lots of rows per PI-value.
338702 Jan 2015 @ 10:06 PSTAnalyticsReplyAdding a row for a non existing transactionYou might already have all needed rows in the SALES_MULTIPLIER table. Of course then you need to change the join to a RIGHT join and change the columns from D.whatever to Q.whatever in your S...
338602 Jan 2015 @ 02:50 PSTAnalyticsReplyAdding a row for a non existing transactionThe only way to get non-existing data is using a list of all needed values, e.g. in a Left Join
338501 Jan 2015 @ 09:19 PSTDatabaseReplyCOLLECT STATSHi John, it's not the same as the first COLLECT will fail without brackets around FACTOR_ID :-)   Otherwise both are processed exactly the same, simply check Explain.
338401 Jan 2015 @ 04:26 PSTDatabaseReplyQuery on efficient join and update Hi Santanu, what's the number of rows in your calendar? You should trust the optimizer, can you show the Explain?
338301 Jan 2015 @ 04:17 PSTToolsReplyTERADATA TPUMPHi Shankar, everything's in the manuals, but not always easy to find :-) http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/Database_Management/B035_1094_015K/ch08.060.076.html
338201 Jan 2015 @ 04:04 PSTDatabaseReplyFind Second Highest Salary - SQLHi Santanu, if you want to avoid DENSE_RANK your not on TD14.10? There are several ways to calculate a DENSE_RANK, all of them need nested OLAP-functions: Missing Functions: DENSE_RANK For your...
338131 Dec 2014 @ 04:02 PSTDatabaseReplywhere does TD store transient journal?MODIFY DATABASE DROP DEFAULT JOURNAL is not related to the Transient Journal, this is for Permanent Journals only. Your BTEQ job probably logged on using an ANSI session, so all INSERTs are part o...
338031 Dec 2014 @ 03:58 PSTDatabaseReplyGET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong outputHi Parth, I just tried it on a 15.00.01.04 and it was the same. ROW_COUNT is defined as an INT (why not as BIGINT?), but seems to return the range of a SMALLINT.  I didn't notice that b...
337931 Dec 2014 @ 03:30 PSTDatabaseReplyusing update with WITH clauseHi Parth, randomly updating a table? Why? Is there no key? Can you show the actual definition? What's the size of those tables?   The only way might be inserting the data into a new ...

Pages