OraFAQ Forum: SQL & PL/SQL » Help any one with a code please

Home » SQL & PL/SQL » SQL & PL/SQL » Help any one with a code please (oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help any one with a code please [message #645753]

Mon, 14 December 2015 09:24

vs140580
Messages: 70
Registered: December 2015

Member

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

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

I here by upload a csv sample file from which we have to pick all possible types of 5 rows with the above conditions as there are 4 columns in this sample set. In the csv TAB is my delimiter as , is used within brackets.

[MERGED by LF]

Attachment: sample.csv
(Size: 2.94KB, Downloaded 1363 times)

[Updated on: Tue, 15 December 2015 00:10] by Moderator

Report message to a moderator

Re: Help any one with a code please [message #645754 is a reply to message #645753]

Mon, 14 December 2015 09:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

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

How to use {code} tags and make your code easier to read

BTW - We don't do homework

Report message to a moderator

Re: Help any one with a code please [message #645755 is a reply to message #645754]

Mon, 14 December 2015 09:38

vs140580
Messages: 70
Registered: December 2015

Member

Kind help sir actually i am doing my research in mathematics and i am trying methods to get something related done without getting into mathematical aspects of the question posed i have asked for help to write a procedure as i am not sql programmer but i can use it. please help me if possible.

Report message to a moderator

Re: Help any one with a code please [message #645756 is a reply to message #645755]

Mon, 14 December 2015 09:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> i am not sql programmer
Then why are you trying to solve mathematical problem using SQL?

How are we to know what the correct result should be?

Report message to a moderator

Re: Help any one with a code please [message #645758 is a reply to message #645756]

Mon, 14 December 2015 10:02

vs140580
Messages: 70
Registered: December 2015

Member

I have given only the part of the requirement i want through the program please help i have explained it in detail what my requirement is from the procedure with a sample data to test. later i can use it to analyze bigger work from that. things are explained please help if possible

Report message to a moderator

Re: Help any one with a code please [message #645759 is a reply to message #645758]

Mon, 14 December 2015 10:05

vs140580
Messages: 70
Registered: December 2015

Member

TO be straight i would in the world of research all are interdependent and each helps the other without any basis as we do research together in the world

Report message to a moderator

Help with a command [message #645769 is a reply to message #645753]

Mon, 14 December 2015 21:53

vs140580
Messages: 70
Registered: December 2015

Member

I have table gr with 12 columns say El to El2 as names of columns I would like to inner join gr with itself when the first column values are same but all other value column's different any easy way

Select * from graph h1 inner join graph h2 on he.e1=h2.e1 and (I have to give all remaining columns are not equal )

IN SHORT THE TWO TUPLES should INTERSECT AT ONLY ONE COLUMN

Report message to a moderator

Re: Help with a command [message #645771 is a reply to message #645769]

Mon, 14 December 2015 22:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We need CREATE TABLE statements.
We need INSERT statements.

We need expected & desired results in both actual results & explanation why these results should be produced.

Report message to a moderator

Re: Help with a command [message #645772 is a reply to message #645771]

Mon, 14 December 2015 22:22

vs140580
Messages: 70
Registered: December 2015

Member

I have created the table without statement using interactive screen available I have imported data I just require that the find possible pairs or tuples with intersection at only one field say example if the four fields all varchar and one row has data a,b,c,d and the other a,f,h,u,I then they intersection between the two rows is only at a. I need to find all possible such pairs with only one element intersection in a set of data rows 120 rows or even more

Report message to a moderator

Re: Help with a command [message #645773 is a reply to message #645772]

Mon, 14 December 2015 22:24

vs140580
Messages: 70
Registered: December 2015

Member

Usage of these results are not for others or explainable it has application in AI etc

Report message to a moderator

Re: Help with a command [message #645774 is a reply to message #645772]

Mon, 14 December 2015 22:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We don't have your tables.
We don't have your data.

We can't write SQL when we have no table or data.
We can't write SQL when we don't know what expected results should be.

You can either help us to help you by providing us with needed details or You're On Your Own (YOYO)!

Report message to a moderator

Re: Help with a command [message #645775 is a reply to message #645774]

Mon, 14 December 2015 22:29

vs140580
Messages: 70
Registered: December 2015

Member

In my previous message yesterday I sent u a data csv with tab delimited that can be used as data u can use any table name u wish

Report message to a moderator

Re: Help with a command [message #645776 is a reply to message #645775]

Mon, 14 December 2015 22:35

vs140580
Messages: 70
Registered: December 2015

Member

I apologise for such please guide me will provide more info whatever I can understand

Report message to a moderator

Re: Help with a command [message #645777 is a reply to message #645775]

Mon, 14 December 2015 22:38

vs140580
Messages: 70
Registered: December 2015

Member

vs140580 wrote on Mon, 14 December 2015 22:29

In my previous message yesterday I sent u a data csv with tab delimited that can be used as data u can use any table name u wish

Report message to a moderator

Re: Help with a command [message #645778 is a reply to message #645777]

Mon, 14 December 2015 22:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Is this thread just a continuation of the thread you started yesterday?

Report message to a moderator

Re: Help with a command [message #645779 is a reply to message #645778]

Mon, 14 December 2015 22:53

vs140580
Messages: 70
Registered: December 2015

Member

It is just a small part of that I did started yesterday so that if I get small helps I will try as I am not sql background I am trying my best please apologise and help I fall at your feet

Report message to a moderator

Re: Help with a command [message #645782 is a reply to message #645779]

Mon, 14 December 2015 23:49

vs140580
Messages: 70
Registered: December 2015

Member

Respected sir,

I hope u can help

Report message to a moderator

Re: Help with a command [message #645811 is a reply to message #645782]

Tue, 15 December 2015 07:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have table gr with 12 columns say El to El2 as names of columns
posted file has at most only 4 or 5 columns of data.
explain how it applies to above.

Report message to a moderator

Re: Help with a command [message #645812 is a reply to message #645811]

Tue, 15 December 2015 07:34

vs140580
Messages: 70
Registered: December 2015

Member

I am sorry please edit it to 4 columns Say El to El as I have another table by typing error

Report message to a moderator

Re: Help with a command [message #645814 is a reply to message #645812]

Tue, 15 December 2015 07:35

vs140580
Messages: 70
Registered: December 2015

Member

E1 to E4

[Updated on: Tue, 15 December 2015 07:36]

Report message to a moderator

Re: Help with a command [message #645817 is a reply to message #645814]

Tue, 15 December 2015 07:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

vs140580 wrote on Tue, 15 December 2015 05:35

E1 to E4

OK, but what exists for E5 through E12?
How are these 7 columns processed?

I don't understand the problem or what expected results should be.

Report message to a moderator

Re: Help with a command [message #645818 is a reply to message #645817]

Tue, 15 December 2015 07:52

vs140580
Messages: 70
Registered: December 2015

Member

That is totally a different table of same type see my first post N columns we choose N+1 rows in the example I have taken 4 columns so 5 rows which satisfies the conditions so this data has no 7th to 12 th columns. If there is that data with 12 columns I have to choose 13 rows from it which satisfies conditions see first post above please ask anything please kind help

Report message to a moderator

Re: Help with a command [message #645840 is a reply to message #645818]

Tue, 15 December 2015 08:57

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Nobody will be able to help you unless you post SQL for create and populate table, expected results and rules and logic to get the results.

SY.

Report message to a moderator

Re: Help with a command [message #645844 is a reply to message #645840]

Tue, 15 December 2015 09:05

vs140580
Messages: 70
Registered: December 2015

Member

Respected sir,

In oracle we can populate the table data in csv file here if u see from our discussion that all fields are varchar expected result is in example of first post and table variable names are the first row of csv file if u can download

[Updated on: Tue, 15 December 2015 09:06]

Report message to a moderator

Re: Help with a command [message #645846 is a reply to message #645844]

Tue, 15 December 2015 09:13

vs140580
Messages: 70
Registered: December 2015

Member

This is the SQL statement for create table sample with 4 coulmns

CREATE TABLE "SYSTEM"."SAMPLE"
( "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" ;

where i have to pick 5 columns one answers result is

(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)

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.

csv data for this is attached in first post i attach once more.

Attachment: sample.csv
(Size: 2.94KB, Downloaded 1238 times)

[Updated on: Tue, 15 December 2015 09:15]

Report message to a moderator

Re: Help with a command [message #645862 is a reply to message #645846]

Tue, 15 December 2015 09:51

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

I see no INSERTs to populate table and see no expected results and no rules how to get them.

SY.

Report message to a moderator

Re: Help with a command [message #645863 is a reply to message #645862]

Tue, 15 December 2015 09:53

vs140580
Messages: 70
Registered: December 2015

Member

Insert not required as i can populate with the csv file in oracle sql developer. data populated is in the csv file

[Updated on: Tue, 15 December 2015 09:54]

Report message to a moderator

Re: Help with a command [message #645865 is a reply to message #645863]

Tue, 15 December 2015 09:57

vs140580
Messages: 70
Registered: December 2015

Member

Expected result: is

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.

remove all such possible N+1 rows from the table with N columns

in example we have to remove 5 rows as it has 4 coulumns

example output is

(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)

Report message to a moderator

Re: Help with a command [message #645866 is a reply to message #645865]

Tue, 15 December 2015 09:59

vs140580
Messages: 70
Registered: December 2015

Member

Rule:

N+1 rows of N elements or here columns, 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.

Report message to a moderator

Re: Help with a command [message #645868 is a reply to message #645866]

Tue, 15 December 2015 10:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://www.google.com/search?q=einstein+quote+insanity+definition&hl=en&biw=1366&bih=643&site=webhp&source=lnms& amp;tbm=isch&sa=X&ved=0ahUKEwiOyJ29nt7JAhVL1mMKHXTyAQoQ_AUIBigB

Report message to a moderator

Re: Help with a command [message #645870 is a reply to message #645868]

Tue, 15 December 2015 10:05

vs140580
Messages: 70
Registered: December 2015

Member

so what should i do sir if some asks me how to insert data here to insert huge data we dont require a insert command

please advice me

Report message to a moderator

Re: Help with a command [message #645871 is a reply to message #645870]

Tue, 15 December 2015 10:06

vs140580
Messages: 70
Registered: December 2015

Member

please advice me and tell me i will follow i have to solve my problem to get a result

Report message to a moderator

Re: Help with a command [message #645872 is a reply to message #645871]

Tue, 15 December 2015 10:11

vs140580
Messages: 70
Registered: December 2015

Member

one thing in this discussion is have understood how to put a problem to some extent better by doing in over and over again by myself and thinking

Report message to a moderator

Re: Help with a command [message #645876 is a reply to message #645872]

Tue, 15 December 2015 12:45

vs140580
Messages: 70
Registered: December 2015

Member

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 13 Column: 22 help to correct the below query

select * from graph h1 inner join graph h2 inner join graph h3 inner join graph h4 inner join graph h5 inner join graph h6 inner join graph h7 inner join graph h8 inner join graph h9 inner join graph h10 inner join graph h11 inner join graph h12 inner join graph h13 ON(
h1.e1=h2.e1 and h1.e2=h3.e1 and h1.e3=h4.e1 and h1.e4=h5.e1 and h1.e5=h6.e1 and h1.e6=h7.e1 and h1.e7=h8.e1 and h1.e8=h9.e1 and h1.e9=h10.e1 and h1.e10=h11.e1 and h1.e11=h12.e1 and h1.e12=h13.e1
and h2.e2=h3.e2 and h2.e3=h4.e2 and h2.e4=h5.e2 and h2.e5=h6.e2 and h2.e6=h7.e2 and h2.e7=h8.e2 and h2.e8=h9.e2 and h2.e9=h10.e2 and h2.e10=h11.e2 and h2.e11=h12.e2 and h2.e12=h13.e2
and h3.e3=h4.e3 and h3.e4=h5.e3 and h3.e5=h6.e3 and h3.e6=h7.e3 and h3.e7=h8.e3 and h3.e8=h9.e3 and h3.e9=h10.e3 and h3.e10=h11.e3 and h3.e11=h12.e3 and h3.e12=h13.e3
and h4.e4=h5.e4 and h4.e5=h6.e4 and h4.e6=h7.e4 and h4.e7=h8.e4 and h4.e8=h9.e4 and h4.e9=h10.e4 and h4.e10=h11.e4 and h4.e11=h12.e4 and h4.e12=h13.e4
and h5.e5=h6.e5 and h5.e6=h7.e5 and h5.e7=h8.e5 and h5.e8=h9.e5 and h5.e9=h10.e5 and h5.e10=h11.e5 and h5.e11=h12.e5 and h5.e12=h13.e5
and h6.e6=h7.e6 and h6.e7=h8.e6 and h6.e8=h9.e6 and h6.e9=h10.e6 and h6.e10=h11.e6 and h6.e11=h12.e6 and h6.e12=h13.e6
and h7.e7=h8.e7 and h7.e8=h9.e7 and h7.e9=h10.e7 and h7.e10=h11.e7 and h7.e11=h12.e7 and h7.e12=h13.e7
and h8.e8=h9.e8 and h8.e9=h10.e8 and h8.e10=h11.e8 and h8.e11=h12.e8 and h8.e12=h13.e8
and h9.e9=h10.e9 and h9.e10=h11.e9 and h9.e11=h12.e9 and h9.e12=h13.e9
and h10.e10=h11.e10 and h10.e11=h12.e10 and h10.e12=h13.e10
and h11.e11=h12.e11 and h11.e12=h13.e11
and h12.e12=h13.e12)

Report message to a moderator

Re: Help with a command [message #645877 is a reply to message #645876]

Tue, 15 December 2015 12:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

which line throws error from below?

SELECT     * 
FROM       graph h1 
inner join graph h2 
inner join graph h3 
inner join graph h4 
inner join graph h5 
inner join graph h6 
inner join graph h7 
inner join graph h8 
inner join graph h9 
inner join graph h10 
inner join graph h11 
inner join graph h12 
inner join graph h13 
ON        ( 
                      h1.e1=h2.e1 
           AND        h1.e2=h3.e1 
           AND        h1.e3=h4.e1 
           AND        h1.e4=h5.e1 
           AND        h1.e5=h6.e1 
           AND        h1.e6=h7.e1 
           AND        h1.e7=h8.e1 
           AND        h1.e8=h9.e1 
           AND        h1.e9=h10.e1 
           AND        h1.e10=h11.e1 
           AND        h1.e11=h12.e1 
           AND        h1.e12=h13.e1 
           AND        h2.e2=h3.e2 
           AND        h2.e3=h4.e2 
           AND        h2.e4=h5.e2 
           AND        h2.e5=h6.e2 
           AND        h2.e6=h7.e2 
           AND        h2.e7=h8.e2 
           AND        h2.e8=h9.e2 
           AND        h2.e9=h10.e2 
           AND        h2.e10=h11.e2 
           AND        h2.e11=h12.e2 
           AND        h2.e12=h13.e2 
           AND        h3.e3=h4.e3 
           AND        h3.e4=h5.e3 
           AND        h3.e5=h6.e3 
           AND        h3.e6=h7.e3 
           AND        h3.e7=h8.e3 
           AND        h3.e8=h9.e3 
           AND        h3.e9=h10.e3 
           AND        h3.e10=h11.e3 
           AND        h3.e11=h12.e3 
           AND        h3.e12=h13.e3 
           AND        h4.e4=h5.e4 
           AND        h4.e5=h6.e4 
           AND        h4.e6=h7.e4 
           AND        h4.e7=h8.e4 
           AND        h4.e8=h9.e4 
           AND        h4.e9=h10.e4 
           AND        h4.e10=h11.e4 
           AND        h4.e11=h12.e4 
           AND        h4.e12=h13.e4 
           AND        h5.e5=h6.e5 
           AND        h5.e6=h7.e5 
           AND        h5.e7=h8.e5 
           AND        h5.e8=h9.e5 
           AND        h5.e9=h10.e5 
           AND        h5.e10=h11.e5 
           AND        h5.e11=h12.e5 
           AND        h5.e12=h13.e5 
           AND        h6.e6=h7.e6 
           AND        h6.e7=h8.e6 
           AND        h6.e8=h9.e6 
           AND        h6.e9=h10.e6 
           AND        h6.e10=h11.e6 
           AND        h6.e11=h12.e6 
           AND        h6.e12=h13.e6 
           AND        h7.e7=h8.e7 
           AND        h7.e8=h9.e7 
           AND        h7.e9=h10.e7 
           AND        h7.e10=h11.e7 
           AND        h7.e11=h12.e7 
           AND        h7.e12=h13.e7 
           AND        h8.e8=h9.e8 
           AND        h8.e9=h10.e8 
           AND        h8.e10=h11.e8 
           AND        h8.e11=h12.e8 
           AND        h8.e12=h13.e8 
           AND        h9.e9=h10.e9 
           AND        h9.e10=h11.e9 
           AND        h9.e11=h12.e9 
           AND        h9.e12=h13.e9 
           AND        h10.e10=h11.e10 
           AND        h10.e11=h12.e10 
           AND        h10.e12=h13.e10 
           AND        h11.e11=h12.e11 
           AND        h11.e12=h13.e11 
           AND        h12.e12=h13.e12)

Report message to a moderator

Re: Help with a command [message #645878 is a reply to message #645877]

Tue, 15 December 2015 12:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Query above will scale as well as my goat can fly.

Report message to a moderator

Re: Help with a command [message #645879 is a reply to message #645878]

Tue, 15 December 2015 13:06

vs140580
Messages: 70
Registered: December 2015

Member

when i queried the above in oracle it gave the below error

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 93 Column: 38

Report message to a moderator

Re: Help with a command [message #645880 is a reply to message #645879]

Tue, 15 December 2015 13:07

vs140580
Messages: 70
Registered: December 2015

Member

If u can improve the query possibly as i am new to sql learning

Report message to a moderator

Re: Help with a command [message #645884 is a reply to message #645880]

Tue, 15 December 2015 14:31

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes as soon as you post a test case.

Report message to a moderator

Re: Help with a command [message #645886 is a reply to message #645880]

Tue, 15 December 2015 15:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

vs140580 wrote on Tue, 15 December 2015 11:07

If u can improve the query possibly as i am new to sql learning

The first improvement is to post a SELECT that is valid syntax.
unless & until every column in ON clause is indexed, Full Table Scan will result.

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	how to remove more than one value
Next Topic:	Define/declare dates and use in CTE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 21 12:39:58 CDT 2026