Help - Search - Members - Calendar
Full Version: Create Table As Select with Variable
Oracle DBA Forums > Oracle > Oracle Forum
bobsmall
Hi Guys,

I am trying to create a procedure as follows (a parallel example):

Declare
dateid number;
ssdate date := date '2008-05-31';
Begin
select ID into dateid from date_table where date_column = to_char(ssdate, 'yyyy-mm-dd');
IF dateid != 0
then
begin execute immediate
'Create table new_table
as
select '||ssdate||' as snapshotdate,
col_2,
col_3
from details_table
where col_date_id ='||dateid;
end;
END IF;
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

Problem: Error on line 'select '||ssdate||' as snapshotdate' ->ORA-00904: "MAY": invalid identifier

This was changed to 'select '||to_char(ssdate, 'yyyy-mm-dd')||' as snapshotdate' and the table was created, however, the snapshotdate column was populated with 1972 (the arithmetic evaluation of '2008-05-31')


I want the snapshotdate column to be populated for all records returned with the value of the ssdate variable in a date format. Help please....
aussie_dba
"This was changed to 'select '||to_char(ssdate, 'yyyy-mm-dd')||' as snapshotdate' and the table was created, however, the snapshotdate column was populated with 1972 (the arithmetic evaluation of '2008-05-31')"

You defined it as a char, that makes sense.

You want it to be a date datatype?

Try:


CODE
'select '||to_date(to_char(ssdate, 'yyyy-mm-dd'))||' as snapshotdate'
bobsmall
You want it to be a date datatype?

Try:
CODE
'select '||to_date(to_char(ssdate, 'yyyy-mm-dd'))||' as snapshotdate'

[/quote]



Thanks for the quick response.

Yes I want the column to contain date values, I tried the code you suggested and got the following error:

ORA-01861: literal does not match format string

I'll try google searching this error, but any further suggestions will be appreciated
SteveC
bobsmall -

You do see the typo and can deduce that it should be sysdate?
aussie_dba
Jeex . . . typo:

CODE
'select '||to_date(to_char([SYSDATE, 'yyyy-mm-dd'))||' as snapshotdate'
bobsmall
CODE
'select '||to_date(to_char([SYSDATE, 'yyyy-mm-dd'))||' as snapshotdate'




Hi Guys,

I don't want the field to be populated with the SYSDATE, ssdate is a variable I created where the user can change this value. Refer to the 3rd line in my code:

ssdate date := date '2008-05-31';


Basically, I want this value (and not the system date) to be populated in a snapshot field column for all records returned.
SteveC
Oops, sorry about that.

Your declaration is incorrect.

ssdate date := date '2008-05-31';

Should be

ssdate date := '2008-05-31';

If you enter date in that format, you may need to do more such as setting the format mask or altering the nls parameters for the session.
bobsmall
Should be

ssdate date := '2008-05-31';

If you enter date in that format, you may need to do more such as setting the format mask or altering the nls parameters for the session.


Thanks for your response, but i get the following when i make that change:

ORA-01861: literal does not match format string

based on this error, it indicates that the problem is in the format so i modified the line to read as follows:

ssdate date := '31-May-2008';

when this is done, the error came on the line

SELECT '||to_date(to_char(ssdate, 'yyyy-mm-dd'))||' AS snapshotdate,

which read ORA-01861: literal does not match format string

being the same error i modified the select to read:

SELECT '||to_date(to_char(ssdate, 'dd-mon-yyyy'))||' AS snapshotdate,

now i recieved this error:

ORA-00904: "MAY": invalid identifier

So it seems that instead of populating the snapshotdate field with the value its checking the table in the SELECT (details_table) for a field with MAY in it and trying to mathematically evaluate 31-May-2008 which is not what i want...

How do i specify that this snapshotdate column is of type char or varchar and that the value should remain as is?
burleson
>> based on this error, it indicates that the problem is in the format

Yes, like Steve says, make your character data match the value of your nls_format!

I'm not a coder, but shouldn't your ssdate be specified as a variable (:ssdate)?

CODE
SELECT '||to_date(to_char(:ssdate, 'yyyy-mm-dd'))||' AS snapshotdate,
bobsmall
I'm not a coder, but shouldn't your ssdate be specified as a variable (:ssdate)?

CODE
SELECT '||to_date(to_char(:ssdate, 'yyyy-mm-dd'))||' AS snapshotdate,



I included the colon (smile.gif before the ssdate variable, this prompted me for a value (although i would have liked it to retain the value that was assigned), i entered the date and received similar errors as before such as:

ORA-00904: "MAY": invalid identifier

As much as I would like to figure this out, based on my initial code, it should be clear as to what i am trying to do (if not I will be happy to clarify), maybe the entire logic can be reconstructed to give the same output, any suggestions???
SteveC
1. If you are asking for code help, post the details of what the tables contain.

2. Learn how to troubleshoot your code - use output to test values so you can see what Oracle is trying to work with.

3. Learn how to program in general. What is the rollback for?

4. How many times do you expect this to run? You can only create the table once.

CODE
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> create table date_table
  2  (id number, date_column date);

Table created.

SQL> insert into date_table values (1,to_date('2008-05-31','yyyy-mm-dd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from date_table;

        ID DATE_COLU
---------- ---------
         1 31-MAY-08

SQL> create table details_table
  2  (ssdate date, col2 number, col3 number, col_date_id number);

Table created.

SQL> insert into details_table values ('31-MAY-08', 1, 2, 1);

1 row created.

SQL> commit;

Commit complete.


With some output - here you can see what you are dealing with:

CODE
SQL>
SQL> set serveroutput on
SQL> declare
  2    dateid number;
  3    ssdate date := to_date('2008-05-31','yyyy-mm-dd');
  4    v_stmt varchar2(1000);
  5  begin
  6    --test the date string
  7    dbms_output.put_line('ssdate is '||ssdate);
  8    select id into dateid from date_table where date_column = ssdate;
  9    if dateid != 0 then
10      v_stmt := 'create table new_table as  
11                 select '||ssdate||' as snapshotdate, col2, col3
12                 from details_table
13                 where col_date_id ='||dateid;
14     dbms_output.put_line(v_stmt);
15    end if;
16  exception
17    when others then
18      dbms_output.put_line(sqlerrm);
19  end;
20  /
ssdate is 31-MAY-08
create table new_table as
               select 31-MAY-08 as snapshotdate, col2, col3
               from
details_table
               where col_date_id =1

PL/SQL procedure successfully completed.
bobsmall
ssdate is 31-MAY-08
create table new_table as
select 31-MAY-08 as snapshotdate, col2, col3
from
details_table
where col_date_id =1

PL/SQL procedure successfully completed.[/code]



Thanks SteveC, but based on the output:

from the section of code that was outputted (select 31-MAY-08 as snapshotdate, col2, col3...) wouldn't the sql look for a column 31-MAY-08 in details_table?

I needed to get it to output something like:

select '31-MAY-08' as snapshotdate, col2, col3....


I eventually got the solution, the code was modified as follows:

SELECT '||''''||ssdate||''''||' AS snapshotdate,.....'

that is four single quotes before and after the ||ssdate||. This allowed the value 31-MAY-08 to remain static in all records returned.


Thakns guys for all your help! biggrin.gif
SteveC
Which proves the value of outputting statements - see what the statement looks like to Oracle. If you don't like using the extra single quotes, you can also concatenate chr(39), FYI.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.