Home » SQL & PL/SQL » SQL & PL/SQL » Something Like a loop
Something Like a loop [message #413351] Wed, 15 July 2009 06:34 Go to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hi,

trying to do a (for me) very tricky sql statement:

Here is a result from an sql statement where i need to do an extra query to achieve my query goal:

select inf.G3ID, inf.G3FNO, inf.G3_OWN_ID, inf.CID, 
p.k_keitNR, c.c_leitNR from B$B_MANY_INF_N inf, 
B$E_POLE p, B$E_CABLE c
where inf.G3FNO = 1070
and inf.G3_OWN_FNO = 1010
and inf.G3ID = p.G3ID
and inf.G3_OWN_ID = c.G3ID

.[?].
.[?].
.[?].


The result is a table with ~141k entries!
The following table is a part of this:

[
[B]G3ID         G3FNO   G3_OWN_ID      CID      k_keitNR  c_leitN[/B]

400047913	1070	606597558	1	2/1328d	  2/1328d
400047913	1070	606612330	2	2/1328d	  2/520b
400047913	1070	606612331	3	2/1328d	  2/597b
400047913	1070	606597525	4	2/1328d	  2/1328d
609630061	1070	609630063	1	555	  12345
.
.
.


For explanation what i need now!

the G3ID with the same Number (like 400047913) is one single objekt. The G3_OWN_ID is another Objekt related to this one.

What I need to do is: the G3ID Objekt have (remember, no matter how often the same number exist its only on objekt) between 1 and 18 related objekts. (This is shown by the CID Value.
The goal now is to do the query this way, to check if one of the related objekts have the same value in the c_leitN column as in the k_keitNR. If not, this G3ID is the Result.

So in this Case, only this line
609630061	1070	609630063	1	555	  12345
is the result.

My problem is, how i can tell with sql, that it has to compare every row from CID 1 to x and if 1 or more k_keitNR and c_leitN are equal dont show this as result. Show the rows where there is no 1 to x CID row equal.

thx a lot in advance

[Updated on: Wed, 15 July 2009 06:37] by Moderator

Report message to a moderator

Re: something like a loop [message #413354 is a reply to message #413351] Wed, 15 July 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
something like a loop [message #413357 is a reply to message #413351] Wed, 15 July 2009 06:42 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
a table and a part of the statement already postet, also the result.

but sorry, i have no clue how to perform the query to get this restult!

[Updated on: Wed, 15 July 2009 06:52]

Report message to a moderator

Re: something like a loop [message #413360 is a reply to message #413357] Wed, 15 July 2009 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't post what I asked.
And STOP multiposting your question, I had to delete 4 of them.

Regards
Michel
Re: something like a loop [message #413365 is a reply to message #413360] Wed, 15 July 2009 07:12 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hope this is correct! and sorry for the multi posts, happened because i had a horrible lag at posting and a nervous finger Razz

CREATE Table ownersh
(G3ID varchar2(9),
G3FNO number(4),
G3_OWN_ID varchar2(9),
CID number(1),
k_keitNR varchar(7),
c_leitN varchar(7))

INSERT INTO ownersh(G3ID, G3FNO, G3_OWN_ID, CID, k_keitNR, c_leitN)
VALUES 	('400047913',	'1070',	'606597558',	'1',	'2/1328d', '2/1328d'),
	('400047913',	'1070',	'606612330',	'2',	'2/1328d', '2/520b'),
	('400047913',	'1070',	'606612331',	'3',	'2/1328d', '2/597b'),
	('400047913',	'1070',	'606597525',	'4',	'2/1328d', '2/1328d'),
	('609630061',	'1070',	'609630063',	'1',	'555',	   '12345');


Result:
609630061	1070	609630063	1	555	  12345

[Updated on: Wed, 15 July 2009 07:16]

Report message to a moderator

Re: something like a loop [message #413366 is a reply to message #413365] Wed, 15 July 2009 07:26 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 15 July 2009 13:39
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Previous Topic: weekly interval partitioning
Next Topic: Dynamic sql
Goto Forum:
  


Current Time: Wed Dec 07 11:05:58 CST 2016

Total time taken to generate the page: 0.15111 seconds