ch20.htm

Learn SQL in an easy way...

This table is composed of one column called <TT>DUMMY</TT> whose only row of data
is the value <TT>X</TT>. The <TT>DUAL</TT> table is available to all database users
and can be used for general purposes, such as performing arithmetic (where it can
serve as a calculator) or manipulating the format of the <TT>SYSDATE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> desc dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------- -------- ----
 DUMMY                                    VARCHAR2(1)</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">D
-
X
</FONT></PRE>
<P>Take a look at a couple of examples using the <TT>DUAL</TT> table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
--------
08-MAY-97</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select 2 * 2</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">      2*2
 --------
        4
</FONT></PRE>
<P>Pretty simple. The first statement selected <TT>SYSDATE</TT> from the <TT>DUAL</TT>
table and got today's date. The second example shows how to multiply in the <TT>DUAL</TT>
table. Our answer for <TT>2 * 2</TT> is <TT>4</TT>.
<H2><FONT COLOR="#000077">The DECODE Function</FONT></H2>
<P>The <TT>DECODE</TT> function is one of the most powerful commands in SQL*Plus--and
perhaps the most powerful. The standard language of SQL lacks procedural functions
that are contained in languages such as COBOL and C.</P>
<P>The <TT>DECODE</TT> statement is similar to an <TT>IF...THEN</TT> statement in
a procedural programming language. Where flexibility is required for complex reporting
needs, <TT>DECODE</TT> is often able to fill the gap between SQL and the functions
of a procedural language.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DECODE(column1, value1, output1, value2, output2, output3)
</FONT></PRE>
<P>The syntax example performs the <TT>DECODE</TT> function on <TT>column1</TT>.
If <TT>column1</TT> has a value of <TT>value1</TT>, then display <TT>output1</TT>
instead of the column's current value. If <TT>column1</TT> has a value of <TT>value2</TT>,
then display <TT>output2</TT> instead of the column's current value. If <TT>column1</TT>
has a value of anything other than <TT>value1</TT> or <TT>value2</TT>, then display
<TT>output3</TT> instead of the column's current value.</P>
<P>How about some examples? First, perform a simple select on a new table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select * from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ST
--
IN
FL
KY
IL
OH
CA
NY

7 rows selected.
</FONT></PRE>
<P>Now use the <TT>DECODE</TT> command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select decode(state,'IN','INDIANA','OTHER') state</B>
  2  <B>from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">STATE
------
INDIANA
OTHER
OTHER
OTHER
OTHER
OTHER
OTHER

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Only one row met the condition where the value of state was <TT>IN</TT>, so only
that one row was displayed as <TT>INDIANA</TT>. The other states took the default
and therefore were displayed as <TT>OTHER</TT>.</P>
<P>The next example provides output strings for each value in the table. Just in
case your table has states that are not in your <TT>DECODE</TT> list, you should
still enter a default value of <TT>'OTHER'</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select decode(state,'IN','INDIANA',</B>
  2                    <B>  'FL','FLORIDA',</B>
  3                     <B> 'KY','KENTUCKY',</B>
  4                    <B>  'IL','ILLINOIS',</B>
  5                  <B>    'OH','OHIO',</B>
  6                   <B>   'CA','CALIFORNIA',</B>
  7                  <B>    'NY','NEW YORK','OTHER')</B>
  8 <B> from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">DECODE(STATE)
----------
INDIANA
FLORIDA
KENTUCKY
ILLINOIS
OHIO
CALIFORNIA
NEW YORK

7 rows selected.
</FONT></PRE>
<P>That was too easy. The next example introduces the <TT>PAY</TT> table. This table
shows more of the power that is contained within <TT>DECODE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; col hour_rate hea &quot;HOURLY|RATE&quot; for 99.00
SQL&gt; col date_last_raise hea &quot;LAST|RAISE&quot;
SQL&gt; select name, hour_rate, date_last_raise
  2  from pay;</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                     HOURLY LAST
NAME                   RATE RAISE
-------------------- ------ --------
JOHN                  12.60 01-JAN-96
JEFF                   8.50 17-MAR-97
RON                    9.35 01-OCT-96
RYAN                   7.00 15-MAY-96
BRYAN                 11.00 01-JUN-96
MARY                  17.50 01-JAN-96
ELAINE                14.20 01-FEB-97

7 rows selected.
</FONT></PRE>
<P>Are you ready? It is time to give every individual in the <TT>PAY</TT> table a
pay raise. If the year of an individual's last raise is 1996, calculate a 10 percent
raise. If the year of the individual's last raise is 1997, calculate a 20 percent
raise. In addition, display the percent raise for each individual in either situation.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col new_pay hea 'NEW PAY' for 99.00</B>
SQL&gt; <B>col hour_rate hea 'HOURLY|RATE' for 99.00</B>
SQL&gt; <B>col date_last_raise hea 'LAST|RAISE'</B>
SQL&gt; <B>select name, hour_rate, date_last_raise,</B>
  2       <B>  decode(substr(date_last_raise,8,2),'96',hour_rate * 1.2,</B>
  3                                           <B> '97',hour_rate * 1.1) new_pay,</B>
  4        <B> decode(substr(date_last_raise,8,2),'96','20%',</B>
  5                                           <B> '97','10%',null) increase</B>
  6  <B>from pay;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                     HOURLY LAST
NAME                   RATE RAISE     NEW PAY INC
-------------------- ------ --------- ------- ---
JOHN                  12.60 01-JAN-96   15.12 20%
JEFF                   8.50 17-MAR-97    9.35 10%
RON                    9.35 01-OCT-96   11.22 20%
RYAN                   7.00 15-MAY-96    8.40 20%
BRYAN                 11.00 01-JUN-96   13.20 20%
MARY                  17.50 01-JAN-96   21.00 20%
ELAINE                14.20 01-FEB-97   15.62 10%

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>According to the output, everyone will be receiving a 20 percent pay increase
except Jeff and Elaine, who have already received one raise this year.
<H2><FONT COLOR="#000077">DATE Conversions</FONT></H2>
<P>If you want to add a touch of class to the way dates are displayed, then you can
use the <TT>TO_CHAR</TT> function to change the &quot;date picture.&quot; This example
starts by obtaining today's date:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
--------
08-MAY-97
</FONT></PRE>
<P>When converting a date to a character string, you use the <TT>TO_CHAR</TT> function
with the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">TO_CHAR(sysdate,'date picture')
</FONT></PRE>
<P><TT>date picture</TT> is how you want the date to look. Some of the most common
parts of the date picture are as follows: <TT>Month</TT> The current month spelled
out.</P>
<P>
<TABLE BORDER="1">
	<TR>
		<TD><TT>Mon</TT></TD>
		<TD>The current month abbreviated.</TD>
	</TR>
	<TR>
		<TD><TT>Day</TT></TD>
		<TD>The current day of the week.</TD>
	</TR>
	<TR>
		<TD><TT>mm</TT></TD>
		<TD>The number of the current month.</TD>
	</TR>
	<TR>
		<TD><TT>yy</TT></TD>
		<TD>The last two numbers of the current year.</TD>
	</TR>
	<TR>
		<TD><TT>dd</TT></TD>
		<TD>The current day of the month.</TD>
	</TR>
	<TR>
		<TD><TT>yyyy</TT></TD>
		<TD>The current year.</TD>
	</TR>
	<TR>
		<TD><TT>ddd</TT></TD>
		<TD>The current day of the year since January 1.</TD>
	</TR>
	<TR>
		<TD><TT>hh</TT></TD>
		<TD>The current hour of the day.</TD>
	</TR>
	<TR>
		<TD><TT>mi</TT></TD>
		<TD>The current minute of the hour.</TD>
	</TR>
	<TR>
		<TD><TT>ss</TT></TD>
		<TD>The current seconds of the minute.</TD>
	</TR>
	<TR>
		<TD><TT>a.m.</TT></TD>
		<TD>Displays a.m. or p.m.</TD>
	</TR>
</TABLE>
</P>
<P>The date picture may also contain commas and literal strings as long as the string
is enclosed by double quotation marks <TT>&quot;&quot;</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col today for a20</B>
SQL&gt;<B> select to_char(sysdate,'Mon dd, yyyy') today</B>
  2 <B> from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TODAY
--------------------
May 08, 1997
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice how we used the <TT>COLUMN</TT> command on the alias <TT>today</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col today hea 'TODAYs JULIAN DATE' for a20</B>
SQL&gt; <B>select to_char(sysdate,'ddd') today</B>
  2 <B> from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TODAYs JULIAN DATE
--------------------
128
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Some companies prefer to express the Julian date with the two-digit year preceding
the three-digit day. Your date picture could also look like this: <TT>'yyddd'</TT>.</P>
<P>Assume that you wrote a little script and saved it as <TT>day</TT>. The next example
gets the file, looks at it, and executes it to retrieve various pieces of converted
date information.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>get day</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">line 10 truncated.
  1  set echo on
  2  col day for a10
  3  col today for a25
  4  col year for a25
  5  col time for a15
  6  select to_char(sysdate,'Day') day,
  7         to_char(sysdate,'Mon dd, yyyy') today,
  8         to_char(sysdate,'Year') year,
  9         to_char(sysdate,'hh:mi:ss a.m.') time
 10* from dual
</FONT></PRE>
<P>Now you can run the script:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>@day</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; set echo on
SQL&gt; col day for a10
SQL&gt; col today for a25
SQL&gt; col year for a25
SQL&gt; col time for a15
SQL&gt; select to_char(sysdate,'Day') day,
  2         to_char(sysdate,'Mon dd, yyyy') today,
  3         to_char(sysdate,'Year') year,
  4         to_char(sysdate,'hh:mi:ss a.m.') time
  5  from dual;

DAY        TODAY                    YEAR                    TIME
---------- ------------------------ ----------------------- ------------
Thursday   May 08, 1997              Nineteen Ninety-Seven    04:10:43 p.m.
</F