RSS

Category Archives: SQL

Copy rows in the same table with modified data in Oracle

To copy data, modify column values and insert it into the same table we’ll have to modify the primary key and unique columns. Here are 3 different approaches:

The 1st approach:

BEGIN

  FOR r IN (SELECT * FROM MyTable WHERE Column2 = 'Value2') 
  LOOP
    r.Table_ID := PK_Seq.nextval;
    r.Column2  := 'NewValue2';
    r.Column3  := 'NewValue3';
    INSERT INTO MyTable VALUES r;
  END LOOP;

END;

Read the rest of this entry »

Advertisements
 
Leave a comment

Posted by on August 8, 2012 in Database, Oracle, SQL

 

Select the first row of each group

This example demonstrates how to select the first records grouping by Salesman and Store.

Sales:

ID Store Salesman Amount
1 A Lin 120.00
2 A Lin 80.50
3 A Fields 200.00
4 B Lin 160.50
5 B Lin 70.00
6 A Fields 130.00
7 B Fields 180.00
8 A Fields 110.50

The result should be like this:

Result:

ID Store Salesman Amount
3 A Fields 200.00
7 B Fields 180.00
1 A Lin 120.00
4 B Lin 160.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

ORA-01790 when Union Null and Date

In Oracle 8i or earlierUnion Null and Date Type column will cause ORA-01790: expression must have same datatype as corresponding expression.

-- This query will fail
SELECT PayDate FROM Table1
UNION
SELECT '' FROM Table2

The solution is using To_Date(NULL)instead of Null.

SELECT PayDate FROM Table1
UNION
SELECT To_Date(NULL) FROM Table2

Reference:NULL in union causing ORA-01790

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

Query using Oracle Parameter returns no result

I had a parameterized query with some string variables binding to the OracleParameters. The query works with certain parameters but doesn’t with others. Take a simplified database table with only 2 columns for example:

SampleTable:

Name Type
Currency Char(3)
SerialNo Char(8)

My original query looks something like this. The first query works. The second runs normally without error message but returns no result when there are records matched in the database.

Dim currency As string = "USD"
Dim serialNo As string = "1311B"

'Quering with cmd1 works
Dim cmd1 As New OracleCommand("SELECT * FROM SampleTable WHERE Currency = :Currency")
cmd1.Parameters.Add(New OracleParameter("Currency", currency))

'Quering with cmd2 works but no record is returned when there are matching records in the database
Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = :SerialNo")
cmd2.Parameters.Add(New OracleParameter("SerialNo", serialNo))

Read the rest of this entry »

 
Leave a comment

Posted by on July 23, 2012 in .NET, ADO.NET, Database, Oracle, SQL

 

Select values calculated between rows

The example here uses 2 different approaches one using JOIN and another using LAG analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.

Sales:

ID Store SaleMonth Amount
1 A Jan 120.00
2 A Feb 80.50
3 A Feb 200.00
4 B Jan 160.50
5 B Feb 70.00
6 A Feb 130.00
7 B Mar 180.00
8 A Mar 110.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 2, 2012 in Database, Oracle, SQL

 

Using Oracle SQL LIKE operator in parameterized OracleCommand

The SQL LIKE operator can be used in the CommandText with Oracle parameters. The syntax of comparing a OracleParameter using SQL LIKE operator is as follows:

Dim cmd As New OracleCommand
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE :param || '%'"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param || '%'"

Reference:Usage of Oracle binding variables with LIKE in C#

 
Leave a comment

Posted by on June 29, 2012 in .NET, ADO.NET, Oracle, SQL, VB

 

A workaround to use SQL IN clause in TableAdapters

This article is a simple implementation on how to use parameterized SQL query with IN clause or unknown number of parameters in TableAdapter.

When modifying those ancient database access codes, SQL query strings appear in the form of concatenated string very often. These codes are hard to read, maintain and prone to SQL Injection. Using DataSet Designer can easily improve it by accessing database with parameterized commands. But DataSet Designer does not support automatically generating commands with parameterized IN clause.

The idea of this article is originated from Extending TableAdapters for Dynamic SQL on The Code Project. The article demonstrate a way to change the command text programmatically by extending the TableAdapter which is defined as a partial class from the DataSet Designer. What’s not covered is how to parameterized the command.
Read the rest of this entry »

 
Leave a comment

Posted by on May 22, 2012 in .NET, ADO.NET, Oracle, SQL, VB

 

Tags: , , , , , ,