ansi sql : between syntax

select emp_id from emptable where emp_id BETWEEN 5 and 10;

This will list
5
6
7
8
9
10

because the range on BETWEEN clause is inclusive of the test values on databases using ANSI SQL.

Error SP2-0734 - unknown command beginning

while running a query that ran fine on toad but failed on the server/sqlplus due to above error.

The solution is simple, just remove all blank lines from your script.

To delete blank lines in vi:
%g/^$/d
(i.e. global change, nothing between beginning of line to end of line, delete)

Count Distinct on Multiple Columns

Some days back I figured following is a valid query. It can be used to get count of distinct occurrences of a column.

select count(distinct colname) from tablename;

But it would not take more than one column. To look up distinct records based on more than one column we can use Oracle's concatenate function - "||". Thus all the columns will be treated as a single entity.

select count(distinct colname1 || colname2 || colname3) from tablename;

A good example where we would want to use above query is to get distinct customer orders from a transaction table that contains customer-name,order-date and order-status.

Duel with Dual

Dual is a default table that comes with all Oracle db installations. It is tiny table with just one column and has just one row. See desc below.

desc dual;
DUMMY

select * from dual;
DUMMY
X

It's owner is SYS but all users have access to it. This table always returns one row. So that's something that comes in handy.

Usages:
1. mostly to select pseudo columns from tables or views
2. test any of the Oracle functions

e.g.
select to_date(sysdate) as system_date from dual;
SYSTEM_DATE
6/25/2009

select 'ruchi' as name, 'crazy' as fame from dual;
NAME FAME
ruchi crazy

select upper('ruchi') as ucase from dual;
UCASE
RUCHI

Oracle functions INSTR, SUBSTR, LENGTH, UPPER, LOWER

Usage:
length('snake')
= 5
upper('body')
= BODY
lower('roLLeR')
=roller

subset of a string is substr
substr('thisplace', 5)
= place
substr('thisplace',2,3)
= his

find in a string is instr
instr('thisplace','his')
=2
instr gives this starting position of the searched string in source string.

Database User Access information

To look up what role/access you have in an Oracle database we can use this query -

select * from dba_role_privs where grantee = 'userid';

Table structure of dba_role_privs:

Column Name    ID    Data Type
GRANTEE         1    VARCHAR2 (30 Byte)  
GRANTED_ROLE    2    VARCHAR2 (30 Byte) -- Roles defined specifically for the db
ADMIN_OPTION    3    VARCHAR2 (3 Byte)  
DEFAULT_ROLE    4    VARCHAR2 (3 Byte)  

If there's no entry for the user-id in this table that means user doesn't have access to the database.

sql function : NVL can be used to provide a default value when a field is null.

if salary = Null
then salary = 0
else
salary = emp_salary from emp_table
end if

This can be reduced to
select nvl(emp_salary,0) from emp_table;

---------------------------------------------------------------------------

sql function : DECODE can be used instead of a nested if condition or a case statement.

DECODE(expression or column,if_value,then_return_this_value,[ if_value,then_return_this_value,...],default_return_value)

e.g.
Following sql will assign state codes for Washington or Ohio and default to Florida if the state is neither of the two.
select emp_id, decode(emp_state,'Washington','WA','Ohio','OH','FL')
from emp_table;

Following sql will assign tax percentage as 30 if salary >=60000 else 20.
select emp_id, decode(emp_tax_pct,emp_salary >= 60000,30,20)
from emp_table;

display only desired number (n) of rows in sql result

 

select * from table_name where rownum < n;

 

 

#

to search for a particular table in Oracle database:
select * from dba_objects where object_type = 'TABLE' and object_name like '%THAT_TABLE%';

Add owner name to limit query to a schema name.


to find a particular column by name:
select * from all_tab_columns where column_name like '%COLUMN_NAME%' ;