#DateForumTypeThreadPost
232810 Oct 2013 @ 11:18 PDTDatabaseReplyHow to generate 32 characters unique numberHi Sagar, you need to write a UDF. Probably the easiest way is to search for an open source C-implementation of a UUID version 4 and wrap this in a Teradata C-UDF.   Dieter
232709 Oct 2013 @ 12:54 PDTDatabaseReplyHow to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???Hi Sayakm, Teradata's FORMAT doesn't support single digit days, but you can use this: CASE WHEN ts LIKE '_____,%' THEN SUBSTRING(ts FROM 1 FOR 4) || '0' || SUBST...
232609 Oct 2013 @ 08:27 PDTDatabaseReplyTeradata 14 and REGEXP_INSTRHi Piotr, you can use only two or all six params.   Dieter
232508 Oct 2013 @ 11:32 PDTDatabaseReplySELECT INTO SQL inside stored procedureHi David, it's valid Teradata SQL :-) This is based on some really old syntax (before Standard SQL), when you correctly start with the first keyword (SELECT/DELETE/etc.) you can write in non-...
232408 Oct 2013 @ 11:28 PDTDatabaseReplyHOw do you ALTER table add partitions on a multilevel partitioned table?If you want to add on the top level it's a simple ADD RANGE. if it's a sub-partition there's ADD RANGE#Lx. Check the SQL DDL manual for details.   Dieter
232308 Oct 2013 @ 11:02 PDTDatabaseReplyCasting to An integer giving incorrect result.Hi Rakesh, works fine for me, i'ts probably a client problem. Which client and which connection is used? ODBC?   Dieter
232207 Oct 2013 @ 01:52 PDTToolsReplyConvert date format m/d/yyyy to mm/dd/yyyy in TPTCheck if your release of TPT supports VARDATE, defining your column as col VARDATE(10) FORMATIN ('mm/dd/yyyy') FORMATOUT ('mm/dd/yyyy') should allow single digit month/day. &...
232106 Oct 2013 @ 10:48 PDTDatabaseReplyStrong and Weak Join MatchesTry this: SELECT a.*, COALESCE(B1.Policy, B2.Policy, B3.Policy) AS Policy2, COALESCE(B1.Claim, B2.Claim, B3.Claim) AS Claim2 FROM tableA AS A LEFT JOIN tableB AS B1 ON A.Policy = B1...
232006 Oct 2013 @ 07:01 PDTDatabaseReplyPrimary index + Partition Key + single AMP operationHi Piotr, no, no, no, no, don't do that! The number of rows per PI-value should be as low as possible, as a rule of thumb they should fit on a datablock, i.e. up to a three or four digit numb...
231906 Oct 2013 @ 04:27 PDTDatabaseReplyHow to spell out numeric value in English words by select query.When you go to my profile page you'll see my email. Dieter
231805 Oct 2013 @ 04:35 PDTDatabaseReplyWhat are Resusage table logging rulesThere's no view for it, you must use a command line utility, e.g. xdbw -> get resource/logtable/sumlogtable ctl -> screen rss   If you only want to know the collection/logging ra...
231705 Oct 2013 @ 03:59 PDTDatabaseReplyStrong and Weak Join MatchesYou have to be more precise on your rules. Why is there only one row for policy 126?  I would assume two row because there are two rows for 126 in table B. Why is 126 | null | 126 | ab...
231603 Oct 2013 @ 01:19 PDTDatabaseReplyCASE statement in SQLIf two columns with different datatypes are compared there will be an automatic typecast to get comparable datatypes. When a string and a numeric colum are compared the string will be converted to...
231503 Oct 2013 @ 12:56 PDTToolsReplyConverting Binary extracted file using TPT to ASCII?TPT DataConnector 14.10 and 14.00.00.08 supports delimited output without casting all columns to Varchars. This will not add much overhead on server side as the conversion in done on the client. ...
231403 Oct 2013 @ 10:17 PDTDatabaseReplySpecial Characters in TeradataHi Thomas, your not using the built-in TD14 oTranslate function :-) It must be a custom implementation which exactly mimics Oracle's empty Varchar equals NULL "feature". The Oracle...
231303 Oct 2013 @ 10:10 PDTToolsReplywaht is Spoolmode option in TPT script?Check the SPOOLMODE option in the FastExport manual. In a nutshell: NOSPOOL will avoid creating a huge spool, but locks will be released later.   Dieter
231203 Oct 2013 @ 10:07 PDTGeneralReplyOracle to Teradata SQL equivalents?In Teradata you can re-use an alias instead of cut&paste to further simplify to  select CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND) AS Err...
231103 Oct 2013 @ 10:04 PDTDatabaseReply3556 : Database does not have a permanent journalHi Mahesh, well, the reason is that  jedi_cly_db does not have a permanent journal :-) You must specify another database with an already existing PJ. Or you add a PJ to jedi_...
231002 Oct 2013 @ 03:50 PDTDatabaseReplyproblem joining on imported unix columnIf it exists in both tables following SQL should return exactly one row:   select * from (select val from tablea where val= '207b90761400a5f144074567fbac3b07') as a full join ...
230902 Oct 2013 @ 03:35 PDTDatabaseReplyproblem joining on imported unix columnHi Derek, so you're searching for '207b90761400a5f144074567fbac3b07'? And this exists in both tables? For 100% shure? In the beginning i thought there might have been '20'xb ...
230802 Oct 2013 @ 02:57 PDTDatabaseReplyproblem joining on imported unix column"does work" is almost as good as "doesn't work" :-) What is returned by that query? One row or two rows? And for UNION ALL? Can you show the actual output? Btw, you never...
230702 Oct 2013 @ 12:01 PDTDatabaseReplyproblem joining on imported unix columnStrange, what is returned when you UNION both values? SELECT value, char2hexint(value) FROM tableA a WHERE value='abc123' UNION SELECT value, char2hexint(value) FROM tableB b WHERE va...
230602 Oct 2013 @ 09:23 PDTDatabaseReplySpecial Characters in TeradataoTRANSLATE is case sensitive, you need to either add chars 'A' to 'Z' or use  OTRANSLATE(UPPER(your_column), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')   Dieter
230502 Oct 2013 @ 03:32 PDTDatabaseReplyORPHAN ACCESS RIGHTS - HOW TO DELETE ?Hi Pierre, yes, that's the "official" query to delete orphan access rights.  --DELETE SELECT count(*) FROM DBC.AccessRights WHERE TVMID <> ‘000001000000’...
230402 Oct 2013 @ 01:06 PDTGeneralReplyOracle to Teradata SQL equivalents?It's just a minor mistake: Within the FORMAT you can't use ' ' for blanks you must use 'B' -> FORMAT 'YYYY-MM-DDbHH:MI:SS' But if your error_time includes a time...

Pages