Home » SQL & PL/SQL » SQL & PL/SQL » CREATE TABLE: tablename with data from subselect (variable) (Oracle 10g)
CREATE TABLE: tablename with data from subselect (variable) [message #594758] Mon, 02 September 2013 09:22 Go to next message
oranooob
Messages: 88
Registered: May 2009
Member
Hi

I wish to create a table with weeknumber as suffix _36 or _30

CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);           


$WEEKNUMBER is this statement:

SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL


CREATE TABLE doesn't accept subselect Mad

Howto do that with SQL?
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594759 is a reply to message #594758] Mon, 02 September 2013 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
With pure sql you can't - have to use dynamic sql via pl/sql.
However are you sure you want to create 52 separate tables here? One table is likely to be simpler and more efficient.
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594760 is a reply to message #594758] Mon, 02 September 2013 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
It can be done, but it should NOT be done.

data should be normalized to Third Normal Form which you proposed 'design" does not do.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594761 is a reply to message #594758] Mon, 02 September 2013 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') WEEKNUMBER from DUAL;
SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') WEEKNUMBER from DUAL
                        *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Query is wrong.
Don't you think that TO_DATE on a date (SYSDATE) is silly?

Regards
Michel
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594762 is a reply to message #594758] Mon, 02 September 2013 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> set define '$'
SQL> col WEEKNUMBER new_value WEEKNUMBER
SQL> SELECT TO_CHAR (SYSDATE, 'IW') WEEKNUMBER from DUAL;
WE
--
36

1 row selected.

SQL> create table test1 (test integer);

Table created.

SQL> CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);

Table created.

SQL> desc TEST1_36
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ------------
 TEST                                                                       NUMBER(38

Regards
Michel
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594763 is a reply to message #594762] Mon, 02 September 2013 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
>Howto do that with SQL?
How do you write code against table which has not yet been created?
How do you test code against table which has not yet been created?
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594786 is a reply to message #594760] Tue, 03 September 2013 02:23 Go to previous messageGo to next message
oranooob
Messages: 88
Registered: May 2009
Member
BlackSwan wrote on Mon, 02 September 2013 09:34
It can be done, but it should NOT be done.

data should be normalized to Third Normal Form which you proposed 'design" does not do.


Howto do better?

Every week I import with SQL*Loader a *csv into this table

> cat loader.ctl
load data
 infile '/opt/data/tools/jdbc_interface/server.csv'
 REPLACE
 into table test1
 fields terminated by ";" optionally enclosed by '"'
 ( OS_FQDN, OS_OS, OS_OSVersion, OS_Virtual, OS_IP, HDW_NodeName, HDW_Location, HDW_Producer, ALIAS_FQDN, HDW_Model, ALIAS_IP, ALIAS_NetworkAddressType, HWLifecycle, Maintainer)



CREATE TABLE STOCK.TEST1
(
  OS_FQDN                   VARCHAR2(120 BYTE),
  OS_OS                     VARCHAR2(60 BYTE),
  OS_OSVERSION              VARCHAR2(60 BYTE),
  OS_VIRTUAL                VARCHAR2(60 BYTE),
  OS_IP                     VARCHAR2(60 BYTE),
  HDW_NODENAME              VARCHAR2(60 BYTE),
  HDW_LOCATION              VARCHAR2(60 BYTE),
  HDW_PRODUCER              VARCHAR2(60 BYTE),
  HDW_MODEL                 VARCHAR2(60 BYTE),
  ALIAS_FQDN                VARCHAR2(60 BYTE),
  ALIAS_IP                  VARCHAR2(60 BYTE),
  ALIAS_NETWORKADDRESSTYPE  VARCHAR2(60 BYTE),
  HWLIFECYCLE               VARCHAR2(60 BYTE),
  MAINTAINER                VARCHAR2(60 BYTE),
  IMPORTED                  DATE                DEFAULT sysdate
)


I wish to see/query old data (one year back). What's best practise?

@Michel
Yes TO_DATE on SYSDATE is silly. Sorry.
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594787 is a reply to message #594786] Tue, 03 September 2013 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Every week I import with SQL*Loader a *csv into this table (...) I wish to see/query old data (one year back)

Instead of REPLACE, use APPEND in control file. Doing so, you'd keep all imported data in the same table, and you'd be able to query it (by the IMPORTED column, as it contains date value, so there's no problem to select any date you want).
Re: CREATE TABLE: tablename with data from subselect (variable) [message #594838 is a reply to message #594787] Tue, 03 September 2013 07:48 Go to previous message
oranooob
Messages: 88
Registered: May 2009
Member
thanks, bro. Nice hint Littlefoot.
Previous Topic: Simple example of (not) using an index
Next Topic: Need help to generate Procedure Execution LOG
Goto Forum:
  


Current Time: Fri Nov 28 03:06:12 CST 2014

Total time taken to generate the page: 2.12425 seconds