2653 | 27 Feb 2014 @ 07:43 PST | Database | Reply | no more spool space in recursive query | Using recursion for this task will result in a spool file with an approximate size of
(COUNT(*) + 1) * SUM(CHAR_LENGTH(state)+1) / 2 for each cust_id
Is there a known maximum number of states per... |
2652 | 27 Feb 2014 @ 07:33 PST | Database | Reply | Getting a dynamic Sample | You can assign a percentage based on a PERCENT_RANK * 100. If it needs to be statistically random you must use a Derived Table to create a RANDOM(1,2000000000) number to ORDER BY it ... |
2651 | 27 Feb 2014 @ 07:06 PST | Database | Reply | Query regarding Column and row merge | Wow, that's a sick data model, hopefully you need this query to fix this mess.
SELECT
t1.Cnbr
,CASE
WHEN t2.Att1 = 'Income' THEN t1.Att1
WHEN t2.Att2 = 'Incom... |
2650 | 27 Feb 2014 @ 01:20 PST | Database | Reply | Getting a dynamic Sample | Hi San,
some questions:
Are the shared departments known in advance or should the query find them?
Are there multiple combinations?
Can there be more than two shared departments?
Is the alloca... |
2649 | 26 Feb 2014 @ 11:57 PST | Database | Reply | Change One Row Data To Multiple Rows. | What's your TD release?
In TD14 there's a STRTOK_SPLIT_TO_TABLE function:
SELECT *
FROM
TABLE (STRTOK_SPLIT_TO_TABLE(myTable.name,myTable.col1,'#')
RETURNS (outkey VARC... |
2648 | 26 Feb 2014 @ 11:39 PST | Database | Reply | Teradata OLAP Functions |
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY GLAccount
ORDER BY COUNT DESC, Product) = 1
Assuming that COUNT is the result of a aggregation you can simply replace COUNT with the ac... |
2647 | 25 Feb 2014 @ 11:49 PST | Tools | Reply | fast export and mload in fastload format issue - error code 2793 | Hi Cheeli,
the default for FastExport is INDICATOR mode, you need to modify your LAYOUT:
.LAYOUT DATA_LAYOUT INDICATORS;
Or let FastExport create the MLoad:
.EXPORT .... MLSCRIPT xxx.... |
2646 | 25 Feb 2014 @ 11:01 PST | Tools | Reply | fast export and mload in fastload format issue - error code 2793 | Hi Cheeli,
2793 is the result of the MARK DUPLICATE INSERT option, i.e. all rows already exist in the target table.
|
2645 | 25 Feb 2014 @ 10:06 PST | General | Reply | Migrate Oracle UDF to Teradata UDF | What kind of UDF?
SQL or C/Java?
|
2644 | 25 Feb 2014 @ 10:05 PST | Database | Reply | Inserting multiple values with single quotes in single field | Hi Sundeep,
it's not specifically Teradata, every DBMS works like that, to get a single quote within a string you need to write two single quotes.
|
2643 | 25 Feb 2014 @ 09:58 PST | Database | Reply | Update SQL query is taking long time to execute | Did you check DBQL for elapsed time and CPU/IO usage?
If it's actually 100 rows this should run much faster (even if it's changing the PI and partitioning).
Are there any Secondary Indexe... |
2642 | 25 Feb 2014 @ 09:41 PST | Database | Reply | Unicode Substr issue with special characters | Hi Irfan,
hopefully you want this SQL to split the data in two columns, a VARCHAR is definitely plain wrong.
CASE WHEN POSITION(' ' IN TRIM(price)) > 0 THEN SUBSTRING(TRIM(price) FRO... |
2641 | 21 Feb 2014 @ 12:27 PST | Database | Reply | Multiple values using case statement |
param_file must be a single line readable text file. Param values are separated by blanks where strings must be enclosed in single quotes, e.g.
.ACCEPT numparam, charpara from par... |
2640 | 21 Feb 2014 @ 08:09 PST | Database | Reply | Date as Integer | CAST(datecol AS INT) returns TD's internal DATE format. You want something different:
extract(year from datecol) * 10000 +
extract(month from datecol) * 100 +
extract(day from daycol)
... |
2639 | 20 Feb 2014 @ 11:54 PST | Database | Reply | Date as Integer | You're trying to update every row in Test with all 73414 in sys_calendar.
There should be a DATE column in a calendar table:
update DB.Test
set Date_Key = cast(calendar_date as int);... |
2638 | 20 Feb 2014 @ 11:39 PST | Database | Reply | Multiple values using case statement | OK, I think I know what you want:
If there are any rows matching :ratescale only return those, otherwise return all rows.
SEL colA,colB FROM table1
WHERE colA = :ratescale
OR NOT EXISTS ... |
2637 | 20 Feb 2014 @ 11:11 PST | Database | Reply | Table Stats | You don't want/need to collect stats on each and every column because they must be re-collected from time to time (which is resource-intensive).
That's why you need to know/decide wh... |
2636 | 20 Feb 2014 @ 02:53 PST | Database | Reply | Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert | A sequence GENERATED ALWAYS AS IDENTITY with NO CYCLE should never return a duplicate value (based on that you don't need to define a PK on settlement_id).
What's your client and... |
2635 | 20 Feb 2014 @ 02:45 PST | Database | Reply | Table Stats | HELP STATS test COLUMN (test1) shows the actual data.
|
2634 | 20 Feb 2014 @ 02:43 PST | Database | Reply | Order by Date | Hi Reddy,
ORDER BY within a view is not allowed.
There's a workaround, but it's definitely not recommended: Add TOP.
REPLACE VIEW xxx AS SELECT TOP 100 PERCENT * FROM tab ORDER... |
2633 | 19 Feb 2014 @ 11:08 PST | Tools | Reply | fastload on linux | This is probably related to AIX using big-endian while your Linux box is little-endian.
I don't know if there's any way to load "wrong" endian data.
|
2632 | 18 Feb 2014 @ 10:00 PST | Database | Reply | SQL for Year and Month | Hi Natasha,
in a calendar table there's a date column, too.
You might use it instead and simply apply a FORMAT:
cast(cast(calendar_date as format 'mmm''yy') as char(7... |
2631 | 18 Feb 2014 @ 06:03 PST | Tools | Reply | How to Run Fast Export | You should always specify which error you get.
FastExport commands must start with a period. Both Fexp and SQL commands must be terminated by a semicolon:
.LOGTABLE DB.errors;
.LOGON jugalDB/J... |
2630 | 18 Feb 2014 @ 06:00 PST | Database | Reply | Insert in to date dimension is not working | Hi Reddy,
of course the Insert worked, it added 7 rows for monday to sunday where all other columns are NULL (the Select returned 73414 rows, but your target table is SET so duplicate rows where r... |
2629 | 17 Feb 2014 @ 11:05 PST | Database | Reply | Want to check if two specific columns exist in more than one table |
select DatabaseName, TableName
from dbc.ColumnsV
where ColumName in ('party_no', 'appl_id', 'co_no')
group by 1,2
having count(*) = 3
|