Search results for query: *

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

...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 information concerning Teradata: www.teradataforum.com Dieter

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

You filter audit dep within the decode, so just remove that. But why do you use ROLLUP and then filter for unwanted groups instead of a simple: group by grouping sets((r.status_code,r.status_desc,r.AUDIT_DEPT_ID) ,()) Btw, this looks like Oracle code, i'd recommend replacing...

Rowwise comparison is done using union/intersect/except. select * from oldtable except --or minus select * from newview; select * from newview except --or minus select * from oldtable; If both result set are empty then both selects return exactly the same data. Another way: select count(*)...

Be careful, if there are negative values (wrong result) or values outside of the range of an integer (error). The default rounding in Teradata (and most programming languages) is banker's rounding, but you can switch to your desired behaviour (commercial rounding?), there's a global flag...

Of course there's Standard SQL for date/time calculation, e.g. (date1 - date2) day Even Oracle supports a part of it (although Oracle's date is a timestamp), IIRC this should be valid: (date1 - date2) day to second Dieter

Hi Michael42, just use two Derived Tables (Oracle calls it Inline Views) to retrieve the dates: select [your calculation involving date1, date2] from (select date1 from tab1 ....) dt1, (select date2 from tab2 ....) dt2 Of course this assumes that there's only one date from each table. If...