Sunday, April 02, 2006

Logical Operators & Single-row functions

Comparison operators:

WHERE column_name='string';


=

>

>=

<

<=

<>

Other Comparison operators:


BETWEEN ... AND ....

IN(set)

LIKE

IS NULL

WHERE Salary BETWEEN 2500 AND 3500;

WHERE cust_id IN(101, 102, 105);

WHERE job LIKE '_sal%';

_ : single character

% : zero or more

WHERE mgr_id IS NULL;

Escape Characters to search wildcard characters : WHERE job_id LIKE '%S\_%' ESCAPE '\';

Logical Conditions :


AND

OR

NOT

WHERE job LIKE '_sal%'
AND mgr_id IS NULL

WHERE job LIKE '_sal%'
OR mgr_id IS NULL;

WHERE cust_id NOT IN(101, 102, 105);

Rules of Precedence:

Order Evaluated

Operator

1

Arithmatic operators

2

Concatenation operator

3

Comparison conditions

4

IS [NOT] NULL, LIKE, [NOT] IN

5

[NOT] BETWEEN

6

NOT logical condition

7

AND logical condition

8

OR logical condition

Single Row Functions

Character Functions:

Number functions:

Date functions:

    • MONTH_BETWEEN(date1, date2)

:

(+ve if date1>date2)

    • ADD_MONTHS(date,n)

:

N= number of calender months

    • NEXT_DAY(date,'char')

:

'char' : a number representing the day of week, or a char string.

    • LAST_DAY(date)

:

Last date of the month containing date

    • ROUND(date[,fmt])

:

If fmt omitted, date rounded to nearest day

    • TRUNC(date[,fmt])

:

If fmt omitted, date truncated to nearest day

Date arithmatic :

date+no_of_days=date
date-no_of_days=date
date-date=no of days

SYSDATE : system date

Implicit Data Type Conversion :

For assignments:

For expression evaluation:

VARCHAR2 OR CHAR-> NUMBER
VARCHAR2 OR CHAR -> DATE
NUMBER -> VARCHAR2
DATE -> VARCHAR2

VARCHAR2 OR CHAR-> NUMBER
VARCHAR2 OR CHAR -> DATE

Explicit Data Type Conversion :

TO_CHAR(number|date,[fmt],[nlsparams])

:

Convert a number of date value to VARCHAR2 as per fmt.

nlsparams specifies decimal character, group separater, local currency symbol & international currency symbol.

If nlsparams or any other parameter is omitted, default session parameter values are used.

TO_DATE(char,[fmt],[nlsparams])

:

If fmt is omitted DD-MON-YY is used.

Date format Model :

    • YYYY

:

Full year in numbers

    • YEAR

:

Year spelled out

    • MM

:

Two digit value of month

    • MONTH

:

Full name of the month

    • MON

:

Three letter abbreviation of month

    • DY

:

Three letter abbreviation of day-of-week

    • DAY

:

Full name of week

    • DD

:

Numeric day of month

Other formats & number displays :

    • / . ,

    • of the”

    • TH

    • SP

    • SPTH or THSP

:

:

:

:

:

Punctuation reproduced in the result

quoted string reproduced in the result

Ordinal Number (eg. DDTH for 4TH)

spelled out number (eg. DDSP for FOUR)

spelled out ordinal number (eg. DDSPTH for FOURTH)

Eg. TO_CHAR(SYSDATE, 'fmDdspth “of” Month YYYY fmHH:MI:SS AM')

Number format Model :


Element

Desc

Example

Result

9

Numeric position

999999

1234

0

Leading zeros

099999

001234

$

Floating dollar sign

$999999

$1234

L

Floating local currency symbol

L999999

FF1234

.

Decimal point in position specified

999999.99

1234.00

,

Comma in position specified

999,999

1,234

MI

Minus sign to right (-ve values)

999999MI

1234-

PR

Parenthesize -ve numbers

999999PR

<1234>

EEEE

Scientific notation

99.999EEEE

1.234E+03

V

Multiply by 10 n times (n=no. of 9s after V)

9999V99

123400

B

Zero value as blank, no zero.

B9999.99

1234.00

Eg. :

TO_CHAR(salary, '$99,999.99')
TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');

Note : “fx” modifier specifies exact match. Any extra space will cause the conversion to fail. Without “fx” Oracle ignores extra blanks.

General Functions

NVL (expr1, expr2)

:

Converts a Null value to an actual value

example:

NVL(commission_pct,0);
NVL(hire_date,'02-FEB-98');
NVL(job_id,'Job not allocated');

NVL conversion:

NUMBER

:

NVL(number_col,9)

DATE

:

NVL(date_col,'02-FEB-98')

CHAR OR VARCHAR2

:

NVL(char_col,'string')

NVL2 (expr1, expr2, expr3)



If expr1 <> NULL returns expr2
if expr1 = NULL returns expr3

example:

NVL2(commission_pct,'SAL+COMM','SAL');

Note :

  • expr1 can have any data type.

  • expr2,expr3 can have any data type except LONG.

  • if data types of expr2 & expr3 are different, expr3 is converted to expr2 data type before comparison.

  • Return data type is that of expr2.

  • If expr2 is CHAR, then VARCHAR2 is returned.

NULLIF (expr1, expr2)

:

If expr1=expr2 , returns NULL

else return expr1

COALESCE (expr1, expr2,..., exprn)

:

Returns the 1st non-null expression in the expression list.

Example:

COALESCE (commission_pct, salary,0);

Conditional Expressions:

CASE expr WHEN comparison_expr1 THEN return_expr1


[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr

END

Example:

SELECT last_name, job_id, salary,


CASE job_id WHEN 'SAL_CLK' THEN salary * 1.10


WHEN 'MKT_MGR' THEN salary * 1.25
WHEN 'HRO_EXC' THEN salary * 1.20
ELSE salary

END “REVISED_SALARY

FROM employees;

DECODE (col|exprn, search1, result1,


[search2, result2,....]
[, default]

Example:

SELECT last_name, job_id, salary,


DECODE (job_id, 'SAL_CLK', salary * 1.10,


'MKT_MGR', salary * 1.25, 'HRO_EXC',salary * 1.20,
salary) REVISED_SALARY

FROM employees;


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google