Ari,

Thanks for the SQL info it worked great. I had another question if you had time.

I want to create a single row table that contains a year. The year value would

be the (SYSDATE + 1) if the SYSDATE month/day is greater then or equal to July

1, else the just (SYSDATE).

example: in if,then,else format

If select to_char(SYSDATE,'MM/DD') >= '07/01' then

    create table FY as (select to_number(to_char(SYSDATE,'YYYY') + 1)

      from dual);

else

    create table FY as (select to_number(to_char(SYSDATE,'YYYY'))

      from dual;

Please let me know if you have enough info for this. I have been trying to get

this to work with decode, but so far no luck.

Best Regards,

Jim Finerfrock

-----  Reply -----

This cannot be done in straight SQL. First, decode will not work because it is a

function, and can return values only. It cannot execute SQL based on values,

especially DDL such as CREATE TABLE. Second, straight SQL cannot do the IF THEN

ELSE structure.

What you will need to do is create a PL/SQL routine. What you are trying to do

is very easy, and you already have the logic set-up. PL/SQL can handle the IF

THEN ELSE logic flow, and so on. If you do not know PL/SQL I recommend getting

Steve Feuerstein's book from O'Reilly and Associates.

One thing in the CREATE TABLE statement is that you should make an alias (such

as COLUMN_A) for the column, shown below:

create table FY as (select to_number(to_char(SYSDATE,'YYYY') "COLUMN_A")

      from dual;

Best regards,

-Ari Kaplan

Independent Oracle DBA

www.arikaplan.com                       

-------------------------------------------------------------------

NOTE: The following was suggested from a browser of this page. His comments

follow...

From: Victor Yu 

To: akaplan@interaccess.com

Subject: From Victor

Hi, Ari,

  This is Victor from Boston, MA. I do DBA works for more than 3

years. I've read all the tips in your website. They are very useful.

  I have a suggestion on question #235. Please look at the 

following SQL:

create table FY as

  (select to_number(to_char(add_months(sysdate,6),'yyyy')) "COL1" from dual)

  Thanks for your attention.

---------------------------------------------------------------------

NOTE: This is my response:

Victor,

Glad to see that you enjoy my web page...so much that you read all of the tips!

Thanks for your comment. I put it on the web page if that's ok. The syntax

of your SQL needs a few small changes. Aside from that it's great!

create table FY as (select to_number(to_char(SYSDATE,'YYYY')) "COLUMN_A"

from dual);

-Ari

_________________________________________________________________________

Note: This is from  Haleem Jamaludeen haleem@haleem.com:

235. Creating a table with a condition that DECODE cannot handle

I want to create a single row table that contains a year. The year value

would be the (SYSDATE + 1) if the SYSDATE month/day is greater then or

equal to July 1, else the just (SYSDATE).

SQL> set echo on;

SQL> sta afiedt.buf

SQL> drop table fy

   2  /

Table dropped.

SQL>  create table FY

   2   as

3            (

4           select decode(

   5                to_char(sysdate,'MM'),

   6                '07',to_number(to_char(SYSDATE,'YYYY') + 1),

   7                '08',to_number(to_char(SYSDATE,'YYYY') + 1),

   8                '09',to_number(to_char(SYSDATE,'YYYY') + 1),

   9                '10',to_number(to_char(SYSDATE,'YYYY') + 1),

  10                '11',to_number(to_char(SYSDATE,'YYYY') + 1),

  11                '12',to_number(to_char(SYSDATE,'YYYY') + 1),

  12                     to_number(to_char(sysdate,'YYYY')    )

  13               )

  14                column_a

  15    from dual

  16   )

  17  /

Table created.

SQL> select * from fy

   2  /

  COLUMN_A

---------

      2001

It works fine.  I'm using 8i.

Thanks,

Haleem.

__________________________________________________________________________

Pankaj adds: One possibility could be: SELECT decode(GREATEST(to_char(sysdate, 'mm/dd'), '07/01'), to_char(sysdate, 'mm/dd'), to_number(to_char(sysdate, 'yyyy'))+1, '07/01', to_char(sysdate, 'yyyy'), 'ERROR') value_returned from dual; Though it says GREATEST, it takes into account the EQUAL TO condition too. Thanks and regards, Pankaj. pankas.priyadarshi@metalspectrum.com

Back to Ari Kaplan's Home Page