About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Monday, September 12, 2011

Thinking about using rownum while using it in TOP N Query with ORDER BY Clause: BEAWARE Series-II


First :

SELECT ename, sal FROM
(SELECT ename, sal FROM emp ORDER BY sal DESC)

WHERE rownum <=3;


ENAME SAL
---------- ---------
KING 5000
SCOTT 3000

FORD 3000

3 rows selected

 
Second:

SELECT ename, sal FROM emp
WHERE rownum <=3
ORDER BY sal DESC




ENAME SAL
---------- ----------------------
ALLEN 1600

WARD 1250
SMITH 800

The first will return desired result however, second does not give us the result we want
The reason is described below:

Because Oracle assigns the ROWNUM values to the rows before it does the sort.
In this example, Oracle will retrieve three rows from the table, any three rows, and sort only these three rows. We really need Oracle to sort all the rows and then return the first three. The inline view will ensure that this will happen


Post Reference: Vikram Aristocratic Elfin Share

The Help21X-Men: First Class (+Digital Copy) [Blu-ray]A Time to Heal (Quilts of Lancaster County)

Thinking about using rownum in where clause: BEAWARE Series-I


First :

SELECT rnum, table_name FROM
(SELECT rownum rnum, table_name FROM user_tables)
WHERE rnum > 2;

Second:

SELECT table_name FROM user_tables
WHERE rownum > 2;

The first will return result whereas the second will zero rows.


The reason is described below:
However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behavior, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.
The bottom line is that conditions such as the following will work as expected.
WHERE rownum = 1;
WHERE rownum <= 10;
WHERE rownum <=3;
While queries with these conditions will always return zero rows.

WHERE rownum = 2;
WHERE rownum > 10;



Post Reference: Vikram Aristocratic Elfin Share

The simplest way to export data from dataset to Excel

Step 1. Create a dynamic datagrid.
Step 2: Set the datasource of datagrid to dataset.
Step 3: Render the datagrid to Excel sheet.

This Button click event will establish connection with oracle and fill the dataset with the data from the acc_system_moniter table.

protected void btnExport_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection();
string strCon = "Data Source=CONF_INS;
User Id=INS;Password=INS555;Integrated Security=no;";
con.ConnectionString = strCon;

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "select * from acc_system_monitor";
cmd.CommandType = CommandType.Text;

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
vikramWB(ds);
}


This function will create a dynamic dataGrid and bind the datagrid with the datset which was passed as an argument to this function. Then it render the dataGrid to the HtmlTextWriter

public static void vikramWB(DataSet ds)
{
System.Web.UI.WebControls.DataGrid grid =
new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = ds;
grid.DataMember = ds.Tables[0].TableName;

grid.DataBind();

//StreamWriter will write the excel file
using (StreamWriter sw = new StreamWriter("c:\\test.xls"))
{
//HtmlTextWriter constructer will
//take StringWriter as an argument.
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
//The Grid RenderControl will render
// the grid to the HtmlTextWriter
grid.RenderControl(hw);
}
}
}

Post Reference: Vikram Aristocratic Elfin Share