SQL Reviewer

Posted on Tuesday, 8th December 2009 by kwatog

Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here’s how it is being used.

Syntax #1


CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END


Example


SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;


Syntax #2


CASE
WHEN [ condition_1 ] THEN result_1
WHEN [ condition_2 ] THEN result_2
...
WHEN [ condition_n ] THEN result_n
ELSE result
END


Example


SELECT m.paper_type ,
m.paper_brand ,
m.paper_grammage,
m.paper_width ,
m.paper_length ,
m.paper_stock_no,
(
CASE
WHEN NVL(smc.qty_reserved_onhand,0) != NVL(st.sum_qty_r_oh,0) THEN 'QROH NOT TALLY'
WHEN NVL(smc.qty_reserved_incom,0) != NVL(st.sum_qty_r_incom,0) THEN 'QRINC NOT TALLY'
WHEN NVL(smc.ton_reserved_onhand,0) != NVL(st.sum_ton_r_oh,0) THEN 'TROH NOT TALLY'
WHEN NVL(smc.ton_reserved_incom,0) != NVL(st.sum_ton_r_incom,0) THEN 'TRINC NOT TALLY'
END
) ERROR_MESSAGE
from paper_stock_master m


Personally, I use Syntax #2 most of the time. I don’t know why but maybe it just happened that I find it more suitable to use in the situations that I needed the CASE statement.

Difference Between DECODE and CASE WHEN

The most fundamental difference between the two oracle statements is that in DECODE, you are comparing a field or value in a given set of values or fields. It’s actually a shorthand for IF..ELSIF..ELSE statement with the condition all set to equals (=). CASE WHEN is almost the same but you can use any conditional operator (i.e, =,!=, >, >= , <, <=, etc) thereby giving you more freedom and flexibility in writing your code.

In may instances CASE WHEN helped me reduce the number of lines of my codes as well as optimize the performance. On top of that, I no longer need a full pl/sql program with multiple queries and even cursors to be able to provide the result that a simple CASE statement can provide. However CASE, like any other sql commands, is only applicable for certain scenarios. It’s not a magic keyword that will solve/apply to everything. So you have to weigh your options if you need it or not.

Posted in Intermediate, Oracle, Oracle PL\SQL Functions | Comments (0)

Posted on Thursday, 17th September 2009 by kwatog

I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows:


SELECT *
FROM product_component_version



SELECT *
FROM v$version


This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.

Posted in Basic, Oracle | Comments (0)

Posted on Sunday, 6th September 2009 by captainmi2

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.

Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER.

How to declare variable in Oracle PL\SQL

syntax:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

see example below:


declare
blood_type CHAR := 'O';
acct_id INTEGER(4) NOT NULL := 9999;
result varchar2(100);
created_date date;
n_days CONSTANT NUMBER := 200;

begin
........
end;


You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Posted in Basic, Oracle | Comments (0)

Posted on Sunday, 6th September 2009 by captainmi2

RAISE_APPLICATION_ERROR is used to create a user defined error messages.

The user defined messages are associated with the user defined number (SQLCODE) using RAISE_APPLICATION_ERROR.


DECLARE
a NUMBER := 0;
b NUMBER := 10;
BEGIN
IF (a = 0) THEN
RAISE_APPLICATION_ERROR(-20000,' Cannot Divide by Zero');
ELSE
b:= b/a;
dbms_output.put_line(to_char(b));
END IF;
END;


this will output

ORA-20000: Cannot Divide by Zero

Tags: Oracle Raise_Application_Error
Posted in Intermediate, Oracle | Comments (0)

Posted on Sunday, 6th September 2009 by captainmi2

How to use null in PL\SQL

below are example:

declare
var_a varchar2(10) := null;
begin
if var_a = null then
dbms_output.put_line('empty');
else
dbms_output.put_line(' not empty');
end if;
end;


the above statement will output
“not empty” because the null is not use accordingly.

if the statement were use as below


declare
var_a varchar2(10) := null;
begin
if var_a is null then
dbms_output.put_line('empty');
else
dbms_output.put_line('not empty');
end if;
end;


this will return “empty”

This 2nd statement is the correct way to use the null in the PL\SQL script

Using the NULL in the Oralce SQL statement

Supposed you have 4 records in the employee table. One record does not have salary value.

to get the records from employee without salary value, NULL must be use in where clause


select * from employee
where salary is null


Tags: Oracle PL\SQL Null
Posted in Basic, Oracle | Comments (0)

Posted on Wednesday, 2nd September 2009 by kwatog

SYNTAX

alter table
table_name
rename to
new_table_name;


Example

alter table
employee
rename to
employee_bak;


Tags: Oracle, rename table
Posted in Basic, Oracle | Comments (0)

Posted on Thursday, 6th August 2009 by captainmi2

The lower function in Oracle PL\SQ converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

The syntax for the lower function is:

lower( str )

str is the string to convert to lowercase.

see example below.


SQL> select lower('AbCD56efGHI45') col1 from dual;

SQL>


Tags: Oracle PL\SQL statement for Lower Function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)

Posted on Thursday, 6th August 2009 by captainmi2

The upper function in Oracle PL\SQL converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

The syntax for the upper function is:

upper(str)

str is the string to convert to uppercase.

see example below.


SQL> select upper('abcd2efgh3') col1 from dual;

SQL>


Tags: Oracle PL\SQL statement for upper function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)

Posted on Saturday, 20th June 2009 by captainmi2

For example:

Table A1 has 1000 records.

In SQL:

SQL> select count(1) as count_rec from A1;

SQL> truncate table A1;

After running the statement run again the count statement

SQL> select count(1) as count_rec from A1;

count_rec
--------
0


Tags: Oracle SQL statement for truncating the table
Posted in Basic, Oracle | Comments (0)

Posted on Saturday, 20th June 2009 by captainmi2

Oracle SQL statement for counting the records in a table

For example:

Table A1 has 1000 records.

In SQL:

SQL> select count(1) as count_rec from A1;

count_rec
--------
1000


Tags: Oracle SQL statement for counting the records in a table
Posted in Basic, Oracle | Comments (0)