54 | 02 Sep 2016 @ 06:30 PDT | Database | Reply | replacing existing column | You can create the all_apps2 same as all_apps1 and write an update statement for prcd_amt
Or you could just use 'HELP TABLE all_apps1' to get the column list and copy paste it in the Selec... |
53 | 02 Sep 2016 @ 06:11 PDT | Database | Reply | DECLARE | You can define of parameter variables in Macors, Stored procedures and utilities such as Fastload, Mload etc. You cannot 'DECLARE' a variable outside of these. What are you trying to achiev... |
52 | 29 Aug 2016 @ 06:09 PDT | Database | Reply | extract time from timestamp(6) | SELECT CAST(SUBSTR(CAST(<COL> AS VARCHAR(20)),12,19) AS TIME(0))
|
51 | 29 Aug 2016 @ 03:59 PDT | Database | Reply | Merge different rows in one row | You could do this:
SEL Act_dt,cust_id and cust_line_id,MAX(flg1), MAX(flg2), MAX(flg3), MAX(metric1), MAX(metric2), MAX(metric3)
FROM <TABLE>
GROUP BY 1... |
50 | 23 Aug 2016 @ 03:40 PDT | Database | Reply | rows unbounded preceding | Why is the result of the 3rd row 500 (and not 500-80 = 420)? Should there be no subtraction within a month? Also is the date format in example given DD/MM/YYYY?
|
49 | 22 Aug 2016 @ 09:52 PDT | Database | Reply | Convert date > xx/01/xxxx of given month to first of following month | SEL CASE WHEN EXTRACT (DAY FROM COLUMNNAME) > 2 THEN ADD_MONTHS((COLUMNNAME - EXTRACT(DAY FROM COLUMNNAME)) +1,1) ELSE COLUMNNAME END
Use your column name inplace of 'COLUMNNAME'
|
48 | 22 Aug 2016 @ 09:11 PDT | Database | Reply | Convert date > xx/01/xxxx of given month to first of following month | Try this
SEL CASE WHEN EXTRACT (DAY FROM CURRENT_DATE) <> 1 THEN (CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)) +1 END
Just replace the CURRENT_DATE with your column name
|
47 | 22 Aug 2016 @ 03:49 PDT | Database | Reply | Resource Monitoring While Running Sql querries | You can use Viewpoint for this.
|
46 | 26 Jul 2016 @ 08:03 PDT | Database | Reply | What is difference between Redistribution of rows and Duplication of small table in AMPs | Yes, that is correct at a high level.
Redistribution will be based on hash code of the joining column(s)
|
45 | 21 Jul 2016 @ 09:01 PDT | Database | Reply | How to calculate minimum & maximum values based on a column only if the records are continuous | Refer to this thread
http://forums.teradata.com/forum/database/consolidate-rows
|
44 | 21 Jul 2016 @ 04:15 PDT | Database | Reply | How to calculate minimum & maximum values based on a column only if the records are continuous | Don't understand how you identify 'only when col2 is continuous' : A physical file has order of records, but in Table does not have an implicit order of records.
If this needs to ... |
43 | 13 Jul 2016 @ 01:54 PDT | Database | Reply | Calculate column value based on previous computed value | Here you go
SEL ORG, DEST
,COALESCE (FRU-SUM(VEG) OVER (ORDER BY VEG DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), FRU) AS FRU
,VEG
,FRU - SUM(VEG) OVER (ORDER BY VEG DESC ROWS UNB... |
42 | 12 Jul 2016 @ 07:58 PDT | Database | Reply | Calculate column value based on previous computed value | You can do this as given below
SEL VAL.*, FRU - CSUM(VEG,VEG DESC) AS TOTAL
FROM test VAL
|
41 | 12 Jul 2016 @ 04:15 PDT | General | Reply | cumulative sum and moving average | You can try something like below
SEL CSUM(salary,cont_join_date), MAVG(salary,6,cont_join_date)
FROM <TABLENAME>
WHERE EXTRACT (YEAR FROM cont_join_date) = 2015
AND EXTRACT (MONTH FROM ... |
40 | 29 Jun 2016 @ 06:26 PDT | General | Reply | Some roles are showing in DBC.ROLEINFO but not in DBC.ALLROLERIGHTS |
DBC.RoleInfo view provides information of all roles defined (It is derived from DBC.Roles table)
DBC.AllRoleRights view provides information about the rights granted to each role
You can fi... |
39 | 28 Jun 2016 @ 09:20 PDT | General | Reply | Store Backup of table strucutre into an table | If you have a process to maintain the tables without ALTER (Backup data, Drop & Recreate for changes), you could just directly use the 'RequestText' value from DBC.Tables
SEL Databas... |
38 | 28 Jun 2016 @ 09:11 PDT | General | Reply | transpose in teradata(columns to rows viseversa) | You can also try out the TD_UNPIVOT Function (available from Teradata 14.10)
|
37 | 28 Jun 2016 @ 08:56 PDT | General | Reply | table needs change | This can be done in multiple ways. Below is one way:
SEL ROLL
, CASE WHEN (EXECF =1 OR LARGEF = 1 OR MED = 1 OR SM = 1) THEN NULL ELSE AUTOF END AS AUTOF
, CASE WHEN (EXECF =1 OR LARGEF = 1 OR... |
36 | 28 Jun 2016 @ 08:19 PDT | General | Reply | Join Index | If your sql is covering sql. ie all columns needed to process the query are present in the existing index.
If a query refers to only columns that are part of the index, Teradata would decide ... |
35 | 28 Jun 2016 @ 08:00 PDT | General | Reply | How to detect updates in a teradata table at runtime without using timestamp column or last acess time stamp, | You may want to explore 'Triggers'
|
34 | 01 Sep 2014 @ 06:23 PDT | General | Reply | STRIPING WHITE SPACES | If you need to remove only WHITE SPACES, you can use TRIM
|
33 | 21 Nov 2013 @ 07:14 PST | General | Reply | Collecting Stats |
Generally speaking; Yes you should collect stats on the column, so that optimiser is aware of the low cardinality of the column
You can also compare Explain plan in both... |
32 | 18 Nov 2013 @ 07:51 PST | Data Modeling | Reply | Financial.... LDM | Yes FSLDM has 10 Subject Areas, which you have listed.
Also:
"The current version of the FS-LDM contains more than 3,100
entities, 12,700 attributes, and 5,100 relationships."
|
31 | 18 Nov 2013 @ 07:36 PST | Database | Topic | Start from specific sql in Stored Procedure | Is it possible to start execution of a Stored Procedure from a specific location?
My SP has a series of sqls with STEP_ID assigned. I need to pass a STEP_ID value and excute only from that STEP_ID... |
30 | 15 Sep 2013 @ 02:17 PDT | Database | Reply | Bulk Loading | You can try using Identity Column in DDL
|