#DateForumTypeThreadPost
2926 May 2011 @ 11:43 PDTDatabaseReplyFULL OUTER JOIN syntax and problems with correct outputAddendum: SEL * FROM a FULL OUTER JOIN b ON (b.bb='b') AND (a.ab='a') AND (a.aa=b.ba) WHERE NOT (a.ab IS NULL AND b.bb IS NULL ) ORDER BY 1,3; --great 5 rows is the only way I can seem...
2826 May 2011 @ 11:13 PDTDatabaseTopicFULL OUTER JOIN syntax and problems with correct outputI've been having difficulty with the position of the WHERE/AND clauses within a FULL OUTER JOIN and filtering of the the data on either side of the full outer join. It seems if i'm doing a self joi...
2717 Feb 2011 @ 03:28 PSTDatabaseReplyPermSpace of a Secondary Index Okay got the answer: COLLECT DEMOGRAPHICS FOR retail.ITEMPPI INTO qcd_samples ALL; Then SEL databasename ,tablename ,SubTableID ,SUM(RowCount*AvgRowSize) AS Si...
2601 Feb 2011 @ 01:06 PSTDatabaseTopicPermSpace of a Secondary Index I am wanting to compare the difference in size between a table's NUSI/USI/Join Index/Hash Index. The size of last two are easy to find because they are represented as another table in the dbc.Table...
2525 Jan 2011 @ 08:18 PSTDatabaseReplyDelete duplicate rows(keep only one row from duplicate rows)With any luck you won't get a: 5606 Statistical Functions not allowed in WITH clause. Explanation: Statistical Functions not allowed in WITH clause. Generated By: Opt modules For Whom: End User
2425 Jan 2011 @ 08:17 PSTDatabaseReplyDelete duplicate rows(keep only one row from duplicate rows)FYI 3706 Syntax error: %VSTR. Explanation: The request contains a syntax error. ’%VSTR’ identifies the approximate location of the error or identifies the syntax problem. Generated By...
2325 Jan 2011 @ 08:06 PSTDatabaseReplyDelete duplicate rows(keep only one row from duplicate rows)perhaps give a WITH derived table a go. Otherwise I'd create a Volatile table and split the aggregation straight out of the DELETE... WITH dups(col1, col2, col3, table_rank) AS ( select ...
2222 Jan 2011 @ 04:31 PSTDatabaseTopicHere's a neat little script to get row stats for UPI/NUPI/USI/NUSI selection on the_table.the_rowOne day I'll add the access frequency data... unless somebody else wants to beat me :) WITH the_table_summary(the_row, _count, _rank) AS ( SEL the_row, COUNT(the_row), RANK() OVER (ORDE...
2121 Jan 2011 @ 06:12 PSTToolsReplyRegarding Temporary SpaceAddendum: Temporary space is allocated before spool space. Both are derived from existing free space. If unspecified, default is set to max of immediates owner. http://www.teradatau.courses.ter...
2021 Jan 2011 @ 06:06 PSTToolsReplyRegarding Temporary SpaceThere is not a lot of information about the reason behind why this "Temporary Space" allocation exists. All I have found out is: Temporary space is a Teradata "Extended Attribute" reserved prior t...
1919 Jan 2011 @ 04:24 PSTDatabaseReplyDATE casting and INTERVAL addition or subtraction confusion.Never mind - I didn't realise bracket precedence: sel ((DATE '2005-03-01' - DATE '2005-01-01') DAY) + interval '3' day; *** Query completed. One row found. One column returned. ((2005-03-01...
1818 Jan 2011 @ 11:51 PSTDatabaseTopicDATE casting and INTERVAL addition or subtraction confusion.The following works fine: SEL CAST((DATE '2005-03-01' - DATE '2005-01-01') AS INTERVAL DAY) + INTERVAL '3' DAY; *** Query completed. One row found. One column returned. ((2005-03-01-2005-01...
1729 Nov 2010 @ 02:33 PSTDatabaseReplyUsing QUALIFY and GROUP BY together gives 3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP???Well put. I've also been walking about with this bit of code as a primer: SELECT last_name ,salary_amount ,department_number , AVG(salary_amount) ave_sal2 ,COUNT(*) OVER (PARTI...
1626 Nov 2010 @ 11:54 PSTGeneralReplyTeradata Express 12 BTEQWIN .LOGON command errorI also noticed that you need to select an ASCII charset otherwise the logon will also fail.
1526 Nov 2010 @ 11:47 PSTUDAReplyTIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)Fred's solution turning off ODBC extensions worked for my example too: SELECT CAST((TIME '09:30:22' - INTERVAL '01:20:10.45' HOUR TO SECOND) AS TIME(2));
1413 Nov 2010 @ 08:36 PSTDatabaseTopicIs it possible to convert to a date interval string literal to be used within a select?I want to use the result of (1) or (2) to be used in (3). but (4) returns an error as varchar(255) is not string literal. --(1) SELECT (DATE'2005-02-01'- DATE'2004-01-01') MONTH; --result: 13 ...
1313 Nov 2010 @ 08:25 PSTDatabaseReplySubtracting time fieldsPerhaps the following may suit? SELECT (TIME '18:45:14'- TIME '17:41:05') HOUR TO SECOND(0);
1213 Nov 2010 @ 05:43 PSTTeradata ApplicationsReplyQuestioning the results of a Window Remaining FunctionAs shown by martinbarrow, this is the accepted solution.
1117 Oct 2010 @ 03:59 PDTCloud ComputingReplyTeradata DBMS is in bad state in TD Express 12 images (40GB as 1TB) Okay, failing that I just re-installed TD quick as you can say "VirtalBox virtual disk snapshots".
1017 Oct 2010 @ 03:55 PDTTeradata ApplicationsReplyRESET keyword in Teradata 12 CertificationThis looks like an error in the online course then. In the ordered analytic functions it's one of the last topics. Shall I submit a bug request dnoeth?
917 Oct 2010 @ 02:17 PDTTeradata ApplicationsTopicQuestioning the results of a Window Remaining FunctionUsing the T12 express demo DB "retail" I was looking at how the "Window Remaining Function" works. I was using the following query: SELECT deptNo AS Dept ,name AS Emp ,salary AS S...
817 Oct 2010 @ 12:41 PDTTeradata ApplicationsTopicRESET keyword in Teradata 12 CertificationAny ideas why this is going wrong people? The following command in the T12 course notes is causing a syntax error: SELECT Month_Of_The_Year, Sales, SUM( Sales ) OVER ( ORDER BY Month_Of_The_Ye...
717 Oct 2010 @ 12:20 PDTGeneralReplyTeradata Express 12 BTEQWIN .LOGON command errorPerfect as usual Rob. Thanks for your quick responses.
609 Oct 2010 @ 04:02 PDTCloud ComputingReplyTeradata DBMS is in bad state in TD Express 12 images (40GB as 1TB) I started Teradata 12 Express today and all queries fail because an amp is down. vprocmanager "STATUS NOTONLINE" shows VPROC #0 as being in UTILITY mode. I can't seem to reboot it to ONLINE - BOO...
509 Oct 2010 @ 02:08 PDTGeneralReplyTeradata Express 12 BTEQWIN .LOGON command errorFYI, I have amended C:\WINDOWS\system32\drivers\etc\hosts to have the line: 127.0.0.1 localhost localtdcop1 dbc dbccop1 demo1099cop1 and this has been to no effect.

Pages