Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20

    Unanswered: 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 ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    What is the maximum value from the following:

    (Verordnung.Preis*Verordnung.Faktor)

  6. #6
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •