#DateForumTypeThreadPost
362827 Mar 2015 @ 03:18 PDTGeneralReplyDatatype of a column from dbc tablesThis is a UDF I wrote some tme ago: /********** Returns the datatype of a column as a literal 20111012 initial version - Dieter Noeth 20131010 added TD14.10 ARRAY datatypes - dn 20140812 a...
362727 Mar 2015 @ 12:11 PDTGeneralReplyIs there a way to avoid NOT IN function Hi Navy, you should always try to avoid NOT IN unless both columns (from the inner and outer table) are defined as NOT NULL (that's similar for all DBMSes), because otherwise you have to ...
362625 Mar 2015 @ 03:38 PDTDatabaseReplyPerformance with pertitioned tableHi Moutusi, Q1: Like this? extract(year from STRT_DT) = 2015? No, I don't think that partition elimination will work for this case.   Q2: No, definitely not. It's exactly the ...
362525 Mar 2015 @ 03:30 PDTDatabaseReplyTransposing Member Count with Date RangeHi Bob, your method 1 is the common solution for this kind of problem, you just have to translate it to SQL :-) It's mostly cut&paste&modify (and 100% Standard SQL, no TD syntax neede...
362425 Mar 2015 @ 03:16 PDTDatabaseReplyHow to find if a varchar field has 9 digit intergersYou don't need a UDF, TD14 supports regular expressions. Do you want only those rows where the field has exactly 9 digits? where REGEXP_SIMILAR(x, '^[0-9]{9}$') = 1 Or extract th...
362325 Mar 2015 @ 09:33 PDTDatabaseReplyTranspose data from wide table to long tableThere's no simplified way besides Ulrich's CASE, blame that stupid data model :) Why do you think that 120 columns are too much?  
362225 Mar 2015 @ 04:07 PDTDatabaseReplyQuestion on Randomized allocationHi Valeria, RANDOMIZED ALLOCATION is an option of the SAMPLE clause, but sampling only works on all rows. Plus it's random, not repeatable, so you can't do two Inserts and still get distin...
362124 Mar 2015 @ 08:58 PDTDatabaseReplyAGE in MONTHS & YEARSOf course the combination of (year, month, day) is not a fixed number of days, so you must define some rules, e.g. is between 2012-02-29 and 2013-02-28 a full year?   MONTHS_BETWEEN uses thi...
362024 Mar 2015 @ 08:47 PDTDatabaseReplyUnwanted databases gets assigned whenever a new user is created.Hi Guru, this flag has been switched on using GRANT ... ON ALL db TO userx, you can switch it off using either REVOKE ... ON db FROM userx (only switch of inheritance, but don't remove the inh...
361924 Mar 2015 @ 02:21 PDTDatabaseReplyUnwanted databases gets assigned whenever a new user is created.Hi Guru, a new user gets access based on the public role: select * from dbc.allrightsv where username = 'PUBLIC' or due to inherited rights of an owner of the new user: select * fr...
361824 Mar 2015 @ 12:14 PDTDatabaseReplyIs there a way to make column aliases or titles longer? - ideally 256 characters.Hi Tim, hopefully you never have to key in your column names :-)  Seems you run on a version before TD14.10 which only supports only 30 characters for column names and 60 for titles. TD14.1...
361723 Mar 2015 @ 02:54 PDTDatabaseReplyUsing Case statement instead of UnionCollect Stats will not help for UNION (or CASE). Do you really need a UNION instead of UNION ALL? This will add a DISTINCT step with a lot of overhead, distribution & sort. And you must add ...
361623 Mar 2015 @ 12:24 PDTDatabaseReplyTranspose from columns to rowsOf course not, '' is less than 'F' :)
361523 Mar 2015 @ 05:31 PDTDatabaseReplyBug in exponentiate ** operator precedence and associativity ?The manuals clearly specify the left-to-right order, so at least it's documented behaviour :-) I tend to use brackets, so there's no gueass about order...   Btw, your expected resul...
361423 Mar 2015 @ 04:29 PDTDatabaseReplyUsing Case statement instead of UnionWhy don't you want utilize UNIONs? For CASE you need to cross join to a table containing one row per column, e.g. n = 1,2,3,4 select case when helper.n = 1 then C1 when ...
361323 Mar 2015 @ 04:25 PDTDatabaseReplyExecute a set of DDL's as a single scriptYou're running CREATEs without prior testing? Why don't you check the DDLs in a development database?
361223 Mar 2015 @ 04:22 PDTUDAReplyUsing qualify and group by togetherYour "tool" should be able to create the needed SQL
361123 Mar 2015 @ 04:21 PDTDatabaseReplyNon duplicate row selection queryYou need to install a hash function, hash_sha512 doesn't exist on your system.
361022 Mar 2015 @ 01:20 PDTDatabaseReplyBest criteria for selecting NUPIHi Samir, first check the FK columns (used for joins), then the columns used in Where, but defining a useless PI is the last resort.  Every relational table should have a logical Primary Key...
360922 Mar 2015 @ 02:57 PDTToolsReplyHow to make use of ARCMAIN utility to take DB backup.You can't use more than 8 characters for FILE or add a path within the ARC script: archive data tables (financial.custonline), release lock, file=ARCHIVE; Then you use a runtime parameter...
360822 Mar 2015 @ 02:47 PDTDatabaseReplyHow to Skip first COLOUMN from a flatfile and load remaining columns using mload?Hi Amarnath, simply define it as a FILLER instead of a FIELD in the LAYOUT.
360722 Mar 2015 @ 02:35 PDTDatabaseReplyDBC.Objectusage table is empty but ObjectUseCountCollectRate = 10 ON ALL will work, but do you really need it for each and every table in all databases and users? E.g. staging tables are usually fully deleted and reloaded.
360622 Mar 2015 @ 02:33 PDTDatabaseReplyNon duplicate row selection queryYuva's approach utllizing a hash function like SHA (but don't use HASHROW) should work. You don't have to add it as a new column, simply calculate it on the fly concatenating all colum...
360522 Mar 2015 @ 02:09 PDTDatabaseReplyRemoving a line break character in a columnHi Mark, oReplace does not automatically replace line breaks, check with CHAR2HEXINT.      But as you must remove both CR and LB you better use oTranslate(x, '0A0D'x...
360422 Mar 2015 @ 01:52 PDTDatabaseReplyHow SYSDATE will work if load is going more than one dayHi Moutusi, assuming SYSDATE indicates you're using MultiLoad or TPump: SYSDATE & SYSTIME will be the same for all rows loaded, but this is clearly mentioned in the manuals, e.g. &...

Pages