SQL: Generating Series of Numbers in Oracle

 

Generating a series of numbers is a very common practice in SQL environments. The objectives are but not limited to generating testing data and constants to use in SQL statements. Oracle provides a number of ways to accomplish this but the most simple to me is using the CONNECT BY clause.

   1:  SELECT ROWNUM N FROM dual 
   2:  CONNECT BY LEVEL <= 20

The statement generates integers starting from 1 to 20. It can be altered in many ways according to the acquirements e.g. adding a WHERE clause to offset the series or applying a mathematical equation at N to generate different types of series. For example:

   1:  SELECT (ROWNUM * 0.02 + 3) "N" FROM dual 
   2:  WHERE LEVEL > 10 
   3:  CONNECT BY LEVEL <= 20

Another objective of series is the requirement of constants in the SQL statements. For example I used constants of week days (1-7) and number of hours in a day (1-24) to generate some statistics by hour by day of week.

   1:  SELECT DECODE (DN, 
   2:       2, ‘MON ‘, 
   3:       3, ‘TUE ‘, 
   4:       4, ‘WED ‘, 
   5:       5, ‘THU ‘, 
   6:       6, ‘FRI ‘, 
   7:       7, ‘SAT ‘, 
   8:       1, ‘SUN ‘) "Day of Week", 
   9:  Hour, 
  10:  (SELECT COUNT(*) FROM tblsupport 
  11:   WHERE tktopendate > SYSDATE – 90 
  12:   AND TO_CHAR(tktopendate, ‘D’) = DN 
  13:   AND TO_CHAR(tktopendate, ‘HH24′) = H) "Ticket Count" 
  14:  FROM 
  15:  (SELECT ROWNUM DN FROM dual 
  16:    CONNECT BY LEVEL <= 7) WeekDays, 
  17:  (SELECT ROWNUM Hour FROM dual
  18:   CONNECT BY LEVEL <= 24) Hours

This statement generates statistics data about number of tickets received during the last 3 months grouped by week day and hour.

 

Technorati Tags: Oracle,SQL,Series,Integers,Numbers,Constants,CONNECT BY

 

Share this post :

digg it!

Facebook it!

live it!

reddit!

technorati!

yahoo!

View SQL generated by NHibernate

NHibernate has a built-in mechanism to log all SQL statements generated. It has a configuration parameter “show_sql” which should be set to true. NHibernate will throw all statements to Console.out if this is true. So a console application can easily display all statements but what about the web application?

An easy way is to use the NUnit console which has a separate tab for all Console.out output. If you do not use NUnit work out with log4net.

Creating one-to-one relationship in NHibernate

A one-to-one relationship between two tables can be created by:

• Adding a column to the parent table with some unique ID of the child table
• Without any extra column: just inserting the same PK value in both tables

NHibernate supports the second method (correct me) and you can create this relationship using the “foreign” type ID generator and the <one-to-one> tags in both mappings. Today I created the following mappings for two classes named Document and DocumentStatus.

<class name="Document" table="documents">
    <id name="Id" column="id">
          <generator class="foreign">
               <param name="property">Status</param>
          </generator>
    </id>
    <one-to-one name="Status" class="DocumentStatus" constrained="true"/>
    ... other properties ...
</class>

The trick here is just the foreign ID generator class. Now the mapping for DocumentStatus would be fairly simple.

<class name="DocumentStatus" table="documentstatus">
    <id name="Id" column="id">
       ... any generator class ...
    </id>
    <one-to-one name="Document" class="Document"/>
    ... other properties ...
</class>

The classes will have a property of the corresponding type to map this relationship so you can navigate both ways.

class Document {
... other stuff ...
DocumentStatus Status;
}

class DocumentStatus {
... other stuff ...
Document Document;
}

Whenver you insert a new object in Document, the corresponding DocumentStatus object will be created and its PK will be assigned to Document object as well.

Great Stuff!