Problem izvrsavanja UDF

Pozdrav svima,

problem je sledeci :nakon izvrsavanja funkcije IpAdresaDrzava dobijem poruku o gresci:Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Kako otkloniti gresku?


Funkcija je :
CREATE FUNCTION [dbo].[IpAdresaDrzava](@IpAdresa AS varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Ipdrzava as varchar(50)
SET @Ipdrzava =(SELECT geoip2.drzava FROM dbo.geoip2 WHERE pocadresa<=@IpAdresa and krajadresa>=@IpAdresa)
RETURN (@Ipdrzava)
END

computed kolona:

ALTER TABLE ipnumber
ADD [IpDrzava] AS [dbo].[IpAdresaDrzava([ipadresa])]

PRVA TABELA JE:

create table ipnumber
(
ipadresa varchar (50),
drzava varchar (50),

)

INSERT INTO ipnumber values ( 785044919,'??')
INSERT INTO ipnumber values (1838277475,'??')
INSERT INTO ipnumber values (459937403,'??')
INSERT INTO ipnumber values (1841745892,'??')

DRUGA TABELA JE:

create table geoip2
(
pocadresa varchar (50),
krajadresa varchar (50),
drzava varchar (50),
)

INSERT INTO geoip values (16777216 ,16777471 ,'AU')
INSERT INTO geoip values (16777472 ,16778239 ,'CN')
INSERT INTO geoip values ( 16778240 ,16779263 ,'AU')
INSERT INTO geoip values (16779264 ,16781311 ,'CN')

Funkcija ti vraca vishe od jednog zapisa za drzavu. Proveri da nemas za neku drzavu uneto VISHE PUTA pocadresa, krajadresa ili mozda imas preklapanja intervala pocadresa, krajadresa, odnosno neka IdAdresa je u intervalima pocadresa i krajadresa za vishe drzava. znaci oznaci ih kao CAN1, CAN2, AU1,AU2.
Insert je za tabelu geoip a u funkciji je tabela geoip2

Pozdrav Nado,

testirao sam funkciju nad prostim tabelama i istu gresku izbacuje.Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Tabele su :

CREATE TABLE [dbo].[ipnumber](
[ipadresa] [varchar](50) NOT NULL
)

INSERT INTO ipnumber values ( 2)
INSERT INTO ipnumber values (5)
INSERT INTO ipnumber values (10)
INSERT INTO ipnumber values (15)


CREATE TABLE [dbo].[testgeoip](
[pocadresa] [varchar](50) NULL,
[krajadresa] [varchar](50) NULL,
[drzava] [varchar](50) NULL
)

INSERT INTO testgeoip values ( 1, 3)
INSERT INTO testgeoip values (4, 6)
INSERT INTO testgeoip values (8, 11)
INSERT INTO testgeoip values (14, 16)

Nada je u pravu (kao i obicno :-) )


Ako kveri koji dodeljuje vrednost varijabli @IPdrzava vraca vise od jednog reda, ond funkcija puca. Funkcija ne mora da pukne uvek, sve zavisi od parametra @IpAdresa. Za neke vrednosti @IPadresa, kveri verovatno vraca tacno jedan red, a za neke vraca vise od jednog reda. To znaci da je problem ili u kveriju ili u tabeli dbo.geoip2.

LOS PRIMER, ustvari dobar test primer. Posto ip adresa 15 kao string se nalazi u intervalu (1,3), 1<15<3, ista se nalazi i u intervalu (14,16), 14<15<16 zato puca...vraca dve vrednosti.
Mozda sa izmenom u funkciji SELECT geoip2.drzava FROM dbo.geoip2 WHERE cast(pocadresa as int)<=cast(@IpAdresa as int) and cast(krajadresa as int)>=cast(@IpAdresa as int) ; ali mislim da treba proveriti intervale ipadresa kao stringa.

Hvala na brzim odgovorima.


Probao sam da za data type kolone ipadresa postavim int,pokusao sam i sa modifikovanom funkcijom koju je Nada predlozila,medjutim,sada je problem:value is too large for int32.(u oba slucaja).

Prijedlog za jednoznacnim oznacavanjem drzava nije bas praktican,jer u tabeli geoip ima 300 000 redova i 248 razlicitih drzava,sto znaci da se drzave ponavljaju,tj.jedna drzava ima vishe stotina opsega.

Nadam se da imate jos kakav prijedlog.

Zahvaljujem,velik pozdrav.

Intervali ipadresa su jednoznacni,ne ponavljaju se.

Onda stavi bigint. On pokriva do 2 na 31.

Potpis

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Nije problem u drzavama, problem je u intervalima.

probaj d aizvrsis ovaj query i vidi sta vraca, dali mozda negde imas neku ip adresu koja se ponavlja u dva(ili vishe) intervala.

SELECT COUNT(IPADRESA),IPADRESA
FROM
(select a.*,b.*
from
(select * from dbo.geoip2) a
CROSS join
(select distinct ipadresa from dbo.ipnumber) b
where ipadresa>=pocadresa and ipadresa<=krajadresa) C
GROUP BY IPADRESA
HAVING COUNT(IPADRESA)>1

Query vraca rezultat,postoji vise intervala za svaku adresu:

(No column name) IPADRESA
3 2096962874
2 1567709242
3 3642298724
3 3250345690
3 2988795724
2 1534856017
3 2015144836
2 1332076830
2 1392880588

Nije mi bas najjasniji broj redova koje vraca upit (225410),otkud toliki broj ako ipnumber tabela ima (231210) i geoip2 (150241)?

Nado,mozes li mi pojasniti ovaj dio skripte,dio sa select a.*,b.*,koja je uloga (.*)?

Upit vraca ipadrese koje pripadaju u vishe od jedan definisan interval.
pr. ipadresa 2096962874 se nalazi u 3 intervala.
Sa sledecim querijem otkrices koja su to 3 intervala.

declare @vracenaipadresa as varchar(30)
set @vracenaipadresa='2096962874'
select a.*,b.*
from
(select * from dbo.geoip2) a
CROSS join
(select distinct ipadresa from dbo.ipnumber where ipadresa=@vracenaipadresa) b
where ipadresa>=pocadresa and ipadresa<=krajadresa

Proveri te intervale, nesto nije u redu sa njima, ili prikazi ih da i mi vidimo.
Ako nadjemo resenje za njih, nacimo i za ostale ipadrese i njene intervale. Dok se nesrede intervali u dbo.geoip2 i funkicja nece raditi, za ip adrese koje se nalazi u vishe od jednog intervala.

Zahvaljujem Nado, naucio sam dosta iz tvojih query/ja.

Zadnji query vraca sledece vrijednosti:



Opsezi nisu isti,ali ih 'vidi' kao da jesu.data type problem?

iz onog sto vidim iz ovog primera, ne znam dali je slican slucaj i sa drugima, duzine pocadresa i kraj adresa su razlicite.
Ako promenis uslov u funkciji
SET @Ipdrzava =(SELECT geoip2.drzava FROM dbo.geoip2 WHERE pocadresa<=@IpAdresa and krajadresa>=@IpAdresa
and len(pocadresa)=len (@IpAdresa) and len(krajadresa)=len(@Ipadresa))
mozda ce funkcionisati..

nadam se da ne trebas raditi ltrim(rtrim(pocadresa)) i ltrim(rtrim(krajadresa))

za konkretan primer sad ce promenjen queri vratiti samo IN, sto predpostavljam i treba

declare @vracenaipadresa as varchar(30)
set @vracenaipadresa='2096962874'
select a.*,b.*
from
(select * from dbo.geoip2) a
CROSS join
(select distinct ipadresa from dbo.ipnumber where ipadresa=@vracenaipadresa) b
where ipadresa>=pocadresa and ipadresa<=krajadresa and len(pocadresa)=len(@vracenaipadresa) and len(krajadresa)=len(@vracenaipadresa)