3978 | 17 Sep 2015 @ 07:21 PDT | Database | Reply | Case Statement |
select
case Status
when 3 then 'Approved'
when 4 then 'Declined'
else 'In Progress'
end,
count(*)
...
group by 1
|
3977 | 11 Sep 2015 @ 08:33 PDT | Database | Reply | Which DBS control settings affect Teradata Performance? | In most environments both Prod and Dev will have exactly the same settings.
|
3976 | 11 Sep 2015 @ 08:31 PDT | Database | Reply | Partitioning Issue | A single row table can be replaced by a view without accessing any table. In that case the optimizer always knows the actual value, similar to the dynamic plan in TD14.10, but without cost threshol... |
3975 | 11 Sep 2015 @ 08:20 PDT | Viewpoint | Reply | Understanding REQ CPU in Query Spotlight | CPU seconds <> clock seconds
There's more than one CPU in a your system, e.g. if you got 100 CPUs there are 100 CPU-seconds per second.
|
3974 | 10 Sep 2015 @ 01:15 PDT | Private Forum |
3973 | 10 Sep 2015 @ 12:54 PDT | Database | Reply | Which DBS control settings affect Teradata Performance? | DBS control settings regarding performance (there's just a small number) usually default to a proper value.
Besides increasing DictionaryCacheSize and maybe MaxParseTreeSegs you... |
3972 | 10 Sep 2015 @ 12:37 PDT | Database | Reply | Teradata Query to handle previous day amount |
select ID, Date, Amount,
Amount + coalesce(max(amount)
over (partition by ID
order by Date
rows between 1 prece... |
3971 | 10 Sep 2015 @ 12:33 PDT | Database | Reply | Newbie to Teradata | All metadata is found in dbc-views, details can be found in the Data Dictionary manual.
Access rights for all users/databases: dbc.AllRightsV
Access rights based on roles: dbc.AllRoleRigh... |
3970 | 09 Sep 2015 @ 10:50 PDT | Database | Reply | Partitioning Issue | Assuming that TMP_RUNDATE is a single row table this should work as expected if you replace it with a View:
replace view TMP_RUNDATE as select date '2014-10-01' as RUN_DATE
|
3969 | 08 Sep 2015 @ 10:12 PDT | Database | Reply | Convert scientific notation to decimal | CAST(col AS NUMBER) should work
|
3968 | 08 Sep 2015 @ 10:12 PDT | Database | Reply | Partitioning Issue | In TD14.10+ "Incremental Planning and Execution" should be switched on, so settting the Queryband will not help.
But your test data seems to be small (based on estimated rows), so ... |
3967 | 08 Sep 2015 @ 01:29 PDT | Database | Reply | SQL Warning 5815 Function is not ANSI | Flagging SQL compliance was a SQL-92 feature, CAST is Standard SQL, but not in Entry Level.
A copy of SQL-92 can be found at:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
|
3966 | 06 Sep 2015 @ 01:38 PDT | Database | Reply | Stored Procedure (SELECTION FORM) EXPLAIN/SHOW/HELP | The previous page:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1148_112A/ch03.149.062.html
Invocation
Nonexecutable preprocessor declaration.
... |
3965 | 05 Sep 2015 @ 08:34 PDT | Database | Reply | Stored Procedure (SELECTION FORM) EXPLAIN/SHOW/HELP | The manual is for both Embedded SQL and Stored Procedures and clearly states that you cannot use EXPLAIN/SHOW/HELP in a SP:
http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_R... |
3964 | 05 Sep 2015 @ 05:05 PDT | General | Reply | How to find CONSECUTIVE values coming in a column(TERADATA) | There's no "original sequence" in a table in a relational DBMS.
You need to add an ORDER BY to get a reliable & repeatable result, if there's no ordering column you're ou... |
3963 | 31 Aug 2015 @ 01:30 PDT | General | Reply | convert julian 5 digit number to regular date | This behaviour is controlled by a global setting (CenturyBreak in dbccontrol).
Simply add '20' before casting:
CAST('20'||trim(Date_Created) AS DATE FORMAT 'yyyyddd')
&... |
3962 | 31 Aug 2015 @ 01:24 PDT | General | Reply | Spoil Table information in Data Dictionary | "We spoil the parser's dictionary cache for the table" removes all chached metadata about this table, e.g. after a ALTER TABLE or a CREATE INDEX, or after DELETE/UPDATE/INSERT of... |
3961 | 31 Aug 2015 @ 01:19 PDT | General | Reply | How to create a dynamic concatenation | Multiplying by 1000000 shifts the BR_NO seven digits to the left before adding the seven digits of ACCT_NO.
|
3960 | 31 Aug 2015 @ 10:59 PDT | Database | Reply | Aggregation | Hi Gnana Reddy,
you want a moving sum:
sum(col4)
over (partition by col1,col2
order by col3
rows between 3 preceding and current row)
|
3959 | 31 Aug 2015 @ 10:34 PDT | Database | Reply | Need help to write an SQL to validate mutiple date ranges combined from more than one SCD type2 tables. | Your INSERTs don't match your example.
If your step #2 returns the correct data you can simply add a LAST_VALUE:
WITH cte (
hid
,strt_dttm
,end_dttm
,h1name
,h2name
)
AS (
SE... |
3958 | 31 Aug 2015 @ 08:45 PDT | Database | Reply | Need help to write an SQL to validate mutiple date ranges combined from more than one SCD type2 tables. | What's your Teradata release?
|
3957 | 31 Aug 2015 @ 08:43 PDT | General | Reply | How to create a dynamic concatenation | You didn't tell that those columns were numeric.
Concatenation on numerics results in an automatic typecast, which results in leading blanks. You get rid of them when you TRIM:
... |
3956 | 31 Aug 2015 @ 12:44 PDT | Database | Reply | cast varchar value YYYY-MMM-DD HH:MI:SS to date | Any invalid data will result in "Invalid Timestamp".
Why is this column defined as a VarChar if it's supposed to be a Timestamp? This should be casted during load.
Instead of trying... |
3955 | 29 Aug 2015 @ 09:32 PDT | General | Reply | How to create a dynamic concatenation | There's an LPAD function:
cast(LPAD(BR_NO || ACCT_NO, 12, '0') as char(12)) || 'TDA'
|
3954 | 29 Aug 2015 @ 09:25 PDT | Database | Reply | TRIM Leading letters then Leading 0 | The easiest way to get what you want is probably a regular expression, find the first digit which is not a zero:
regexp_substr(col, '([1-9].*)')
|