#DateForumTypeThreadPost
85321 Dec 2010 @ 11:36 PSTDatabaseReplyJoins on columnsWhere do you get that data from? Your data source should provide usefull data, not that kind of garbage. The only way to achieve your desired result using SQL is to split that string into rows ...
85221 Dec 2010 @ 01:46 PSTDatabaseReplyHow to use LIKE in queriesTablename is a CHAR(30) and any comparison using LIKE does *not* append trailing blanks like "=" etc. Two possible solutions come to mind: - TRIM(tablename) like '%\_L' ESCAPE '\'; - when you'...
85115 Dec 2010 @ 01:34 PSTDatabaseReplyHow to dynamically pass values to SQL ?If you're supposed not to write an SP, you should tell your boss/dba, that this is foolish. Of course you can write some BTEQ scripts to create and run the neccessary SQL, too, but this is more ...
85008 Dec 2010 @ 09:05 PSTDatabaseReplyImplicit conversion CHAR to INT failingIn fact it's used correctly :-) You get an automatic typecast (= Teradata style typecast), which uses the format of the column, which is right aligned within 11 characters, i.e. leading blanks. ...
84901 Dec 2010 @ 02:17 PSTDatabaseReplyExecute Macro from Stored Procedure???Hi JK, the DECLARE CURSOR(MACRO) is only for Embedded SQL, but not SPs. You can call an SP from a macro, but not vice versa. Dieter
84818 Nov 2010 @ 01:04 PSTDatabaseReplyerror: bad argument for sqrt functionIf sqrt(abs(...)) work then you definitely got negative values. Remove the sqrt and check for it. FLOAT is a 8 byte IEEE floating point format with approximately 15/16 digits precision, whereas...
84717 Nov 2010 @ 10:28 PSTDatabaseReplyerror: bad argument for sqrt functionHi Carlos, the query fails even if you use BIGINT instead of BYTEINT :-) Remove the SQRT and see the negative value. This looks like a part of a standard deviation calculation, in that case th...
84617 Nov 2010 @ 07:02 PSTDatabaseReplyStatistics on Primary IndexI use this is for missing stats on any PI/SI, you could add filter for PIs only. I'm shure it can be further simplified, as it was created based on a query solving a different problem :-) /**...
84517 Nov 2010 @ 06:49 PSTDatabaseReplyerror: bad argument for sqrt functionStrange. I would suggest to run that query from BTEQ or any other query tool, maybe it's because of a SQLA/ODBC/.NET "feature". To eliminate issues with SQRT you could replace it with (a-b)**...
84417 Nov 2010 @ 05:41 PSTDatabaseReplyerror: bad argument for sqrt functionIs this the error? 2604 Bad argument involving ... for SQRT function. First try adding a condition to see if actually a >= b: WHERE a >= b Do you run that query in SQL Assistant pre-TD13?...
84314 Nov 2010 @ 01:18 PSTDatabaseReplyLarge History Table Design using PPIWhy separate tables? As long as the optimizer is able to eliminate partitions based on the WHERE-condition, there is no reason to create seperate tables. And if the typical date range queried i...
84214 Nov 2010 @ 01:07 PSTDatabaseReplyIs it possible to convert to a date interval string literal to be used within a select?You were quite close: SELECT DATE '2004-12-15' + ((DATE'2005-02-01'- DATE'2004-01-01') MONTH); You can't construct an interval the way you did, you have to cast: SELECT DATE '2004-12-15' + C...
84109 Nov 2010 @ 12:47 PSTDatabaseReplySolving a problem without using an OLAP functionHi Kevin, it's still confusing. You want all rows where test_time is not null plus the row where there's only a NULL for a (person_name,test_date) combination, is that correct? You could wri...
84008 Nov 2010 @ 09:31 PSTGeneralReplyTeradata variable/view/macroYou mean the Where-condition "WHERE col1 = '123'" is stored as a string in col3? This needs Dynamic SQL which is only possible within a Stored Procedure. How does col4 actually look like? Is ...
83908 Nov 2010 @ 08:17 PSTDatabaseReplyCase When Statement in TeradataYour join returns two rows, because two rows match. A CASE statement in the SELECT list will never filter any rows. Logically there's the JOIN* first, followed by WHERE*, GROUP BY, HAVING*, OLA...
83808 Nov 2010 @ 07:00 PSTDatabaseReplyCase When Statement in TeradataHI Janaki, it's not the CASE, it's the JOIN which creates too many rows. If you remove the CASE the number of rows shouldn't change, you probably need a ROW_NUMBER to implement that "best fit". ...
83708 Nov 2010 @ 05:42 PSTDatabaseReplyCase When Statement in TeradataHi Janaki, this is confusing me, you write "In my select clause,i have a case statement " but then "I am getting more than one row". CASE returns a scalar value, it will never change the number...
83605 Nov 2010 @ 07:12 PDTDatabaseReplyDynamic Date Calculation in SQL QueryHi Carlos, try that query two months later :-) Dieter
83504 Nov 2010 @ 10:46 PDTDatabaseReplyDynamic Date Calculation in SQL QueryYou have to play around with ADD_MONTHS and EXTRACT. d - (EXTRACT(DAY FROM d) - 1) returns the first day of the current month and ADD_MONTHS adds or substracts some months: SELECT CURRENT_DAT...
83401 Nov 2010 @ 09:05 PDTDatabaseReplyNumeric overflow....2616Hi Rob, the keyword is probably "backward compatibility". Most customers don't like it, if any defaults change:-( Dieter
83301 Nov 2010 @ 07:26 PDTUDAReplyalter table modify primary index to unique primary indexThere's no reason to copy to a new table. Simply add a USI on the NUPI columns and then ALTER TABLE xxx MODIFY UNIQUE PRIMARY INDEX (NUPI_cols) This also drops the USI automatically. Dieter
83201 Nov 2010 @ 07:14 PDTDatabaseReplyNumeric overflow....2616The manuals clearly state the resulting datatype for a COUNT. ANSI mode: DECIMAL(15,0) or DECIMAL(38,0), depending on the MaxDecimal setting in dbscontrol. Teradata mode: INTEGER Dieter
83118 Oct 2010 @ 05:36 PDTDatabaseReplyWhy do we need integer datatypesHi Manu, the maximum to be stored for 2 byte integer is 127, so in theory this could be stored in a dec(2,0), but 127 are 3 digits and and dec(2,0) only defines 2 digits, so 127 simply doesn't fit...
83018 Oct 2010 @ 12:45 PDTGeneralReplyDifferent results in bteq and sql assitant for current_date with format optionHi Sakthi, there's no setting in SQL Assistant to achieve the same result. FORMAT was used for BTEQ reports, which uses CLI and requests the result as a formatted string If you want to get the...
82918 Oct 2010 @ 12:31 PDTTeradata ApplicationsReplyQuestioning the results of a Window Remaining FunctionIs this actually the query you're running? This should result in a syntax error, because ORDER BY must be after PARTITON BY? And if you submit that "remaining window" without final ORDER BY t...

Pages