3603 | 22 Mar 2015 @ 01:43 PDT | General | Reply | Diffrent values displayed for Timestamp (6) column on diffrent queries | The first timestamp will not be generated by Teradata (only two fractional digits).
Is this always shifting the fractional seconds one digit?
What client do you use?
Before you open an i... |
3602 | 22 Mar 2015 @ 01:32 PDT | UDA | Reply | Using qualify and group by together | Well, this is the same as accessing a view with different Selects and everyone is doing that all the time.
The optimizer will do his best to eliminate unused columns.
|
3601 | 22 Mar 2015 @ 01:25 PDT | Database | Reply | Transpose from columns to rows | You need nested aggregrations, e.g.
select
count(case when typestring = 'FE' then 1 end) as count_FTET
,count(case when typestring = 'F' then 1 end) as count_FT
,count... |
3600 | 17 Mar 2015 @ 03:35 PDT | General | Reply | Month end data from a daily table | This might be what you want, if there are no overlapping ranges:
SELECT ...
CASE
-- already last day of month
WHEN Validity_end_date = LAST_DAY(Validity_end_date) THEN Validity_e... |
3599 | 17 Mar 2015 @ 03:04 PDT | General | Reply | NEED HELP !! - Merging Consecutive and OVERLAPPING date spans | Hard to tell with two example rows :)
SELECT
id, count, StartDate,
COALESCE(MIN(StartDate)
OVER (PARTITION BY id
ORDER BY StartDate, EndDate
... |
3598 | 17 Mar 2015 @ 02:54 PDT | Database | Reply | REGEXP_SIMILAR Syntax | This should work as-is.
Check what's returned by
select * from dbc.FunctionsV where FunctionName = 'REGEXP_SIMILAR'
Should be two functions in td_sysfnlib with 2 and 3 parame... |
3597 | 17 Mar 2015 @ 02:51 PDT | Database | Reply | On Commit Preserve Rows taking much time | It's always the same reason:
The table is created as a SET table plus a bad Primary Index (usually no PI defined, so defaulting to first column)
|
3596 | 17 Mar 2015 @ 02:49 PDT | General | Reply | Reverse engineer rules from dbqlruletbl rows | Hi Abhishek,
I wrote this to get the DBQL commands from DBQLRuleTbl in TD14.10/15, will need some modification for earlier releases:
SELECT
'BEGIN QUERY LOGGING'
|| CASE WHEN Wi... |
3595 | 17 Mar 2015 @ 10:56 PDT | Database | Reply | Non duplicate row selection query | There's no way to avoid the GROUP/PARTITION BY all columns.
Clean your data once and next time simply don't insert duplicates, your load process should take care of this.
There sh... |
3594 | 17 Mar 2015 @ 10:51 PDT | Database | Reply | Using Dynamic databasename and tablename in stored procedure | Move the Handler to a nested BEGIN/END:
REPLACE PROCEDURE MYSP
(
IN SRC_DB_NM VARCHAR(30)
, IN SRC_TBL_NM VARCHAR(30)
, OUT MESSAGE VARCHAR(200)
)
BEGIN
DECLARE QUERY ... |
3593 | 17 Mar 2015 @ 10:32 PDT | Database | Reply | Teradata decimal calculation behaviour | This is documened behaviour, Teradata rounds after every step when DECIMALs are involved.
So the basic rule is "first multiply, then divide".
Or you cast the first operand to a ... |
3592 | 16 Mar 2015 @ 04:01 PDT | Database | Reply | Query Performance Issue | Hi Asish,
did you check DBQL for estimated vs. actual number of rows?
The optimizer's choice picking the index on transaction_country is bad.
There's a bad condition, comparing str... |
3591 | 16 Mar 2015 @ 03:23 PDT | Database | Reply | Query | FIRST_VALUE cam be replaced by nested OLAP-functions:
SELECT name, sum,
MIN(min_sum) OVER () - sum
FROM
(
SELECT name, sum,
CASE WHEN ROW_NUMBER() OVER (ORDER BY name) = 1 THEN... |
3590 | 16 Mar 2015 @ 03:12 PDT | Database | Reply | Ordered Analytical Function/Iterative Loop | What's your TD release?
In TD14 you could use FIRST_VALUE/LAST_VALUE:
SELECT party_id, fisc_week_val, lifecycle_stage,
COALESCE(LAST_VALUE(lifecycle_stage IGNORE NULLS)
OV... |
3589 | 16 Mar 2015 @ 10:41 PDT | Database | Reply | Stored procedure for passing date values | Hi Mahesh,
why is using a SP more standard than using a table?
If you use this the optimizer can do Incremental Planning in TD14.10:
AND A.DLVR_DT BETWEEN CURRENT_DATE
– (SEL MIN... |
3588 | 16 Mar 2015 @ 10:33 PDT | Database | Reply | CompressValueList add blank spaces in DBC.Columns field | FIELD_CMP is defined as CHAR(30), of course values are padded with blanks.
|
3587 | 16 Mar 2015 @ 10:30 PDT | Database | Reply | Best criteria for selecting NUPI | Hi John,
if SEQID is not used it's useless as PI column, in fact you don't need it at all.
What's the actual Primary Key of this table?
|
3586 | 16 Mar 2015 @ 10:18 PDT | Analytics | Reply | Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table | OLAP-functions are calculated after where/join/group by/having, so you have move it to a Derifed Table:
SEL FILLD_DT FROM CLM_TBL C
INNER JOIN
(
Sel EFF_DT,
Max (EFF_DT)
ov... |
3585 | 16 Mar 2015 @ 10:10 PDT | Analytics | Reply | Help with lag/lead function in Teradata | Hi Teresa,
you can't use a column number in the ORDER or PARTITION BY of an OLAP-function.
Simply change it to ORDER BY Activity_date
|
3584 | 16 Mar 2015 @ 12:46 PDT | Database | Reply | Stored procedure for passing date values | Hi Mahesh,
you can't use an SP in a SELECT.
Why do you actually need it?
Your example can easily be rewritten without it.
|
3583 | 16 Mar 2015 @ 12:40 PDT | Database | Reply | sliding window merge join | Sliding Window Join is applied only to partitioned tables, without partitioning there's no need for it.
|
3582 | 16 Mar 2015 @ 12:39 PDT | Database | Reply | Best criteria for selecting NUPI | Hi John,
this is not bad unless there are too many rows per PI value. Duplicate row checks will be done only for roews with the same PI (in fact, the same Row Hash).
If this sequence colu... |
3581 | 16 Mar 2015 @ 12:35 PDT | Database | Reply | Choosing Primary Index for a table | Q1/Q2/Q3: yes, of course
Q4: no, of course, there's only one PI per table (think of it as a kind of clustered index)
Q5: columns used for joins (in best case also used in Where) if the distri... |
3580 | 12 Mar 2015 @ 10:53 PDT | Database | Reply | Looking for TD 15 Database Installation on local system | Hi Paras,
Teradata Studio is only a client, you need a Teradata server.
You can download Teradata Express and run it on VMWare Player: VMware
|
3579 | 12 Mar 2015 @ 09:57 PDT | Database | Reply | subtract- datediff 52 weeks to a date | 52 weeks are 52*7 days, simply do current_date - 52*7
|