Category Archives: SQL

SQL

Select value from DB table which presents more than one time

duplicates
If you want to see duplicated column values in DB table use next:

 select 
     doc_id,
     count(1)
 from doc_pictures
 group by doc_id
 having count(1) > 1

Thanx to http://stackoverflow.com/a/19635626

How to kill session in Oracle DB

oracle
To kill session in oracle we need:

Step 1: Find SID and SERIAL#:

SELECT SID, SERIAL# FROM v$session WHERE username = 'TEST';

Step 2: Try to disconnect session:

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

Or:

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

If doesn’t help, use next step.

Step 3: Run kill session command:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

If doesn’t help, use next step.

Step 4: Kill system process. Firstly find SPID:

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = XXX;

And then for windows:

orakill ORACLE_SID spid

Or for linux:

kill spid

For more info, read here:
http://oracle-base.com/articles/misc/killing-oracle-sessions.php
http://www.ora00600.com/wordpress/articles/kill-oracle-session/

SQL

Difference between SQL query restriction and join-restriction

For ex. you have simple join (Query 1):

SELECT * FROM table1
LEFT JOIN table2 ON table1.column1=table2.column2

and the other query is (Query 2):

SELECT * FROM table1,table2
WHERE table1.column1=table2.column2

What is the difference?

Query 1 gives you more records than Query 2. Case, in result will present ALL rows from table1 and columns of table2 = NULL
The second query results coinciding rows only.

Connect to DB2 via JDBC in Database Manager

To connect you’d point a URL to databse, a driver and its location.

URL:

  jdbc:db2://somhost.org:50001/SOMEDBNAME

Driver:

  com.ibm.db2.jcc.DB2Driver

Driver location (for Windows OS):

  C:\Program Files\Aqua Data Studio 8.0 - 32bit\lib\drivers\db2jcc.jar

Also, read http://docs.aquafold.com/ads/4.7/docs-jdbcdrivers-db2.html

SQL

Select records in specific order in SQL


In order to obtain result records in spec. order you can apply next query:

string strRowIDs = "NULL";
string strSelectOrder = string.Empty;
if (aRowIDs.Count > 0)
{
	int nCnt = 0;
	strRowIDs = string.Empty;
	strSelectOrder = "select_order = case _id";
	foreach (int i in aRowIDs)
	{
		strSelectOrder += " when " + i.ToString() + " then " + (nCnt++).ToString();
		strRowIDs += "," + i.ToString();
	}
	strSelectOrder += " end";
}
string strWhere = "_id in (" + strRowIDs.Trim(new char[] { ',' }) + ")";

// Get data
/*
select *,strSelectOrder
from XXX
where strWhere 
order by select_order
*/

where aRowIDs- set of IDs
select_order – new order