help in correcting the syntax error of the below create table with as with multiple select
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" ;
oracle ddl
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 2 more comments
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" ;
oracle ddl
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
What exactly are you trying to do? How many columns do you expect the result to have? It looks like you just wantcreate 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
|
show 2 more comments
2
2
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" ;
oracle ddl
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
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" ;
oracle ddl
oracle ddl
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
edited Dec 18 '15 at 6:47
a_horse_with_no_name
40.7k777113
40.7k777113
asked Dec 18 '15 at 5:13
sriramsriram
146
asked Dec 18 '15 at 5:13
sriramsriram
146
asked Dec 18 '15 at 5:13
sriramsriram
146
146
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
What exactly are you trying to do? How many columns do you expect the result to have? It looks like you just wantcreate 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
|
show 2 more comments
1
What exactly are you trying to do? How many columns do you expect the result to have? It looks like you just wantcreate 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
1
1
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
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
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
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
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
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
|
show 2 more comments
1 Answer
1
active
oldest
votes
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 !
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f124028%2fhelp-in-correcting-the-syntax-error-of-the-below-create-table-with-as-with-multi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 !
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
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
add a comment |
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 !
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
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
add a comment |
0
0
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 !
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
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 !
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
answered Dec 18 '15 at 9:39
Paresh ZawarParesh Zawar
18138
18138
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
add a comment |
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
How to make it dynamic and more fast computional any new sql query
– sriram
Dec 18 '15 at 10:13
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
To make it dynamic you can use substitution variables "&"
– Paresh Zawar
Dec 18 '15 at 10:30
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f124028%2fhelp-in-correcting-the-syntax-error-of-the-below-create-table-with-as-with-multi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
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