#DateForumTypeThreadPost
265327 Feb 2014 @ 07:43 PSTDatabaseReplyno more spool space in recursive queryUsing recursion for this task will result in a spool file with an approximate size of (COUNT(*) + 1) * SUM(CHAR_LENGTH(state)+1) / 2 for each cust_id Is there a known maximum number of states per...
265227 Feb 2014 @ 07:33 PSTDatabaseReplyGetting a dynamic SampleYou can assign a percentage based on a PERCENT_RANK * 100. If it needs to be statistically random you must use a Derived Table to create a RANDOM(1,2000000000) number to ORDER BY it ...
265127 Feb 2014 @ 07:06 PSTDatabaseReplyQuery regarding Column and row mergeWow, that's a sick data model, hopefully you need this query to fix this mess. SELECT t1.Cnbr ,CASE WHEN t2.Att1 = 'Income' THEN t1.Att1 WHEN t2.Att2 = 'Incom...
265027 Feb 2014 @ 01:20 PSTDatabaseReplyGetting a dynamic SampleHi San, some questions: Are the shared departments known in advance or should the query find them? Are there multiple combinations? Can there be more than two shared departments? Is the alloca...
264926 Feb 2014 @ 11:57 PSTDatabaseReplyChange One Row Data To Multiple Rows.What's your TD release? In TD14 there's a STRTOK_SPLIT_TO_TABLE function: SELECT * FROM TABLE (STRTOK_SPLIT_TO_TABLE(myTable.name,myTable.col1,'#') RETURNS (outkey VARC...
264826 Feb 2014 @ 11:39 PSTDatabaseReplyTeradata OLAP Functions QUALIFY ROW_NUMBER() OVER (PARTITION BY GLAccount ORDER BY COUNT DESC, Product) = 1 Assuming that COUNT is the result of a aggregation you can simply replace COUNT with the ac...
264725 Feb 2014 @ 11:49 PSTToolsReplyfast export and mload in fastload format issue - error code 2793Hi Cheeli, the default for FastExport is INDICATOR mode, you need to modify your LAYOUT: .LAYOUT DATA_LAYOUT INDICATORS;  Or let FastExport create the MLoad: .EXPORT .... MLSCRIPT xxx....
264625 Feb 2014 @ 11:01 PSTToolsReplyfast export and mload in fastload format issue - error code 2793Hi Cheeli, 2793 is the result of the MARK DUPLICATE INSERT option, i.e. all rows already exist in the target table.  
264525 Feb 2014 @ 10:06 PSTGeneralReplyMigrate Oracle UDF to Teradata UDFWhat kind of UDF? SQL or C/Java?
264425 Feb 2014 @ 10:05 PSTDatabaseReplyInserting multiple values with single quotes in single fieldHi Sundeep, it's not specifically Teradata, every DBMS works like that, to get a single quote within a string you need to write two single quotes.
264325 Feb 2014 @ 09:58 PSTDatabaseReplyUpdate SQL query is taking long time to executeDid you check DBQL for elapsed time and CPU/IO usage? If it's actually 100 rows this should run much faster (even if it's changing the PI and partitioning). Are there any Secondary Indexe...
264225 Feb 2014 @ 09:41 PSTDatabaseReplyUnicode Substr issue with special charactersHi Irfan, hopefully you want this SQL to split the data in two columns, a VARCHAR is definitely plain wrong. CASE WHEN POSITION(' ' IN TRIM(price)) > 0 THEN SUBSTRING(TRIM(price) FRO...
264121 Feb 2014 @ 12:27 PSTDatabaseReplyMultiple values using case statement param_file must be a single line readable text file. Param values are separated by blanks where strings must be enclosed in single quotes, e.g.   .ACCEPT numparam, charpara from par...
264021 Feb 2014 @ 08:09 PSTDatabaseReplyDate as IntegerCAST(datecol AS INT) returns TD's internal DATE format. You want something different: extract(year from datecol) * 10000 + extract(month from datecol) * 100 + extract(day from daycol) ...
263920 Feb 2014 @ 11:54 PSTDatabaseReplyDate as IntegerYou're trying to update every row in Test with all 73414 in sys_calendar.  There should be a DATE column in a calendar table: update DB.Test set Date_Key = cast(calendar_date as int);...
263820 Feb 2014 @ 11:39 PSTDatabaseReplyMultiple values using case statementOK, I think I know what you want: If there are any rows matching :ratescale only return those, otherwise return all rows. SEL colA,colB FROM table1 WHERE colA = :ratescale OR NOT EXISTS ...
263720 Feb 2014 @ 11:11 PSTDatabaseReplyTable StatsYou don't want/need to collect stats on each and every column because they must be re-collected from time to time (which is resource-intensive).  That's why you need to know/decide wh...
263620 Feb 2014 @ 02:53 PSTDatabaseReplyKeep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsertA sequence GENERATED ALWAYS AS IDENTITY with NO CYCLE should never return a duplicate value (based on that you don't need to define a PK on settlement_id). What's your client and...
263520 Feb 2014 @ 02:45 PSTDatabaseReplyTable StatsHELP STATS test COLUMN (test1) shows the actual data.
263420 Feb 2014 @ 02:43 PSTDatabaseReplyOrder by DateHi Reddy, ORDER BY within a view is not allowed. There's a workaround, but it's definitely not recommended: Add TOP.   REPLACE VIEW xxx AS SELECT TOP 100 PERCENT * FROM tab ORDER...
263319 Feb 2014 @ 11:08 PSTToolsReplyfastload on linux This is probably related to AIX using big-endian while your Linux box is little-endian. I don't know if there's any way to load "wrong" endian data.
263218 Feb 2014 @ 10:00 PSTDatabaseReplySQL for Year and MonthHi Natasha, in a calendar table there's a date column, too. You might use it instead and simply apply a FORMAT:  cast(cast(calendar_date as format 'mmm''yy') as char(7...
263118 Feb 2014 @ 06:03 PSTToolsReplyHow to Run Fast ExportYou should always specify which error you get. FastExport commands must start with a period. Both Fexp and SQL commands must be terminated by a semicolon: .LOGTABLE DB.errors; .LOGON jugalDB/J...
263018 Feb 2014 @ 06:00 PSTDatabaseReplyInsert in to date dimension is not workingHi Reddy, of course the Insert worked, it added 7 rows for monday to sunday where all other columns are NULL (the Select returned 73414 rows, but your target table is SET so duplicate rows where r...
262917 Feb 2014 @ 11:05 PSTDatabaseReplyWant to check if two specific columns exist in more than one table select DatabaseName, TableName from dbc.ColumnsV where ColumName in ('party_no', 'appl_id', 'co_no') group by 1,2 having count(*) = 3  

Pages