2

Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table.

create table newsample  AS 
(
select(
(select h1.E1,h1.E2,h1.E3,h1.E4 from sample1 h1),
(select h1.E1,h1.E2,h1.E4,h1.E3 from sample1 h1),
(select h1.E1,h1.E3,h1.E2,h1.E4 from sample1 h1),
(select h1.E1,h1.E3,h1.E4,h1.E2 from sample1 h1),
(select h1.E1,h1.E4,h1.E2,h1.E3 from sample1 h1),
(select h1.E1,h1.E4,h1.E3,h1.E2 from sample1 h1),
(select h1.E2,h1.E1,h1.E3,h1.E4 from sample1 h1),
(select h1.E2,h1.E1,h1.E4,h1.E3 from sample1 h1),
(select h1.E2,h1.E3,h1.E1,h1.E4 from sample1 h1),
(select h1.E2,h1.E3,h1.E4,h1.E1 from sample1 h1),
(select h1.E2,h1.E4,h1.E1,h1.E3 from sample1 h1),
(select h1.E2,h1.E4,h1.E3,h1.E1 from sample1 h1),
(select h1.E3,h1.E1,h1.E2,h1.E4 from sample1 h1),
(select h1.E3,h1.E1,h1.E4,h1.E2 from sample1 h1),
(select h1.E3,h1.E2,h1.E1,h1.E4 from sample1 h1),
(select h1.E3,h1.E2,h1.E4,h1.E1 from sample1 h1),
(select h1.E3,h1.E4,h1.E1,h1.E2 from sample1 h1),
(select h1.E3,h1.E4,h1.E2,h1.E1 from sample1 h1),
(select h1.E4,h1.E1,h1.E2,h1.E3 from sample1 h1),
(select h1.E4,h1.E1,h1.E3,h1.E2 from sample h1),
(select h1.E4,h1.E2,h1.E1,h1.E3 from sample1 h1),
(select h1.E4,h1.E2,h1.E3,h1.E1 from sample1 h1),
(select h1.E4,h1.E3,h1.E1,h1.E2 from sample1 h1),
(select h1.E4,h1.E3,h1.E2,h1.E1 from sample1 h1))) 

Error at Command Line:4 Column:49 Error report: SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

CREATE TABLE "SYSTEM"."SAMPLE1" 
       (    "E1" VARCHAR2(20 BYTE), 
      "E2" VARCHAR2(20 BYTE), 
      "E3" VARCHAR2(20 BYTE),
      "E4" VARCHAR2(20 BYTE)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM" ;

closed as off-topic by mustaccio, Marcello Miorelli, Lennart, MDCCL, McNets Jul 16 at 10:36

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marcello Miorelli, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What exactly are you trying to do? How many columns do you expect the result to have? It looks like you just want create table newsample AS select h1.E1,h1.E2,h1.E3,h1.E4 from sample1 h1 union all select h1.E1,h1.E2,h1.E4,h1.E3 from sample1 h1 union all ... (no parentheses needed) – a_horse_with_no_name Dec 18 '15 at 6:50
  • Yes is got it but need it dynamic minimum 12 columns computation then goes slow any help – sriram Dec 18 '15 at 9:37
  • It's just worth mentioning that it is not a good idea to be creating objects in the SYSTEM schema. Best bet is to create a user schema and use that instead. – BriteSponge Dec 18 '15 at 10:01
  • 100% sir as I am new to it I had done it will it have impact on the speed of execution if I create separate schema I have faster speed – sriram Dec 18 '15 at 10:10
  • It shouldn't make any difference to speed but is bad practice. A user schema is the place for your user work. Let Oracle use the SYS and SYSTEM schema and tablespaces just for itself (and 3rd party applications sometimes). If you are logged on as SYSTEM you can accidentally break stuff that could prove difficult to fix, especially if you are new to Oracle. – BriteSponge Dec 18 '15 at 10:23
0

You can get the result by using UNION ALL . Take help of following example for understanding:

create table empnew as select * from employees union all select * from employees;

As @a_horse_with_no_name perfectly mentioned !

  • How to make it dynamic and more fast computional any new sql query – sriram Dec 18 '15 at 10:13
  • To make it dynamic you can use substitution variables "&" – Paresh Zawar Dec 18 '15 at 10:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.