3103 | 06 Sep 2014 @ 04:21 PDT | Database | Reply | Stored procedure Missing/Invalid SQL statement'E(3707) error | Cursors are evil in a parallel database system and nested cursor are even worse.
Why don't you create you delimited list using MAX(CASE) or recursion, there have been multiple threads on that ... |
3102 | 06 Sep 2014 @ 03:45 PDT | Database | Reply | Optimization of a Join Query | Hi Sarang,
seems like you only want a flag to detemine if data from the previous week exists.
You could extract rows for both dates in a single query and then use an OLAP function to check if the... |
3101 | 06 Sep 2014 @ 03:11 PDT | Teradata Studio | Reply | Changing TMODE from ANSI to TERA after connection created | The "normal" mode is the one used at your company :-)
Changing the transaction mode should not result in any parsing error, only the transaction semantics and some defaults will change. ... |
3100 | 06 Sep 2014 @ 03:06 PDT | Database | Reply | Complex SQL query | Hi Shaum,
your logic is based on ordering by ref_id and seq_nbr?
sum(case when depth = 1 then 1 else 0 end)
over (order by ref_id, seq_nbr
rows unbounded preceding)
|
3099 | 05 Sep 2014 @ 02:25 PDT | Database | Reply | Stored procedure Missing/Invalid SQL statement'E(3707) error | String concatenation is done with two not one pipe character:
SET sql_stmt_cont = 'UPDATE cntNextTable SET PROD_ENTRADA = ' || lista_entrada ||...
But why do you use a cursor to do multip... |
3098 | 05 Sep 2014 @ 07:30 PDT | Database | Reply | Importing Data from .XLS | No release of Teradata supports importing Excel files.
I doubt there's any DBMS which can load .xls directly, as it's Microsoft's proprietary format and changing with every release.
S... |
3097 | 05 Sep 2014 @ 04:33 PDT | Database | Reply | Help with macro | There's no way to do it in a macro.
In every DBMS you will need some Dynamic SQL to do so and a DBA will never allow that due to possible SQL Injection, e.g.
EXEC T('''2305'... |
3096 | 05 Sep 2014 @ 04:29 PDT | Database | Reply | Queue Tables | You're probably correct.
There's no WHERE-clause for SELECT AND CONSUME.
When you SELECT AND CONSUME a row it's deleted from the queue. You might re-insert it but it will be consumed ... |
3095 | 05 Sep 2014 @ 04:02 PDT | Database | Reply | re:spool space | Hi Shiva,
it's hard to tell without knowing the logical PK/FK and Explain.
Assuming ctr.cust_is is the PK you could rewrite it using EXISTS, imho this should be logically equivalent:
SELEC... |
3094 | 05 Sep 2014 @ 03:56 PDT | Teradata Applications | Reply | FASTEXPORT - Sorted order two further distribution between amps. But Why? | Those steps are needed to provide the ability to export a sorted result set using multiple sessions.
The query finished and each AMP has it's result sorted in a local spool.
This spool is the... |
3093 | 05 Sep 2014 @ 03:41 PDT | Database | Reply | Date in varchar and DD-MMM-YY FORMAT | Depending on a global system setting (Century Break in dbscontrol) or your Teradata release this might be easy.
If you're on TD14 you can utilize Oracle's TO_DATE:
WHERE TO_DATE(TRANSMI... |
3092 | 04 Sep 2014 @ 11:33 PDT | General | Reply | Extract email and date from field | Hi Sabrina,
if the data actually looks like this, you can do some simple POSITION/SUBTRING/TRIM
TRIM(LEADING '"' FROM SUBSTRING(x FROM 1 FOR POSITION(' ' IN x))) AS ... |
3091 | 04 Sep 2014 @ 01:48 PDT | General | Reply | Failed 6706: The string contains an untranslatable character error | Hi Praveen,
seems like col2 is in Character Set Unicode and there's a cast to Latin.
What's your Teradata release? INSTR is built-in in TD14, earlier you might have an own UDF implementat... |
3090 | 04 Sep 2014 @ 12:59 PDT | General | Reply | USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure | Simply change the code to get the expected result:
SELECT 'teradata' AS str,
'Select * FROM DB.Table_Name where brand LIKE ''%'||str||'%'' ;'
|
3089 | 04 Sep 2014 @ 12:57 PDT | Database | Reply | Full OuterJoin Issue | Hi Raja,
it's hard to tell, there's no obvious issue.
Can you compare both Explains (SEL vs. INS/SEL) if there's any difference?
Or do a SEL 1 EXCEPT ALL SEL 2 to see the actual diff... |
3088 | 03 Sep 2014 @ 12:54 PDT | Database | Reply | Full OuterJoin Issue | You wrote FULL join but in fact both are LEFT/RIGHT joins, simply check explain.
A WHERE-condition on the inner table removes the outer join, resulting in:
Select * from A LEFT join B... |
3087 | 03 Sep 2014 @ 12:48 PDT | General | Reply | USING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored Procedure | Hi Arun,
there are too many quotes:
SELECT 'teradata' AS str,
'Select * FROM DB.Table_Name where brand LIKE ''''%'||str||'%'''' ; ) AS EVNT_... |
3086 | 03 Sep 2014 @ 12:38 PDT | Analytics | Reply | using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant | CONCAT is an ODBC SQL function which is automatially converted to correct Teradata syntax by the ODBC driver (when a specific option is set).
In the ODBC manual there's a list of those functio... |
3085 | 03 Sep 2014 @ 12:21 PDT | Database | Reply | re:spool space | If you can locate old vs. new Explains in DBQL you will see what SOMETHING changed :-)
|
3084 | 03 Sep 2014 @ 12:18 PDT | Database | Reply | Creating table with defined PK but not defining PI | Hi Moutusi,
A few years ago the result of a SHOW TABLE was changed to return the logical constraints instead of the internally created indexes.
So you still get a UPI on (col1,col2), but you have... |
3083 | 03 Sep 2014 @ 12:14 PDT | General | Reply | Doubt with Rank Over Partition By Function | What have you tried?
Is there a known maximum limit of rows per ID?
Do you need the concatenated result based on a specific order?
What's your Teradata release?
|
3082 | 02 Sep 2014 @ 03:29 PDT | Tools | Reply | Custom Message in Bteq | NOTIFY:
- is not writing to standard output, but to EventLog on Windows or system log on Unix.
- must be specified before the SQL commands
- has a different syntax
.notify HIGH msg 'Bteq ... |
3081 | 02 Sep 2014 @ 01:08 PDT | General | Reply | STRIPING WHITE SPACES | Hex '00' is not a white space, TRIM by default removes hex '20'.
TRIM(TRAILING '00'xc FROM col)
|
3080 | 02 Sep 2014 @ 12:12 PDT | General | Reply | convert 13 digit epoch time to date? | If 1271664970687 includes milliseconds, you need to change the calculation, otherwise you try to add more than 40,000 years :-)
SELECT 1271664970687 AS x,
CAST(DATE '1970-01-01' + ... |
3079 | 01 Sep 2014 @ 09:41 PDT | Database | Reply | what is the significance of FORMAT in table DDL, what if we dont give any format ? | If you don't specify a FORMAT there's always a default.
col1: If BIGINT is changed to INT you will get 'numeric overflow' messages when you got values outside of the INT range (reg... |