3153 | 23 Sep 2014 @ 10:06 PDT | Database | Reply | Selecting all rows that have a unique column | Ups, sorry, I wrote nonsense, mixing ROW_NUMBER and COUNT:
select id, name, email
from source_table
qualify count(*) over (partition by id rows unbounded preceding) = 1
|
3152 | 23 Sep 2014 @ 09:13 PDT | Database | Reply | Selecting all rows that have a unique column | You need to use a kind of ROW_NUMBER instead of a group COUNT:
select id, name, email
from source_table
qualify row_number() over (partition by id order by rows unbounded preceding) = 1
But f... |
3151 | 23 Sep 2014 @ 09:03 PDT | Analytics | Reply | Teradata OLAP functions in SQL | Hi Sue,
I'm not sure, but you might do the 2nd sum in a Derived Table and then cross join to prdlkup and use a SUM OVER like this, fully untested :-)
select a.prd_id,
a.prd_name,
... |
3150 | 21 Sep 2014 @ 10:53 PDT | Database | Reply | DBC.DATABASESVX | Currentperm and maxperm are stored in dbc.DiskSpaceV for each AMP/database.
|
3149 | 21 Sep 2014 @ 10:51 PDT | Database | Reply | help with recursive sql | You must define WITH first and then SELECT from it:
WITH RECURSIVE parse_list (input_date, site_id, delim_pos, item_num, element, remainder) AS
(
SELECT
input_date, site_i... |
3148 | 21 Sep 2014 @ 03:54 PDT | Database | Reply | NO PRIMARY INDEX table in Teradata | LOAD in a NoPI table is approx. 30-40% faster.
Insert/Select from NoPI to PI is slower than tables with matching PIs because there must be a redistribution/sort instead of a direct merge.
... |
3147 | 21 Sep 2014 @ 03:17 PDT | General | Reply | Stored Procedure | Simply create the Volatile table first and then the SP.
Of course you should have some code within your SP which deals with an existing VT, otherwise you can't CALL it a 2nd time withi... |
3146 | 21 Sep 2014 @ 03:13 PDT | Database | Reply | Profiles and Account Strings | No DBC table needs to be updated (in fact you can't update those tables directly).
You simply run a
MODIFY PROFILE myProfile AS ACCOUNT = ('aaa','bbb','bbb');
... |
3145 | 20 Sep 2014 @ 04:47 PDT | Aster | Reply | graphGen output table | Interesting option, never heard of it...
I don't have access to an Aster system right now, but your quote seems to imply you can simply set the output_format to 'nodes_and_edges'. If t... |
3144 | 20 Sep 2014 @ 04:28 PDT | Database | Reply | How to verify Data Pattern in a Column | Hi Hara,
what's your TD release?
In TD14 there are Regular Expressions which can easily do what you need.
|
3143 | 20 Sep 2014 @ 04:27 PDT | Database | Reply | Grants needed for a trigger | The owner of an object is the database where it's created.
This should fix the error:
GRANT INSERT ON "DEV_DB" TO "DEV_DB" WITH GRANT OPTION;
|
3142 | 19 Sep 2014 @ 10:50 PDT | General | Reply | Convert to Hex | There's no built-in way to cast from/to bytes besides those new functions in TD14.
You need to write a C-UDF.
|
3141 | 19 Sep 2014 @ 10:32 PDT | Analytics | Reply | can we have Rank function using rows between 1 preceding 1 preceding | You can utilize the RESET WHEN option.
Untested:
select
...,
row_number()
over (partition by userid
order by date
reset when date - case when channel type = ... |
3140 | 19 Sep 2014 @ 10:26 PDT | Analytics | Reply | Teradata OLAP functions in SQL | Hi Sue,
the p query is strange, do you really need a cross join between a and b?
Is b.prdname actually including wildcards?
Can you show some input/expected result set?
|
3139 | 19 Sep 2014 @ 10:24 PDT | Aster | Reply | Histogram_Map function - need help | Hi Wendy,
the histogram_map/histogram_reduce combination only creates tabular output.
As Aster Lens only supports NpathViz and CFilterViz output you need a visualization tool to get graphical out... |
3138 | 19 Sep 2014 @ 10:20 PDT | Database | Reply | all-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation Difference | This is usually caused by a bad Primary Index on a SET table resulting in a huge amount of duplicate row checks.
Check the number of rows per PI value of the target table.
|
3137 | 19 Sep 2014 @ 02:34 PDT | Database | Reply | No room available in Database testdb. | Hi Parth,
there's no way to increase the permspace of a TDE, next time download the 40GB or 1TB version, not the 4GB version.
Btw, "analyse DBC.TableSize & DBC.DiskSpace"... |
3136 | 17 Sep 2014 @ 12:54 PDT | General | Reply | Convert to Hex | I didn't know you can apply TO_CHAR to a CHAR :)
Hopefully you're on TD14, TO_BYTES returns BYTE which can be used as input to FROM_BYTES to get a VARCHAR again:
SELECT FROM_BYTES(TO_BY... |
3135 | 17 Sep 2014 @ 10:29 PDT | General | Reply | Convert to Hex | Your example will not work as-is in Oracle.
What is the datatype of your input, VarChar, VarByte or any INT?
And what do you want as output, VarChar or VarByte?
In TD14 you might utilize... |
3134 | 17 Sep 2014 @ 04:09 PDT | Analytics | Reply | Outlier Treatment in Teradata | Hi Abhinav,
seems like you have to talk to your DBA to get a higher spool limit.
|
3133 | 16 Sep 2014 @ 11:25 PDT | Analytics | Reply | Outlier Treatment in Teradata | Hi Abhinav,
this is not going to work, you must change the count to COUNT(*) OVER (). And you can probably remove the GROUP BY, too.
What are the datatypes of those columns?
|
3132 | 16 Sep 2014 @ 12:16 PDT | Database | Reply | The Quartile splitup Which trying to approach in Query Level . | I didn't read all that text, but it seems like your narration doesn't fit the definition of a quantile.
Might be a equally sized buckets instead, check WIDTH_BUCKET:
WIDTH_BUCKET(salary... |
3131 | 16 Sep 2014 @ 11:32 PDT | Database | Reply | Use While loop in Stored Procedure in Teradata | There's an END missing:
CREATE PROCEDURE proc (
IN i INTEGER,
IN j INTEGER)
BEGIN
DECLARE ii integer;
set ii=i;
while (ii<j) do
begin
set i... |
3130 | 16 Sep 2014 @ 11:27 PDT | Database | Reply | TIme Difference | Hi Jugal,
you cast current_time to a CHARs and then substract two CHARs, what do you expect?
If you want the result to be a CHAR:
CAST(CAST(CURRENT_TIME AS FORMAT 'hh:') AS CHA... |
3129 | 16 Sep 2014 @ 11:09 PDT | Database | Reply | SQL | You might simply utilize Teradata's sys_calendar (or yor own calendar implementation):
WHERE (Year_No, Month_No)
IN (SELECT year_of_calendar, month_of_year
FROM sys_calendar.cale... |