#DateForumTypeThreadPost
3126 Apr 2013 @ 04:50 PDTDatabaseReplyFind the value of the latest dateThen you can go with Rank() or Row_number() functions to achieve this. Regards, Mohan K
3020 Apr 2013 @ 12:54 PDTDatabaseReply3939 there is a mismatch between the number of parameters specified and the number of parameters requiredhi, I just tried it in the normal SQL, Hope this helps to you! sel a, rank(a) from (sel (add_months(dob,-24) (format 'yyyymm'))(varchar(6)) a from tb_name where dob between add_mont...
2929 Mar 2013 @ 03:10 PDTDatabaseReplyChanging Phone Number FormatTry with the below,   sel * from mkformat_1 A join mkformat_2 B on (cast(A.mtn as bigint) (format '999-999-9999')) (varchar(20)) = B.mtn Regards, Mohan
2821 Mar 2013 @ 02:40 PDTDatabaseReplyUpdate statement using self joinsel id,effdate, max(post_eff_date) over (partition by id, effdate) as postdate from tblname;
2721 Mar 2013 @ 02:15 PDTDatabaseReplyhow know the table size ?sel * from dbc.tablesize where tablename='employee' and databasename='xxxx'
2622 Feb 2013 @ 06:02 PSTDatabaseReplyexact table cloneif i read you correctly, hope you are looking as like below, please let know if otherwise ct new_tablename as exissting_tblname with data and stats unique primary index(alt_id); Regards, Mohan K...
2520 Feb 2013 @ 02:42 PSTDatabaseReplyAdd comment on a COLUMN or TABLEif you just need it in the query then use double hypen to put comments ct dummy( int a --value of a ,int b -- value of b ) But make sure, the comma has to be on the next line :-). . Regards, ...
2420 Feb 2013 @ 02:22 PSTDatabaseReplyConcatenate o/p of two select statementsTo add, the query posted by Dieter is a scalar sub query which will work with TD 13 onwards.. Hence I am not sure about whether this will get product join or not..  
2320 Feb 2013 @ 02:19 PSTDatabaseReplyConcatenate o/p of two select statementsHi Teradatauser2, All the queries which is posted here will get product join except the query which is posted from Mathuram especially when you are performing with more than one table process. if...
2219 Feb 2013 @ 09:35 PSTDatabaseReplyselect FirstRespTime ,* from DBC.DBQLogTblyes we can in TD also, select FirstRespTime, DBQLogTBL.* from DBQLogTbl Regards, Mohan K    
2119 Feb 2013 @ 03:58 PSTDatabaseReplyHow to transpose rows to columns without the use of .sql and BteqsHi Sachin, I tried execute your query with the below inputs it seems you picked the max value for particular Alt_id_type.   Here for 1234, i have just changed C_id instead of D_id and it se...
2018 Feb 2013 @ 10:37 PSTDatabaseReplyFind new tables created in last monthHi , As per my knowledge once we dropped existing table, it will automatically removes all the information from the data dictionary. So once we dropped and recreated the table with the same name ...
1918 Feb 2013 @ 03:53 PSTDatabaseReplyNeed to calculate using LEAD, LAG functionHi Arbiswas, Yes this won't  match with your result set which given in the initial post because of the same date is having multiple status(2/1/2009). However executing the query from my e...
1817 Feb 2013 @ 04:04 PSTDatabaseReplyNeed to calculate using LEAD, LAG functionHere it is.. sel a.empid as a1, min(b.effdate) as leave_start_date, a.effdate as leave_end_date from     (sel empid,effdate,          coa...
1702 Jan 2013 @ 11:08 PSTDatabaseReplyChecking sequence of numbers and populate a flag if number is out of sequenceThe below query will work with duplicates along with null values if we have it in File number too.. sel    Serial_Number,    Send_Date ,    Count(*)  as Fil...
1621 Dec 2012 @ 10:40 PSTDatabaseReplyTransposed multiple rows into one column and one row I hope TD can support upto 64000 characters to store in a column. in such a case if we implement recursive process then there should be rework in future since we do not know the exact no of rows...
1519 Dec 2012 @ 10:44 PSTGeneralReplyViewing results of WITH clause in Teradata SQL AssistantHi Dieter,   Could you please give me some more notes about "Standard SQL WITH (i.e. at the beginnig of your select) you should see the result"?   Thanks in advance, Mohan ...
1411 Dec 2012 @ 11:44 PSTDatabaseReplyConvert integer to TIME@ Harpreet,   I have tried the above query with the value of 0145 and getting the error as "2620 The format of data contains a bad character"   @aarsh,   Could you pl...
1311 Dec 2012 @ 02:12 PSTGeneralReplyNeed help in returning two adjacent rows as one rowIn another 2 method,   1. select c1,min(c1)over(order by c1 rows 1 following and 1 following) as c2 from table qualify c2 is not null;   2. select c1,coalesce(min(c1)over(order by c1 ...
1204 Dec 2012 @ 11:14 PSTDatabaseReplyCheck row is Numeric or nottry with reursive, hope it can help you   with recursive r2(id, st, L, c, rstr) as ( sel id,str as st, char(st) as L, 1 as c, trim(case when position(substr(st,1,1) in 'abcdefghijklm...
1104 Dec 2012 @ 10:43 PSTDatabaseReplyCompare strings in bteq statement, not in sqltry with char(Employee_Name)...
1011 Nov 2012 @ 05:49 PSTDatabaseReplyFilter consecutive records that have same value as preceding recordHere it is... sel csum(1,c1) ,con_rec.* ,min(c2) over (rows between 1 preceding and 1 preceding) as a , min(c3) over (rows between 1 preceding and 1 preceding) as b ,case when c2=a and c3=b t...
910 Nov 2012 @ 09:19 PSTDatabaseReplyTrimming Inner SpacesHere is with recursive function.... Just change this 1234567890 as what we need to replace in a string with recursive r2(id, st, L, c, rstr) as ( sel id,str as st, char(st) as L, 1 as c, trim...
810 Nov 2012 @ 06:51 PSTDatabaseReplyLeft outer join with a NULL valueHere you go... sel la.*, lb.* from la left join lb on (coalesce(la.a,'(novalue)')=coalesce(lb.a,'(novalue)'))
709 Nov 2012 @ 03:04 PSTDatabaseReplySelect Tables created in a previous months onlyHere it is.. SELECT databasename as DBNAME, tablename as TBLNAME, CAST(createtimestamp AS DATE FORMAT 'YYYY-MM-DD')(CHAR(10)) as CREATE_DATE       &nbs...

Pages