3028 | 15 Aug 2014 @ 06:14 PDT | Database | Reply | swap values in two columns using update SQL | Hi Moutusi,
it's that easy :-)
Unless you try it on MySQL, which will screw up the data to
A B
2 2
4 4
6 6
|
3027 | 15 Aug 2014 @ 06:12 PDT | Database | Reply | Partition effectiveness | Hi Robin,
you defined 416.741 partitions and even if there a PARTITION stats the optimizer must still include all possible partitions in the plan.
You should limit the number of partitions, ... |
3026 | 15 Aug 2014 @ 05:57 PDT | Database | Reply | Determine new table skew for a different PI | Hi Steven,
how do you define the skew factor?
I use this for calculating the percent deviation from average:
SELECT
HASHAMP(HASHBUCKET(HASHROW(col))) AS vproc,
COUNT(*) AS cnt,
100... |
3025 | 15 Aug 2014 @ 05:36 PDT | Database | Reply | Determine status on a given date | Instead of a join you might use EXPAND on to create the missing dates, see
http://forums.teradata.com/forum/general/creating-missing-observations
|
3024 | 15 Aug 2014 @ 04:54 PDT | Database | Reply | Varchar to Timestamp(6) conversion | Hi Orlando,
what's your TD release?
In TD14 you might utilize Oracle's TO_TIMESTAMP, which is a bit more flexilbe regarding single digit day/hour/minute/second, but month still needs to b... |
3023 | 14 Aug 2014 @ 03:06 PDT | Database | Reply | cast and concatenate | Hi Salokh,
remove the dashes from the hex string:
sel * from dbc.databasespace where tableid= '00001C000000'xb
|
3022 | 14 Aug 2014 @ 02:19 PDT | Database | Reply | How to limit rows to only those where there are duplicates of a specific column? | Hi Chandan,
based on your result set (I didn't fully understand your explanantion) this should work:
CREATE VOLATILE TABLE vt ( ACCT INT, ID INT, code CHAR(3))
ON COMMIT PRESERVE... |
3021 | 11 Aug 2014 @ 01:37 PDT | General | Reply | Query performance issue | You said 'I did not change anything', well, I would call changing the PI a big thing :(
So you don't know how to choose a good Primary Index, you don't know what Soft RI is, you do... |
3020 | 11 Aug 2014 @ 11:02 PDT | Tools | Reply | Fast load Error | And there's another error, remove the period before DEFINE:
• Commands may begin with a period, but do not have to begin with a period.
• If there is no leading period, then t... |
3019 | 11 Aug 2014 @ 08:58 PDT | General | Reply | Case statement | Hi Pragov,
you should know the functionality of basic functions like SUBSTRING, 5 indicates the number of characters returned, i.e. ';BYE;'. If you want to compare to ';OTHER;... |
3018 | 11 Aug 2014 @ 08:51 PDT | General | Reply | Query performance issue | Soft RI (WITH NO CHECK OPTION) does not validate any referential integrity.
WITH CHECK OPTION validates on a statement level doing a kind of [NOT] EXISTS correlated subquery, but you can spot... |
3017 | 11 Aug 2014 @ 08:39 PDT | Database | Reply | Repeatability of random sampling | There's no random seed, if there's only a single row inserted/deleted/updated it would be impossible to repeat that sample.
The only way is materializing the result of the sample select in... |
3016 | 10 Aug 2014 @ 12:12 PDT | General | Reply | Case statement | Hi Pragov,
it would be helpful to know what you mean by "It is not working"
There's probably an error message as INSTR returns the position where it found the 19th ';' and y... |
3015 | 10 Aug 2014 @ 12:07 PDT | General | Reply | Creating Missing Observations | It's hard to tell without knowing the data.
You might not order by the correct column, based on your example eff_dt was the same for all rows, but period_date was unique.
Or there are multipl... |
3014 | 10 Aug 2014 @ 07:04 PDT | General | Reply | Query performance issue | Ok, there's no step indicating an RI check, this is only visible when using WITH CHECK OPTION.
You implemented Soft RI (WITH NO CHECK OPTION) and since that time it's slower?
Did ... |
3013 | 10 Aug 2014 @ 06:55 PDT | General | Reply | Creating Missing Observations | Looks like a perfect case for EXPAND ON, the missing rows get the last existing row's data.
The needed PERIOD can be created on-the-fly:
SELECT Key
Eff_Date,
Start,
End,
... |
3012 | 09 Aug 2014 @ 07:37 PDT | Database | Reply | Stored procedure conversion from Oracle to Teradata | There's no new session assigned, it's within the calling user's session.
|
3011 | 08 Aug 2014 @ 12:33 PDT | Database | Reply | coalesce function | Add a WHERE AP.RECORD_DATE IS NULL to check if there are any NULLs.
|
3010 | 08 Aug 2014 @ 10:58 PDT | Database | Reply | coalesce function | What do you mean by "i don't see them", you must be more specific.
The COALESCE will return the DATE casted to a string or 'Not yet recorded'.
|
3009 | 08 Aug 2014 @ 10:54 PDT | Database | Reply | cast and concatenate | So you need some TRIMs:
CAST(TRIM(cust_id) || SUBSTRING('0' || TRIM(cust_nbr) FROM CHAR_LENGTH(TRIM(cust_nbr)) - 2 + 1) || TRIM(cust_dig) AS BIGINT)
|
3008 | 08 Aug 2014 @ 10:48 PDT | General | Reply | Query performance issue | If there's Soft RI there shouldn'tt be any kind of check.
Can you show the Explain?
|
3007 | 08 Aug 2014 @ 10:41 PDT | Database | Reply | Delete from table all | Mantra: There's no difference between DELETE and DELETE ALL, ommmmm.
A full table SQL DELETE [ALL] will be faster than a MLoad DELETE, because MLoad actually scans the table.
If there's a... |
3006 | 08 Aug 2014 @ 10:34 PDT | Database | Reply | Stored procedure conversion from Oracle to Teradata | A Stored Procedure in Teradata is rewritten as a C program and then compiled into a shared object on Linux. When you CALL the SP it's actually calling an exported function of that shared object... |
3005 | 08 Aug 2014 @ 10:20 PDT | Database | Reply | cast and concatenate | CAST and || are both Standard SQL, so exactly the same in Teradata.
There's no RIGHT function in Teradata, but it's easy to rewrite:
RIGHT(col, n) = SUBSTRING(col FROM CHAR_LENGTH(co... |
3004 | 08 Aug 2014 @ 03:09 PDT | General | Reply | Query performance issue | As you access the same table twice you might also try a Windowed Aggregate Function instaed of a join:
SELECT
count(distinct(TBL_OUTPUT.C_CID)) as CNT
FROM
( SELECT
... |