Wednesday, July 31, 2013

Oracle Tip: Find Missing Sequence No from a Table

Problem : Finding Missing Sequence No. from a Table

Solution : Using Analytics is the best solution. For this problem I have used LAG()


Steps :

  • Run the statement given below
    • TABLE1 - Table for which you need to find the missing seqno.
    • seqno- Sequence no field


    SELECT last_expected_seq as MissingSeq
    FROM (
      SELECT seqno,
             seqno- 1 as last_expected_seq,
             lag(seqno) over (order by seqno) as last_sequence
      FROM TABLE1
    ) t
    WHERE last_expected_seq <> last_sequence;


  1.                 
read more...

Saturday, July 27, 2013

Oracle Tip : Removing all the duplicates from a table

Problem : Problem of Duplicates in a table

Solution : Using Analytics is the best solution. For this problem I have used ROW_NUMBER()


Steps :

  • Create a backup of the table for which you are removing the duplicates 
  • Identify the no. of duplicates
  • Run the statement given below
    • TABLE1 - replace with your table
    • FIELD1,FIELD2 - Group By fields from your table 


    DELETE FROM TABLE1
  1.       WHERE ROWID IN (
  2.                SELECT row_id
  3.                  FROM (SELECT ROWID row_id,
  4.                               ROW_NUMBER () OVER (PARTITION BY FIELD1,FIELD2 ORDER BY ROWID) ROWNO
  5.                          FROM TABLE1)
  6.                 WHERE ROWNO<> 1);
  7.                 
read more...

Monday, July 22, 2013

Excel Tip : Extract URL's from Hyperlink list

Problem : Extracting URLs from a long list of hyperlinks.

Solution : Using VBA module

read more...