Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. dnoeth

    Create view without checking correctness

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

    Placing a WHERE clause in Teradata OLE DB MultiLoad

    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...
  3. dnoeth

    difficulty dropping user

    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...
  4. dnoeth

    Performance problem on Teradata SQL query

    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...
  5. dnoeth

    Performance problem on Teradata SQL query

    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...
  6. dnoeth

    Multiple Rows to Single Row

    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
  7. dnoeth

    Multiple Rows to Single Row

    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...
  8. dnoeth

    Query for max value, multiple sets?

    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...
  9. dnoeth

    aggregate combined with nonaggregate

    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
  10. dnoeth

    Difference between Teradata and Ansi Commit

    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
  11. dnoeth

    How to suppress SQL in BTEQ export

    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
  12. dnoeth

    Update query problem

    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...
  13. dnoeth

    SQL join

    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...
  14. dnoeth

    Top 20 %

    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
  15. dnoeth

    Query where one record's data is used as an expression

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

    decode, grouping in sql question

    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...
  17. dnoeth

    Row-wise Comparision For All Columns?

    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(*)...
  18. dnoeth

    Rounding to nearest whole number

    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...
  19. dnoeth

    Compare Two Dates In Different Tables

    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
  20. dnoeth

    Compare Two Dates In Different Tables

    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...

Part and Inventory Search

Back
Top