3628 | 27 Mar 2015 @ 03:18 PDT | General | Reply | Datatype of a column from dbc tables | This 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... |
3627 | 27 Mar 2015 @ 12:11 PDT | General | Reply | Is 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 ... |
3626 | 25 Mar 2015 @ 03:38 PDT | Database | Reply | Performance with pertitioned table | Hi 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 ... |
3625 | 25 Mar 2015 @ 03:30 PDT | Database | Reply | Transposing Member Count with Date Range | Hi 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... |
3624 | 25 Mar 2015 @ 03:16 PDT | Database | Reply | How to find if a varchar field has 9 digit intergers | You 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... |
3623 | 25 Mar 2015 @ 09:33 PDT | Database | Reply | Transpose data from wide table to long table | There's no simplified way besides Ulrich's CASE, blame that stupid data model :)
Why do you think that 120 columns are too much?
|
3622 | 25 Mar 2015 @ 04:07 PDT | Database | Reply | Question on Randomized allocation | Hi 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... |
3621 | 24 Mar 2015 @ 08:58 PDT | Database | Reply | AGE in MONTHS & YEARS | Of 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... |
3620 | 24 Mar 2015 @ 08:47 PDT | Database | Reply | Unwanted 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... |
3619 | 24 Mar 2015 @ 02:21 PDT | Database | Reply | Unwanted 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... |
3618 | 24 Mar 2015 @ 12:14 PDT | Database | Reply | Is 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... |
3617 | 23 Mar 2015 @ 02:54 PDT | Database | Reply | Using Case statement instead of Union | Collect 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 ... |
3616 | 23 Mar 2015 @ 12:24 PDT | Database | Reply | Transpose from columns to rows | Of course not, '' is less than 'F' :)
|
3615 | 23 Mar 2015 @ 05:31 PDT | Database | Reply | Bug 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... |
3614 | 23 Mar 2015 @ 04:29 PDT | Database | Reply | Using Case statement instead of Union | Why 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 ... |
3613 | 23 Mar 2015 @ 04:25 PDT | Database | Reply | Execute a set of DDL's as a single script | You're running CREATEs without prior testing?
Why don't you check the DDLs in a development database?
|
3612 | 23 Mar 2015 @ 04:22 PDT | UDA | Reply | Using qualify and group by together | Your "tool" should be able to create the needed SQL
|
3611 | 23 Mar 2015 @ 04:21 PDT | Database | Reply | Non duplicate row selection query | You need to install a hash function, hash_sha512 doesn't exist on your system.
|
3610 | 22 Mar 2015 @ 01:20 PDT | Database | Reply | Best criteria for selecting NUPI | Hi 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... |
3609 | 22 Mar 2015 @ 02:57 PDT | Tools | Reply | How 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... |
3608 | 22 Mar 2015 @ 02:47 PDT | Database | Reply | How 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.
|
3607 | 22 Mar 2015 @ 02:35 PDT | Database | Reply | DBC.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.
|
3606 | 22 Mar 2015 @ 02:33 PDT | Database | Reply | Non duplicate row selection query | Yuva'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... |
3605 | 22 Mar 2015 @ 02:09 PDT | Database | Reply | Removing a line break character in a column | Hi 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... |
3604 | 22 Mar 2015 @ 01:52 PDT | Database | Reply | How SYSDATE will work if load is going more than one day | Hi 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.
&... |