arithmetic overflow

hello everyone,

when using this select query:

select distinct r.*
FROM Rezept r
INNER JOIN Verordnung ON r.Mandant = Verordnung.Mandant AND r.Monat = Verordnung.Monat AND r.Abrechner = Verordnung.Abrechner AND
r.PIC = Verordnung.PIC
where
Verordnung.Taxe-(Verordnung.Preis*Verordnung.Faktor)>500 and r.gesamtbrutto >0
and r.abgabedatum is not null and r.mandant in(1) and r.monat = 1236

i get the error 8115
Arithmetic overflow while converting from experession to int

Rezept and Verordnung are both views with check constraint on month.

If use the above query without 'distinct' everything works fine.
Any idea whats the problem ?Post some DDL...sounds like a column is not defined as numeric, though, and is doing an implicit conversion, and some data is not numeric...

tough to tell w/out the ddl of the tables though

I don't see how DISTINCT has anything to do with it...|||Oj, here we go:

CREATE TABLE [dbo].[Verordnung] (
[Monat] [smallint] NOT NULL ,
[Mandant] [tinyint] NOT NULL ,
[Abrechner] [int] NOT NULL ,
[PIC] [int] NOT NULL ,
[Pos] [tinyint] NOT NULL ,
[Artikel] [int] NULL ,
[PZN] [int] NOT NULL ,
[HilfsmittelNr] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Faktor] [int] NOT NULL ,
[Taxe] [int] NOT NULL ,
[NrTyp] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[NullPos] [tinyint] NULL ,
[Import] [tinyint] NULL ,
[OriginalPZN] [int] NULL ,
[AutIdem] [tinyint] NULL ,
[Preis] [int] NULL ,
[Zuzahlung] [int] NULL ,
[RabattApo] [int] NULL ,
[RabattGH] [int] NULL ,
[RabattHst] [int] NULL ,
[RLIndGr] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ATC] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[TS] [smallint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Verordnung] ADD
CONSTRAINT [PK_Verordnung] PRIMARY KEY CLUSTERED
(
[Monat],
[Mandant],
[Abrechner],
[PIC],
[Pos]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Verordnung] ADD
CONSTRAINT [FK_Verordnung_Rezept] FOREIGN KEY
(
[Monat],
[Mandant],
[Abrechner],
[PIC]
) REFERENCES [dbo].[Rezept] (
[Monat],
[Mandant],
[Abrechner],
[PIC]
)
GO

CREATE TABLE [dbo].[Rezept] (
[Monat] [smallint] NOT NULL ,
[Mandant] [tinyint] NOT NULL ,
[Abrechner] [int] NOT NULL ,
[PIC] [int] NOT NULL ,
[Belegnr] [varchar] (18) COLLATE Latin1_General_CI_AS NOT NULL ,
[Kasse] [int] NOT NULL ,
[Apotheke] [int] NOT NULL ,
[Arzt] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[VersichertenNr] [bigint] NOT NULL ,
[RefVNr] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[Titel] [varchar] (17) COLLATE Latin1_General_CI_AS NULL ,
[Nachname] [varchar] (47) COLLATE Latin1_General_CI_AS NULL ,
[Vorname] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[Geburtsdatum] [datetime] NULL ,
[PLZ] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[Strasse] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[Ort] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[VersichertenStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
[RSA] [varchar] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[Land] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
[Unfall] [tinyint] NULL ,
[Arbeitsunfall] [tinyint] NULL ,
[Noctu] [tinyint] NULL ,
[SonstigeKennzeichen] [tinyint] NULL ,
[BVG] [tinyint] NULL ,
[Hilfsmittel] [tinyint] NULL ,
[Impfstoff] [tinyint] NULL ,
[Sprechstundenbedarf] [tinyint] NOT NULL ,
[TA4Image] [tinyint] NULL ,
[Gebuehrenpflicht] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
[Begruendungspflicht] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
[Verordnungsdatum] [datetime] NULL ,
[Unfalldatum] [datetime] NULL ,
[Unfallbetrieb] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[VKGueltigBis] [smallint] NULL ,
[Abgabedatum] [datetime] NULL ,
[GesamtBrutto] [bigint] NOT NULL ,
[GesamtZuzahlung] [bigint] NOT NULL ,
[IrrlaeuferApotheke] [int] NULL ,
[AbrechnerNr] [int] NULL ,
[TiffDatei] [int] NULL ,
[TiffSeite] [int] NULL ,
[TiffOffset] [int] NULL ,
[Rechnung] [int] NULL ,
[RechNr] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[RechDatum] [datetime] NULL ,
[RechZeitraum] [datetime] NULL ,
[RechArt] [tinyint] NULL ,
[Waehrung] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[SummePreis] [bigint] NULL ,
[SummeZuzahlung] [int] NULL ,
[SummeRabatt] [bigint] NULL ,
[SummeTaxe] [bigint] NULL ,
[Korrigiert] [tinyint] NULL ,
[KorrekturSperre] [tinyint] NULL ,
[Bearbeiter] [smallint] NULL ,
[ImpFile] [int] NULL ,
[VersNrLen] [tinyint] NULL ,
[TS] [smallint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Rezept] ADD
CONSTRAINT [PK_Rezept_Korrektur] PRIMARY KEY CLUSTERED
(
[Monat],
[Mandant],
[Abrechner],
[PIC]
) ON [PRIMARY]
GO

Now the views :

CREATE VIEW [Rezept] AS
SELECT * FROM [S007EC].[GFS].[dbo].[Rezept_1236]
UNION ALL
SELECT * FROM [S007EC].[GFS].[dbo].[Rezept_1237]

CREATE VIEW [Verordnung] AS
SELECT * FROM [S007EC].[GFS].[dbo].[Verordnung_1236] where monat=1236
UNION ALL
SELECT * FROM [S007EC].[GFS].[dbo].[Verordnung_1237] where monat=1237

Rezept_xxxx are of type Rezept and same for Verordnung
With removing DISTINCT the problem doesnt occur.
The problem also occurs if i remove distinct and use a group by instead
like:

select distinct r.monat, r.mandant, r.abrechner, r.pic
FROM Rezept r
INNER JOIN Verordnung ON r.Mandant = Verordnung.Mandant AND r.Monat = Verordnung.Monat AND r.Abrechner = Verordnung.Abrechner AND
r.PIC = Verordnung.PIC
where
Verordnung.Taxe-(Verordnung.Preis*Verordnung.Faktor)>500 and r.gesamtbrutto >0
and r.abgabedatum is not null and r.mandant in(1) and r.monat = 1236
group by r.monat, r.mandant, r.abrechner, r.pic|||I think I hurt myself...

Does this work?

SELECT COUNT(*)
FROM Rezept r
INNER JOIN Verordnung ON r.Mandant = Verordnung.Mandant AND r.Monat = Verordnung.Monat AND r.Abrechner = Verordnung.Abrechner AND
r.PIC = Verordnung.PIC
where
Verordnung.Taxe-(Verordnung.Preis*Verordnung.Faktor)>500 and r.gesamtbrutto >0
and r.abgabedatum is not null and r.mandant in(1) and r.monat = 1236

I don't see how a GROUP BY Or DISTINCT should cause you any trouble...

How many rows do you get back?

and does it come back?|||What is the maximum value from the following:

(Verordnung.Preis*Verordnung.Faktor)|||Thanks for all the replies here .

The problem was indeed
verordnung.preis*verordnung.faktor
Sounds obvious, but the distinct stuff lead me into wrong way ...
I still don't know why w/o distinct i don't get any error.