#DateForumTypeThreadPost
345324 Jan 2015 @ 04:01 PSTGeneralReplyStored Procedure in TeradataCaution: The SP manual mixes syntax for Embedded SQL and SPs, you should skip over the Embedded SQL parts.   You don't need Dynamic SQL to pass parameters to this cursor, only for dynami...
345223 Jan 2015 @ 01:12 PSTGeneralReplyStored Procedure in TeradataThe cursor select is not dynamic, you need to remove the quotes: ; DECLARE CURS_STTS CURSOR FOR SELECT 'COLLECT STATISTICS USING NO S...
345123 Jan 2015 @ 12:51 PSTDatabaseReplyNeed help in tuning a QueryA quick solution is to change the UNION to UNION all, this avoids the DISTINCT processing. In best case the table is partitioned by  I.SETL_DT. You might also try to combine all three s...
345023 Jan 2015 @ 12:43 PSTDatabaseReplyFinding the nearest greater date lookup through SQLHi Maria, there are multiple ways to get your result.   If the number of rows per value is low and emp_id is the PI in both tables this old-style solution using a non-equi join plus aggrega...
344923 Jan 2015 @ 12:29 PSTDatabaseReplyConvert time '0300' in varchar format to Timestamp by appending current_date to timeHi Irf, select cast('0300' as timestamp format 'hhmi')  
344821 Jan 2015 @ 04:12 PSTGeneralReplyQuery about Materialisation of queries based on viewsHi Sanket, CREATE TABLE tab AS (your query) WITH DATA PRIMARY INDEX (...)
344721 Jan 2015 @ 04:10 PSTUDAReplyCAST of CHAR to DECIMALHi Pio, the error message indicates that at least one a.CD_STO doesn't fit in a decimal(5,4), so simply increase the number of digits.
344621 Jan 2015 @ 04:04 PSTDatabaseReplyReplace View ( DDL ) inside a Stored Procedure TeradataHi Karthick, DDL and DML can't be submitted within the same transaction. MACROs are multi statements, i.e. one transaction, so there's no way to do what you want in a macro. Of course yo...
344521 Jan 2015 @ 03:59 PSTDatabaseReplySeeking ideas: need to grab next available UPI for insertsCreating a list of all gaps is easy using OLAP-functions: SELECT id - GapSize AS GapStart ,id - 1 AS GapEnd ,COALESCE(id - MIN(id) OVER ...
344420 Jan 2015 @ 01:40 PSTDatabaseReplyCreate view referencing recursive view, trying to concatenate fieldHi Simon, as you noticed you can't use a recursive view in another view. But depending on your TD release there's are built-in group concat function: XMLAGG SELECT TRIM(TRAILING &#...
344320 Jan 2015 @ 12:50 PSTDatabaseReplySeeking ideas: need to grab next available UPI for insertsAre you going to insert single or multiple rows (INS VALUES or INS/SEL)?
344220 Jan 2015 @ 07:49 PSTTeradata StudioReplyDifferent results with same query using Teradata Studio and Teradata SQL Assistant Hi Sibel, this is based on different defaults in your sessions, ANSI vs. Teradata session: ANSI defaults to case-sensitive string comparisons while Teradata defaults to not case-sensitive (there ...
344117 Jan 2015 @ 06:20 PSTConnectivityReplyRegarding installation of teradata tool in a personnel computerHi Shayne, your system is running as expected. When you tried to start it it was telling it's already running and what to do to force a restart. And The system is quiescent simply means...
344016 Jan 2015 @ 01:43 PSTDatabaseReplydata timestamp issuesHi Dieter, it's the same problem, DATE '2015-01-16' is a date literal, like TIME '12:34:56'. So it's either  transactiontime as of :enddate -- enddate is a DATE o...
343915 Jan 2015 @ 11:25 PSTDatabaseReplyHow to flag products based on start ts and end tsHi Markus, if it's just for flagging the gaps you can simplify it by treating the first row as correct: , CASE WHEN MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BET...
343815 Jan 2015 @ 11:20 PSTDatabaseReply 'CALL Failed. HY000:'You expect reading unformatted code which is mainly commented out? Of course you get an error, you throw it in your code using SIGNAL.
343715 Jan 2015 @ 11:06 PSTDatabaseReplywhile compiling a stored procedure getting error column 'xxx' is not nulll, give column a valueThere was propably a default assigned to that column in Oracle, so simply do the same: INSTIME TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) If you can't alter the table you must add I...
343615 Jan 2015 @ 11:00 PSTDatabaseReplySpool space issueA join condition like yours based on non-equality will always result in a cross join, as preparation the smaller table will be "duplicated on all AMPs", followed by a product join cü...
343515 Jan 2015 @ 09:37 PSTDatabaseReplyCREATE set table xxxxx.x2 AS MULTISET TABLE xxxx.x1 WITH DATA AND STAT - NO stats copied.Btw, this behaviour is fully documented in the manuals: If all columns in a MULTISET source table are non-unique, and if the target table is a SET table, then the system does not copy statistic...
343415 Jan 2015 @ 07:59 PSTConnectivityReplyRegarding installation of teradata tool in a personnel computerHi Shayne, what's the exact error message?
343315 Jan 2015 @ 07:46 PSTDatabaseReplydata timestamp issuesHi Dieter, in your working example you explicitly cast the parameter to a date, of course this works. But "DATE :enddate" is no valid syntax, only literal dates can be used (e.g. DATE &...
343215 Jan 2015 @ 02:21 PSTThird Party SoftwareReplysql server to teradata datatype conversionHi Puneet, geography: probably ST_GEOMETRY (before TD15 only 2 dimensional data) http://www.info.teradata.com/templates/eSrchResults.cfm?prodline=&txtpid=&txtrelno=&txtttlkywrd=GEOTYP...
343115 Jan 2015 @ 12:28 PSTDatabaseReplyHow to flag products based on start ts and end tsHi Rohan, Markus has probably a kind of slowly changing dimension and is trying to flag gaps. So when the start_ts is exactly one day after the previous end_ts it's 'Y' else 'N...
343014 Jan 2015 @ 09:22 PSTDatabaseReplyDECODE uneven behaviorHi KKReddy, I never used DECODE (in Teradata), but it's probably casespecific to mimic Oracle's result.
342914 Jan 2015 @ 09:19 PSTDatabaseReplyLowest Child in recusive queryNice variation of a path through a hierarchy :-) Following CTE (based on Rohan's DDL) traverses bottom-up, thus resulting in inverted level counts and paths. Both are then fixed in the final s...

Pages