3478 | 03 Feb 2015 @ 02:19 PST | General | Reply | Loading data into Table | Create a table with all the odd number between 1 and the maximum number of columns, e.g. 27 and then cross join:
CREATE TABLE odd_numbers(n int);
INSERT INTO odd_numbers VALUES (1);
INSERT INT... |
3477 | 02 Feb 2015 @ 09:40 PST | Analytics | Reply | Using Case When on Teradata | Hi Wiyana,
is the date hard-coded?
SELECT
Col1
,MAX(CASE WHEN DATE = 20150101 THEN col2 END)
,MAX(CASE WHEN DATE = 20150102 THEN col2 END)
FROM tab
GROUP BY 1
|
3476 | 02 Feb 2015 @ 09:32 PST | Database | Reply | wanted to get interval time in the format of Hour:Minute:Seconds | There's no datatype to hold an HOUR TO SECOND with more than 9999 hours.
So go with DAY TO SECOND or extract the various parts, calculate hours from days and then concatenate the parts to a st... |
3475 | 02 Feb 2015 @ 09:27 PST | General | Reply | Loading data into Table | I would load the data as-is and then process it within database.
E.g. load as a single VarChar and extract the columns using STRTOK:
STRTOK(x, ',', n)
n will be hard-coded (one SELECT ... |
3474 | 02 Feb 2015 @ 09:16 PST | Database | Reply | Partition of a Timestamp column | Hi Samir,
150130 is 1915-01-30, which definitely doesn't fit in any partition :-)
#1 and #2 are exactly the same, but #1 is preferred because it's more readable.
But what... |
3473 | 02 Feb 2015 @ 09:11 PST | Tools | Reply | Concatenate one column values into single row value | If your TD system includes XML, this is the simplest way:
SELECT
DatabaseName
,TABLENAME
,IndexNumber
,IndexType
,TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Columnname)|| ',... |
3472 | 31 Jan 2015 @ 08:07 PST | Database | Reply | Decimal issue | When you divide by 0.9 you increase the number of fractional digits to 8.
Simply cast back to 7 digits using
cast(sum(data_volume_month_01)*0.9 as dec(22,7))
|
3471 | 31 Jan 2015 @ 05:39 PST | General | Reply | TIME STAMP Difference in DAYS | Great you got the solution, but could you share it, too?
And could you mention which DBMS returns one day if it's actually less than one?
What does it return for
SELECT (TIMESTAMP '... |
3470 | 31 Jan 2015 @ 05:36 PST | Database | Reply | How to Round Timestamp(6) to nearest hour | Add 30 minutes to the timestamp and then truncate it:
((x + INTERVAL '30' MINUTE) (FORMAT 'YYYY-MM-DDBHH') (CHAR(13))) || ':00:00' (TIMESTAMP(0))
x + (INTERVAL '30... |
3469 | 31 Jan 2015 @ 05:29 PST | Database | Reply | Hierarchy Query using Recursive function | Do you really need the result as a comma-delimited list or as multiple rows?
|
3468 | 31 Jan 2015 @ 05:28 PST | General | Reply | Teradata-Combine multiple rows of a column to multiple columns in a row | Two approaches come to mind:
Old style MAX(CASE) over a ROW_NUMBER:
SELECT
NAME
,MIN(CASE WHEN rn = 1 THEN Department end)
,MIN(CASE WHEN rn = 1 THEN salary end)
,MIN(CASE WHEN... |
3467 | 31 Jan 2015 @ 05:19 PST | Database | Reply | REGEXP_INSPR works on litteral data, but not in SELECT | The oREPLACEs only replace a single character, so you better use oTRANSLATE instead:
(oreplace(oreplace(oreplace(Transaction_Amount,'0D'XC,''),',',''),' '... |
3466 | 31 Jan 2015 @ 04:01 PST | Database | Reply | how to query large rows in Teradata SQL? | Also make shure you set the "Maximum batch size for simple imports" in Tools-Options-Import to 999 (seems to be the max).
Additionally using .NET instead of ODBC should be faster.
|
3465 | 26 Jan 2015 @ 09:02 PST | Database | Reply | Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' | Every single quote within a string must be doubled:
AND SublineaProductoCD IN (''1'',''2'',''3'',''4'','... |
3464 | 26 Jan 2015 @ 08:16 PST | Database | Reply | why coalesce around time interval calculation does not work ? | Hi Ivan,
the COALESCE fails because it's you can't mix both INTERVAL and INTEGER.
I doubt the first cast returns the correct age, you get the number of days between start and end and then... |
3463 | 26 Jan 2015 @ 02:34 PST | Database | Reply | Question regarding RIs | There's no way to get the different types of RI from the system tables, only SHOW will return it.
Regarding the original post:
WITH CHECK OPTION checks using EXISTS/NOT EXISTS on a st... |
3462 | 26 Jan 2015 @ 02:14 PST | Database | Reply | Error Table | Hi Marco,
this info is found in dbc.ErrorTblsV.
|
3461 | 25 Jan 2015 @ 11:25 PST | Jobs | Reply | How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error? | Better use EXISTS instead:
Sel 1
where exists
(
select * from SOURCE_TABLE
where cast(CMPGN_RUN_DATE as date)=current_date-1 and TRANS_DATE =current_date-1
);
.IF ACTIVITYCOUNT ... |
3460 | 25 Jan 2015 @ 08:22 PST | Connectivity | Reply | Teradata ODBC Soutrce | Yes, that's the full file path.
|
3459 | 25 Jan 2015 @ 04:39 PST | Connectivity | Reply | Teradata ODBC Soutrce | I don't know about the settings in Windows VM Player as I run it on a Mac using VMWare Fusion.
But the Teradata VM is running Linux (SUES Enterprise Linus 10 or 11).
The PANIC m... |
3458 | 24 Jan 2015 @ 10:59 PST | Connectivity | Reply | Teradata ODBC Soutrce | No, there's no more Teradata on Windows.
But as it's a virtual machine the OS doesn't matter, you just need VMWare Player for Windows.
|
3457 | 24 Jan 2015 @ 09:36 PST | Connectivity | Reply | Teradata ODBC Soutrce | You can't connect to a database server if there's no server :-)
SQL Assistant is just a client (so you could connect to your existing MySQL server using ODBC), but for Teradata you... |
3456 | 24 Jan 2015 @ 08:43 PST | Connectivity | Reply | Teradata ODBC Soutrce | If you work on the PC where the Teradata VM is running you don't connect to localhost (only from within the VM).
You need the VM's IP instead: Open a terminal window in the VM and do a ifc... |
3455 | 24 Jan 2015 @ 04:14 PST | Database | Reply | Converting a date time | You can't reduce the precision of a timestamp (blame Standard SQL), so there are two solutions:
- don't use TIMESTAMP(6) for this column if only need TIMESTAMP(0)
- CAST to a string using... |
3454 | 24 Jan 2015 @ 04:08 PST | Database | Reply | Need help in tuning a Query | Hi Lalitha,
if you sum up 5 billion rows you will always need a lot spool space :-)
Estimated times are just estimates, actual run time might be totall different.
Wall clock times can't be c... |