sql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

sql

puppyoo
i need to write a basic SQL, taking user parameter (financial year) in the form of e.g. 2006/2007 and from that I need to extract the data for last year 2005/2006. the 2006/2007 comes from a column in the form or string varchar2. how can I get previous years from that entry

select year from year
where year = 'year - 1'

select year from year
where year = '2006/2007 - 1'
is this correct
Reply | Threaded
Open this post in threaded view
|

Re: sql

Jim Melton-2

At 8/6/2007 12:29 AM, puppyoo wrote:

>i need to write a basic SQL, taking user parameter (financial year) in the
>form of e.g. 2006/2007 and from that I need to extract the data for last
>year 2005/2006. the 2006/2007 comes from a column in the form or string
>varchar2. how can I get previous years from that entry
>
>select year from year
>where year = 'year - 1'
>
>select year from year
>where year = '2006/2007 - 1'
>is this correct

Sorry, but that's not the right approach.  What you've written says
"select the year column from all rows in the year table for which the
year column has the following character string value" and then you
give the letter 'y' followed by the letter 'e' followed by the letter
'a' followed by...  In the second example, you're asking for rows in
which the year column contains the digit '2' followed by the digit
'0' followed by...followed by a space followed by a hyphen followed
by a space followed by the digit '1'.

This ought to do the job:

select year
from year
where cast(substring(year,1,4) as integer)
     = cast(substring(:formcol,1,4) as integer)-1
   and
       substring(year,5,1) = '/'
   and cast(substring(year,6,4) as integer)
     = cast(substring(:formcol,6,4) as integer)-1

In that code, ":formcol" is the syntax for referencing the host
parameter provided in an EXEC SQL statement.

Hope this helps,
    Jim

========================================================================
Jim Melton --- Editor of ISO/IEC 9075-* (SQL)     Phone: +1.801.942.0144
   Co-Chair, W3C XML Query WG; F&O (etc.) editor    Fax : +1.801.942.3345
Oracle Corporation        Oracle Email: jim dot melton at oracle dot com
1930 Viscounti Drive      Standards email: jim dot melton at acm dot org
Sandy, UT 84093-1063 USA          Personal email: jim at melton dot name
========================================================================
=  Facts are facts.   But any opinions expressed are the opinions      =
=  only of myself and may or may not reflect the opinions of anybody   =
=  else with whom I may or may not have discussed the issues at hand.  =
========================================================================