Home » SQL & PL/SQL » SQL & PL/SQL » help to correct syntax error (oracle 12c)
help to correct syntax error [message #646017] Thu, 17 December 2015 22:57 Go to next message
vs140580
Messages: 70
Registered: December 2015
Member
create table newsample  AS 
((select h1.1,h1.2,h1.3,h1.4 from sample1 h1),

(select h1.1,h1.2,h1.4,h1.3 from sample1 h1),

(select h1.1,h1.3,h1.2,h1.4 from sample1 h1),

(select h1.1,h1.3,h1.4,h1.2 from sample1 h1),

(select h1.1,h1.4,h1.2,h1.3 from sample1 h1),

(select h1.1,h1.4,h1.3,h1.2 from sample1 h1),

(select h1.2,h1.1,h1.3,h1.4 from sample1 h1),

(select h1.2,h1.1,h1.4,h1.3 from sample1 h1),

(select h1.2,h1.3,h1.1,h1.4 from sample1 h1),

(select h1.2,h1.3,h1.4,h1.1 from sample1 h1),

(select h1.2,h1.4,h1.1,h1.3 from sample1 h1),

(select h1.2,h1.4,h1.3,h1.1 from sample1 h1),

(select h1.3,h1.1,h1.2,h1.4 from sample1 h1),

(select h1.3,h1.1,h1.4,h1.2 from sample1 h1),

(select h1.3,h1.2,h1.1,h1.4 from sample1 h1),

(select h1.3,h1.2,h1.4,h1.1 from sample1 h1),

(select h1.3,h1.4,h1.1,h1.2 from sample1 h1),

(select h1.3,h1.4,h1.2,h1.1 from sample1 h1),

(select h1.4,h1.1,h1.2,h1.3 from sample1 h1),

(select h1.4,h1.1,h1.3,h1.2 from sample h1),

(select h1.4,h1.2,h1.1,h1.3 from sample1 h1),

(select h1.4,h1.2,h1.3,h1.1 from sample1 h1),

(select h1.4,h1.3,h1.1,h1.2 from sample1 h1),

(select h1.4,h1.3,h1.2,h1.1 from sample1 h1))
                      


Error at Command Line:2 Column:12
Error report:
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:



The table sample1 is below.

 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" ;





[Updated on: Thu, 17 December 2015 23:30]

Report message to a moderator

Re: help to correct syntax error [message #646018 is a reply to message #646017] Fri, 18 December 2015 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Invalid syntax.

However, I have no idea what do you expect the NEWSAMPLE table to look like and what values you want to have in it once it is created.

Moreover, creating your own objects in SYSTEM schema is really, really a bad idea.
Re: help to correct syntax error [message #646021 is a reply to message #646017] Fri, 18 December 2015 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can only give one query.
You have to use UNION ALL between the queries.

So
create table newsample  AS 
(select h1.1,h1.2,h1.3,h1.4 from sample1 h1
union all
...)

Re: help to correct syntax error [message #646022 is a reply to message #646021] Fri, 18 December 2015 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's invalid too.
Re: help to correct syntax error [message #646023 is a reply to message #646018] Fri, 18 December 2015 00:14 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
sorry I have found some errors. I what to permute the rows of the table sample1 and update then table newsample.
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:

I am from math background i am new to sql trying to use for my purpose please pardon this layman

[Updated on: Fri, 18 December 2015 00:15]

Report message to a moderator

Re: help to correct syntax error [message #646024 is a reply to message #646021] Fri, 18 December 2015 00:17 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you sir i have posted my requirment below more after finding some syntax errors i have done please help
Re: help to correct syntax error [message #646025 is a reply to message #646024] Fri, 18 December 2015 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I gave you how to get a syntactically correct statement.

Re: help to correct syntax error [message #646026 is a reply to message #646018] Fri, 18 December 2015 00:19 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you sir I have found some errors but please help now with my new post so that i can correct the syntax further with my requirment to put permutations of each each of the table sample1 and create a table newsample and put it there.
Re: help to correct syntax error [message #646027 is a reply to message #646025] Fri, 18 December 2015 00:22 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you sir I will check and reply back I hope help me save the permuations of each row into the same table thank you very much
Re: help to correct syntax error [message #646029 is a reply to message #646025] Fri, 18 December 2015 00:29 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you Thank you very very much sir
your syntax was perfect
Re: help to correct syntax error [message #646030 is a reply to message #646029] Fri, 18 December 2015 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But if you want all permutations of your columns you can use:
SQL> insert into sample1 values ('1','2','3','4');

1 row created.

SQL> insert into sample1 values ('A','B','C','D');

1 row created.

SQL> commit;

Commit complete.

SQL> create table newsample  AS
  2  with
  3    permut as (
  4      select replace(sys_connect_by_path(i,'*'),'*','') p
  5      from (select level i from dual connect by level <= 4)
  6      where connect_by_isleaf = 1
  7      connect by nocycle prior i != i
  8    )
  9  select decode(substr(p,1,1), '1',E1, '2',E2, '3',E3, '4',E4) c1,
 10         decode(substr(p,2,1), '1',E1, '2',E2, '3',E3, '4',E4) c2,
 11         decode(substr(p,3,1), '1',E1, '2',E2, '3',E3, '4',E4) c3,
 12         decode(substr(p,4,1), '1',E1, '2',E2, '3',E3, '4',E4) c4
 13  from sample1, permut
 14  /

Table created.

SQL> select * from sample1;
E1                   E2                   E3                   E4
-------------------- -------------------- -------------------- --------------------
1                    2                    3                    4
A                    B                    C                    D

2 rows selected.

SQL> select * from newsample order by 1,2,3,4;
C1                   C2                   C3                   C4
-------------------- -------------------- -------------------- --------------------
1                    2                    3                    4
1                    2                    4                    3
1                    3                    2                    4
1                    3                    4                    2
1                    4                    2                    3
1                    4                    3                    2
2                    1                    3                    4
2                    1                    4                    3
2                    3                    1                    4
2                    3                    4                    1
2                    4                    1                    3
2                    4                    3                    1
3                    1                    2                    4
3                    1                    4                    2
3                    2                    1                    4
3                    2                    4                    1
3                    4                    1                    2
3                    4                    2                    1
4                    1                    2                    3
4                    1                    3                    2
4                    2                    1                    3
4                    2                    3                    1
4                    3                    1                    2
4                    3                    2                    1
A                    B                    C                    D
A                    B                    D                    C
A                    C                    B                    D
A                    C                    D                    B
A                    D                    B                    C
A                    D                    C                    B
B                    A                    C                    D
B                    A                    D                    C
B                    C                    A                    D
B                    C                    D                    A
B                    D                    A                    C
B                    D                    C                    A
C                    A                    B                    D
C                    A                    D                    B
C                    B                    A                    D
C                    B                    D                    A
C                    D                    A                    B
C                    D                    B                    A
D                    A                    B                    C
D                    A                    C                    B
D                    B                    A                    C
D                    B                    C                    A
D                    C                    A                    B
D                    C                    B                    A

48 rows selected.

Re: help to correct syntax error [message #646032 is a reply to message #646030] Fri, 18 December 2015 00:56 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you very very much sir.

One small help sir if possible can the same code be rewritten such that instead assuming there 4 variable based son the number of variables of sample1 say it may contain n variables then decode is in a loop or so that it automatically creates for that may variables.

If possible please pardon me. I apologize. Kind help.
Re: help to correct syntax error [message #646033 is a reply to message #646032] Fri, 18 December 2015 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You must know the number of columns if you want to create a table.
If you know the number of columns then you can write the correct query modifying the DECODE.

I don't understand what you mean here by "n variables", all I see are columns of tables.

If your example is not correct then post a new one which represents more correctly your issue.

Re: help to correct syntax error [message #646035 is a reply to message #646033] Fri, 18 December 2015 01:09 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
sir table is already create say sample1 instead of manually counting the columns as 4 system command so that it can return the number of columns of that table

second where i really very very bad at kind help decode written such that if the number of columns is say 'n' in that table that many decodes and inner variables are required to avoid huge manual typing i need help please help i have a data with 12 columns say.

Thank you sir again please trying and help me so time. it will help my career.
Re: help to correct syntax error [message #646036 is a reply to message #646035] Fri, 18 December 2015 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your question is very theoretical as a table can't have a dynamic number of columns and so you write a query from a table you already knows the number of columns and there is nothing dynamic.

If you want something dynamic (like giving the name of table and create a new table with the same rule than above) then you have to write code, PL/SQL or other programming language.

Re: help to correct syntax error [message #646038 is a reply to message #646036] Fri, 18 December 2015 01:19 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
The table is static sir but I can use the use the same code on a different table with different number of columns with changing the code based on the columns of the new table i refer to instead of sample1 it should take the columns of that and do the same so there may be new variables in that tables I always keep the column names as E1,E2,E3,E4,...En in the example only E1,E2,E3,E4 suppose there there n columns keeping this column name syntax only.
Re: help to correct syntax error [message #646039 is a reply to message #646038] Fri, 18 December 2015 01:22 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
n is the number of columns given remaining program adjusts itself instead of huge typing
Re: help to correct syntax error [message #646040 is a reply to message #646039] Fri, 18 December 2015 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, dynamic table and columns => PL/SQL

Re: help to correct syntax error [message #646041 is a reply to message #646040] Fri, 18 December 2015 01:30 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Yes sir that is correct as u say can u help It is too much to ask from my side it is not like a doubt i understand as i dont know PL/SQL if possible u can help me in your free time. I possible I apologize If possible help sir.

I thank you very very very much for spending with me this much of your precious time.

I can write the in C++ to get the code for dynamic but I am not good at PL/SQL if possible u can help
Re: help to correct syntax error [message #646042 is a reply to message #646041] Fri, 18 December 2015 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suggested PL/SQL but you can use the programming language you want.
If you prefer C++ then do it in C++.
Get the table name as input, search the columns in ALL_TAB_COLUMNS, generate the statement I posted above for the given table and execute it.

Re: help to correct syntax error [message #646043 is a reply to message #646042] Fri, 18 December 2015 01:41 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Thank you sir will do I have one more help if possible u can help me can i ask
Re: help to correct syntax error [message #646044 is a reply to message #646043] Fri, 18 December 2015 01:45 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
If there are 12 columns in a table then I have to pick all such 13 rows of the table such the intersection between any two rows in that 13 rows is exactly at one column only and every entry of the columns in the 13 rows occurs only exactly twice. Please help when free sir.
Re: help to correct syntax error [message #646045 is a reply to message #646044] Fri, 18 December 2015 01:46 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
table is like sample1
Re: help to correct syntax error [message #646046 is a reply to message #646044] Fri, 18 December 2015 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry I don't understand what you say and I don't see the relation with the original question.
If it is a new one, create a new topic and post a test case, give the result you want from its data and explain this result.

Kind help sir PL\SQL [message #646048 is a reply to message #646017] Fri, 18 December 2015 02:22 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Kind help to find the intersection of all pairs of rows of table and display only those pairs whose intersection is only at one element.


Here I have a table say with 4 varchar elements for a sample and there two rows say somewhere in the table as


(0,1) (0,2) (0,3) (3,4)
(0,1) (1,3) (1,2) (2,4)

You can treat them as rows even


a b c d
c t y u

Here intersection is one that is `c`.

Here in first the intersection between the above two rows is at `(0,1)` if there are 120 or above tuples or rows how to query all possible pairwise two rows such that they have only one intersection?

Please pardon me if any miscommunication as I am using this for my mathematics work will provide more info if asked any.
[code]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 C
Kind help sir PL\SQL [message #646049 is a reply to message #646048] Fri, 18 December 2015 02:25 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Kind help to find the intersection of all pairs of rows of table and display only those pairs whose intersection is only at one element.


Here I have a table say with 4 varchar elements for a sample and there two rows say somewhere in the table as


(0,1) (0,2) (0,3) (3,4)
(0,1) (1,3) (1,2) (2,4)

You can treat them as rows even


a b c d
c t y u

Here intersection is one that is `c`.

Here in first the intersection between the above two rows is at `(0,1)` if there are 120 or above tuples or rows how to query all possible pairwise two rows such that they have only one intersection?

Please pardon me if any miscommunication as I am using this for my mathematics work will provide more info if asked any.

Toggle Spoiler






ALL have only exactly 4 items as each is not null.

a b c d is just to understand my data set can be attached if requested i treat each as varchar only and just compare.

I have given only some amount of the data as i cannot attach a file i feel all [a,b] are such that a<b so we can compare them as strings itself. tab used to delimit.

E1 E2 E3 E4
[0,2] [1,2] [2,3] [3,4]
[0,2] [1,2] [2,4] [3,4]
[0,3] [1,3] [2,3] [2,4]
[0,3] [1,3] [3,4] [2,4]
[0,4] [1,4] [2,4] [2,3]
[0,4] [1,4] [3,4] [2,3]
[0,1] [1,2] [1,3] [3,4]
[0,1] [1,2] [1,4] [3,4]
[0,3] [2,3] [1,3] [1,4]
[0,3] [2,3] [3,4] [1,4]
[0,4] [2,4] [1,4] [1,3]
[0,4] [2,4] [3,4] [1,3]
[0,1] [1,3] [1,2] [2,4]
[0,1] [1,3] [1,4] [2,4]
[0,2] [2,3] [1,2] [1,4]
[0,2] [2,3] [2,4] [1,4]
[0,4] [3,4] [1,4] [1,2]
[0,4] [3,4] [2,4] [1,2]
[0,1] [1,4] [1,2] [2,3]
[0,1] [1,4] [1,3] [2,3]
[0,2] [2,4] [1,2] [1,3]
[0,2] [2,4] [2,3] [1,3]
[0,3] [3,4] [1,3] [1,2]
[0,3] [3,4] [2,3] [1,2]
[1,2] [0,2] [2,3] [3,4]
[1,2] [0,2] [2,4] [3,4]

In this sample i have 4 varchar in a general i can have say n varchar variables n being known.




A row of columns containing a,f,c,d,e intersects with a row of cells b,x,a,y,z once, on the element a.

**A general form of the question**

N+1 rows of N elements, such than there are N*(N+1) / 2 total elements, each duplicated twice.
Further more, the intersection of any two rows is exactly 1 element.

Example

(0,1), (0,2), (0,3), (3,4)
(0,1), (1,3), (1,2), (2,4)
(0,2), (2,3), (1,2), (1,4)
(1,4), (2,4), (0,4), (0,3)


Each row contains each line of above and each column contains (0,1) next (0,2) etc so there are 4 columns and we have to choose 5 rows with five columns like this u can see the intersection there I feel




Edited to add spoiler tags and merge topics, test case is huge. RC

[Updated on: Fri, 18 December 2015 02:30] by Moderator

Report message to a moderator

Re: help to correct syntax error [message #646050 is a reply to message #646046] Fri, 18 December 2015 02:38 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Respected sir,

I have posted a new post kind help if possible. Thank you sir
Re: help to correct syntax error [message #646051 is a reply to message #646046] Fri, 18 December 2015 03:05 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
I have written the c++ code and got the query u gave from 12 case of similar type just to show u if any advice it is taking time in computation any suggestions



create table cubeodc  AS 
with
permut as (
 select replace(sys_connect_by_path(i,'*'),'*','') p
from (select level i from dual connect by level <= 12)
where connect_by_isleaf = 1
connect by nocycle prior i != i
 )
select  decode(substr(p,1,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c1,
	decode(substr(p,2,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c2,
	decode(substr(p,3,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c3,
	decode(substr(p,4,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c4,
	decode(substr(p,5,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c5,
	decode(substr(p,6,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c6,
	decode(substr(p,7,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c7,
	decode(substr(p,8,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c8,
	decode(substr(p,9,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c9,
	decode(substr(p,10,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c10,
	decode(substr(p,11,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c11,
	decode(substr(p,12,1),'1',E1,'2',E2,'3',E3,'4',E4,'5',E5,'6',E6,'7',E7,'8',E8,'9',E9,'10',E10,'11',E11,'12',E12)c12
from graph, permut
Re: help to correct syntax error [message #646053 is a reply to message #646051] Fri, 18 December 2015 03:57 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
vs140580 wrote on Fri, 18 December 2015 10:05
I have written the c++ code and got the query u gave from 12 case of similar type just to show u if any advice it is taking time in computation any suggestions

I am afraid that whichever tool you will use (C++, Oracle SQL, ...), its performance will depend on the algorithm you will choose. It seems to me that you have no idea of any optimized one instead of brute force.

The initial problem looks like some set theory(?) one with quite big complexity (you once claimed the polynomial one), but it is hard to tell from your posts as they are focused on a little peculiar representation.

Maybe you should take one step back and try to analyze the problem mathematically instead of trying to implement the same method in different tools. However this is out of scope of this forum, maybe you should ask some "research" authorities (as you once recalled them).

Good luck.
Re: help to correct syntax error [message #646054 is a reply to message #646053] Fri, 18 December 2015 04:05 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Yes I am also these huge data are the starter for the big one mathematical
Re: help to correct syntax error [message #646101 is a reply to message #646051] Sat, 19 December 2015 09:56 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Kind help sir with any query for the same above example to remove exact duplicate rows in the table
Re: help to correct syntax error [message #646103 is a reply to message #646101] Sat, 19 December 2015 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+remove+exact+duplicate+rows+in+the+table
Re: help to correct syntax error [message #646104 is a reply to message #646103] Sat, 19 December 2015 10:13 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
I am quite poor in writing query for the above 4 column table only I already searched found so only kind help with a sample query
Re: help to correct syntax error [message #646105 is a reply to message #646104] Sat, 19 December 2015 10:13 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
Difficult for me to understand so only
Re: help to correct syntax error [message #646106 is a reply to message #646105] Sat, 19 December 2015 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post a test case, give the result you want from its data and explain this result.

what constitutes "exact match"?
For this discussion say have identified row X & row Y to be "exact match". What should be done now? Remove X? Remove Y? Remove both?
How to differentiate between X & Y since they are IDENTICAL?
Re: help to correct syntax error [message #646107 is a reply to message #646106] Sat, 19 December 2015 10:50 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
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" ;

Suposse there are two rows say


A B C D

A B C D

Keep only one remove the remaining
Re: help to correct syntax error [message #646109 is a reply to message #646107] Sat, 19 December 2015 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table

NULLS in any column can complicate the logic; since your table allows NULLs.
Re: help to correct syntax error [message #646110 is a reply to message #646109] Sat, 19 December 2015 11:30 Go to previous messageGo to next message
vs140580
Messages: 70
Registered: December 2015
Member
There are no null I the data then i think then select distinct * should work I am running it there are 5 lac rows
Re: help to correct syntax error [message #646111 is a reply to message #646110] Sat, 19 December 2015 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vs140580 wrote on Sat, 19 December 2015 09:30
There are no null I the data then i think then select distinct * should work I am running it there are 5 lac rows


that works for me.
Re: help to correct syntax error [message #646115 is a reply to message #646111] Sat, 19 December 2015 22:23 Go to previous messageGo to previous message
vs140580
Messages: 70
Registered: December 2015
Member
K it worked thanks
Previous Topic: Help with how to do
Next Topic: help with syntax error kind help
Goto Forum:
  


Current Time: Fri Mar 29 03:15:25 CDT 2024