Re: sql

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.  =
========================================================================

Received on Monday, 6 August 2007 16:50:37 UTC