Recent content by dnoeth

Hi mikin, there's a column dbc.tables.CreateTimestamp. If you recreate the views in that order this should be safe. Dieter

Hi Stephanie, .IMPORT INFILE Untitled AXSMOD Oledb_Axsmod 'noprompt' LAYOUT Layout1 APPLY LabelA WHERE ICA_ID = 101; Btw, there's a forum for Teradata at Tek-Tips http://www.tek-tips.com/threadminder.cfm?pid=328 and there's *the* best source of...

Hi randyvol, "the modify user statements appear to have removed the journals" No, they didn't. There was an error, because MODIFY USER is DDL and you can't submit DDL within a multistatement, just read the error message :-) And even if it worked, the journal would still be there; "modify user...

Hi zanzemaj, "diagnostic helpstats on for session" is a valid SQL statement, so just submit it. Some remarks: Replace UNION with UNION ALL Try to replace the UNION Derived Tables with a single access to those 4 tables, the main difference is just this year/last year. Btw, "total estimated time...

Hi zanzemaj, it looks like there are some statistics missing, because there are lots of "no confidence". And V2R6.1 is quite sensible for missing stats... First try a "diagnostic helpstats on for session" and check explain for recommended stats... And better show the query itself and the DDL...

Hi notadba, "a 'H' event will not return since they do not have a corresponding 'T' row" Of course it will, that's why i used an Outer Join :-) Dieter

select a.id,a.effdt,a.e_status,a.act, b.effdt,b.e_status,b.act from ( select id,effdt,e_status,act, (select min(effdt) from x_ps_job a2 where a2.act = 'T' and a2.effdt > a.effdt) as nextdt from x_ps_job a where a.emplid = '1234567' and a.act in...

SQL Server 2000: select RECORDID, SERVICE, TRANSACTION from tab join (select SERVICE, max(TRANSACTION) as maxtran from tab group by SERVICE ) dt on tab.SERVICE = dt.SERVICE and tab.TRANSACTION = dt.maxtran SQL Server 2005 using SQL:1999 OLAP functions: select...

If you're using Oracle then you have to get rid of the "AS" before an alias. But instead of doing some complicated query just use the existing OLAP-functions: select ... count(*) over (partition by tab1.col1) from tab1 join tab2 on tab1.col1 = tab2.col2 Dieter

If your system runs in ANSI mode it should be quite similar to DB2. Check the manuals, especially the ANSI session stuff in: SQL Reference, Statement and Transaction Processin Chapter 7: Locking and Transaction Processing If your system runs in Teradata mode, then it's totally different... Dieter

The SQL statement is not echoed to the file, you probably mean the column header. To get rid of it use (TITLE '') .export report file = bla; select col1 || '|' || col2 || ',' || ... (TITLE '') from tab; Dieter

Using SQL:1999 or SQL:2003 UPDATE TABLE_A A SET Issue_Flag = '1' WHERE EXISTS ( SELECT * FROM TABLE_B B WHERE B.Account_Id = A.Account_Id ) AND 1 = ( SELECT ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Sub_Account_ID DESC...

This should be close: SELECT ... CASE WHEN uga.ugid IS NOT NULL THEN 'Y' ELSE 'N' END, CASE WHEN ugl.ugid IS NOT NULL THEN 'Y' ELSE 'N' END FROM ug LEFT JOIN u ON ... (LEFT?) JOIN s ON ... LEFT JOIN uga ON ug.ugid = ugl.ugid AND u.uid = uga.uid LEFT JOIN ugl ON ug.ugid = ugl.ugid AND...

select * from tab qualify percent_rank() over (order by sales desc) <= 0.2 Easy to enhance with PARTITION BY, e.g. percent_rank() over (order by sales desc partition by year) <= 0.2 to get top 20% per year... Dieter

Another way using SQL:1999 OLAP functions: select id, gid, max(mcrtdate) over (partition by gid) from tbl1 Dieter