4178 | 15 Feb 2016 @ 07:53 PST | Database | Reply | Expression for the last day of the month based on Accounting Period | *Existing report*, what reporting tool?
Do you need to do the calculation within the tool or using Teradata SQL?
Is this based on the actual date? E.g. if the user selects "period 2" to... |
4177 | 14 Feb 2016 @ 04:50 PST | Database | Reply | PPI Dropping/Adding Partitions | You can't DROP/ADD/MODIFY a CASE_N-partitioning (unless the table is empty).
|
4176 | 13 Feb 2016 @ 04:42 PST | Database | Reply | Obtain the text of a join index from the data dictionary? |
SELECT * FROM dbc.JoinIndicesV
returns the DatabaseName/TableName for both the JI and it's base tables.
|
4175 | 13 Feb 2016 @ 04:28 PST | Database | Reply | char(23) to timestamp(6) | Hopefully this is done during load, you should store timestamps in a string.
If you got different formats you must a CASE to match each possible format:
CASE
WHEN x LIKE '____-__-__ %... |
4174 | 12 Feb 2016 @ 10:46 PST | Connectivity | Reply | ODBC ts time stamp for now() | I don't know exactly about ODBC, but {ts 'now()'} is definitely wrong, this tries to convert the literal 'now()' into a timestamp, imho this should be {fn now()} instead.
|
4173 | 12 Feb 2016 @ 10:41 PST | General | Reply | Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval. | That's an Oracle query, of course it fails on Teradata, date/time handling is totally different.
Is this trying to adjust timezones? And comparing timestamps using TO_CHAR is very strange.
Yo... |
4172 | 11 Feb 2016 @ 01:06 PST | Database | Reply | Calculation of prescription drug adherence | Argh, I simplified the query and didn't double check.
Change this line
,CAST((INTERVAL(pd) DAY) AS INT) -- days within range
to
,CAST((INTERVAL(pd_in_range) DAY) AS INT) -- days wit... |
4171 | 11 Feb 2016 @ 10:41 PST | Database | Reply | Calculation of prescription drug adherence | Hi Brent,
you're running a Teradata version where you have to list all columns of a CTE (afaik before TD15), sorry for that:
WITH RECURSIVE cte
( Person -- must list all columns... |
4170 | 11 Feb 2016 @ 09:35 PST | Database | Reply | Wrong Statistic Estimations of Optimizer Teradata 14.10? | If the value is within the range of known values but not one of the exactly known values the optimzer does this average calculation based on that specific interval CEILING(OtherRows / OtherVal... |
4169 | 11 Feb 2016 @ 08:19 PST | Database | Reply | How to join minus function in query. | MINUS is a set operator, you need a simple calculation:
select item, saledate,
max(orgprice-sprice)
from
(
select *
from trnsact
qualify -- earliest date where the sale price... |
4168 | 11 Feb 2016 @ 05:47 PST | Database | Reply | Calculation of prescription drug adherence | Hi Brent,
that's a nice puzzle :-)
First I thought that it would be possible to solve with OLAP-functions, but I didn't find a solution
.
As it's based on dat... |
4167 | 10 Feb 2016 @ 12:30 PST | Database | Reply | Wrong Statistic Estimations of Optimizer Teradata 14.10? | Hi Roland,
the optimizer never fully trusts stats :-)
It seems to think that the user has a better knowledge and this value might exists, thus it's assuming the average number of rows per val... |
4166 | 10 Feb 2016 @ 12:11 PST | Database | Reply | Extract VS INTERVAL | A WHERE-condition should be sargable (https://en.wikipedia.org/wiki/Sargable), there should be no function/calculation applied to a column.
Better move the calculation to the BETWEEN:
where
... |
4165 | 10 Feb 2016 @ 08:20 PST | Database | Reply | How to define an array of varchars in Stored Procedure Language? | I think you found a bug :)
I just checked the actual SQL submitted in dbc.QryLogV:
...TD_SYSFNLIB.ARRAY_AGG ( token , NEW array_strings ( ), tokennum , '1') FROM TABLE(TD_SYSFNLIB... |
4164 | 10 Feb 2016 @ 08:00 PST | Tools | Reply | How to change a date column to show only month and year | Running your code you will notice that it return the next to last day of the previous month.
You probably meant DATE - EXTRACT(DAY FROM DATE)+1, but this is a bit less efficient (two time ... |
4163 | 10 Feb 2016 @ 02:46 PST | Database | Reply | How to define an array of varchars in Stored Procedure Language? | I just checked it, for SQL SECURITY DEFINER (which is the default) it runs fine when you create the SP within your own user and fails when it's in a different database. For SQL SECURITY OW... |
4162 | 09 Feb 2016 @ 02:27 PST | Database | Reply | BTEQ Export - Odd File Output | BTEQ REPORT is fixed width out for direct printing.
Switching to TPT would really simplify this, FILE_WRITER plus Format = 'DELIMITED' :-)
|
4161 | 09 Feb 2016 @ 10:02 PST | Database | Reply | How can I get details of C and JAVA UDFs from my Teradata server? | The only way is a SHOW [SPECIFIC ]FUNCTION xxx
|
4160 | 09 Feb 2016 @ 09:55 PST | UDA | Reply | Converting a string to Timestamp and checking if it has valid timestamp | In TD15.10 there's a new TRYCAST function, which returns NULL for invalid data, but it doesn't accept a FORMAT:
TRYCAST(col AS TIMESTAMP(6))
|
4159 | 09 Feb 2016 @ 09:52 PST | Database | Reply | CREATE SYSTEM TIME TABLE in error | Hi Pierre,
no, works in both ANSI and BTET mode.
|
4158 | 09 Feb 2016 @ 09:09 PST | Database | Reply | How to define an array of varchars in Stored Procedure Language? | - The C-functions to access arrays are ment to be used in a C-UDF only (which would be better suited to do logic in complicated loops than SQL).
- STRTOK_SPLIT_TO_TABLE can be used on... |
4157 | 09 Feb 2016 @ 08:10 PST | Database | Reply | Merge Error |
5758 The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s).
... |
4156 | 09 Feb 2016 @ 07:49 PST | Database | Reply | CREATE SYSTEM TIME TABLE in error | Hi Pierre,
ANSI Temporall was introduced in TD15 and you're running TD14.10 :-)
|
4155 | 09 Feb 2016 @ 07:48 PST | UDA | Reply | Converting a string to Timestamp and checking if it has valid timestamp | There's no built-in function to check if a string holds a valid timestamp, you might need to create a C-UDF or a really complicated SQL-UDF.
|
4154 | 09 Feb 2016 @ 07:45 PST | Teradata Applications | Reply | How to install Teradata on UBUNTU | Teradata runs on SLES only, no other Linux distro is officially supported. It's just starting another VM, what's so hard about it?
On a real Teradata you're not going to ins... |