delen door 0 melding

[andycorp]

ik krijg de melding bij het runnen van een query "division by zero".
zie onderstaande UNION query.
het grappige is dat ie deze melding niet geeft als ik de SELECT statements apart draai.

[SQL]SELECT * INTO SpadeFeed
FROM (SELECT Assignaties.[Project description] AS Opportunity,
Assignaties.[Project closing date] AS [CTT Sign Date],
"EUR" AS [Currency],
Assignaties.[Customer name] AS Account,
Assignaties.[Sales Person] AS [Sales Team],
"7" AS [Sales stage],
"ITS Benelux" AS [Selling BU],
"A1..G3" AS Groupskillpractice,
"A1..G3" AS Skillpractice,
Format(IIf([FTE].[Betaaltijd] Is Null,(AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end])*8*([Rate]*([Assign %]/100))),(AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end])*8*([Rate]*([Assign %]/100)))*([FTE]![Betaaltijd]/100)),"Currency") AS Bookings,
Format(IIf(([Rate] Is Not Null Or Str([Rate])<>"0" Or Str([RATE])<>"0.00" Or Str([rate])<>"0,00"),(([Rate]-[DKM])/[Rate]),0),"0.0%") AS [Margin %], IIf([DeliveryUnit]="Subcontractor GDC","Mumbay","No") AS DistributedDelivery,
Assignaties.[Assign start] AS [Start Date],
AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end]) AS Duration,
"None" AS Alliance,
IIf(Len([Empl practice])=6,Right([Empl practice],3),[Empl practice]) AS DeliveryUnit,
"GOU ITS" AS SLOrganization, 1 AS CCSBooking,
1 AS CCSDays,
Assignaties.[Project Code],
Assignaties.[Prj practice],
(Format$(CDate(DateSerial(Year(Date()),Month(Date()),1)),"mm/yyyy")) AS [Month],
Assignaties.[Assign start],
Assignaties.[Assign end]
FROM DKM RIGHT JOIN (FTE RIGHT JOIN Assignaties ON FTE.Persnr = Assignaties.Number) ON DKM.level = FTE.level
WHERE
(([Assignaties]![Assign start])<DateSerial(Year(Date()),Month(Date()),1)
AND (([Assignaties]![Assign end])>DateSerial(Year(Date()),Month(Date()),0)))
OR
(([Assignaties]![Assign start])<DateSerial(Year(Date()),Month(Date()),1)
AND (([Assignaties]![Assign end]) Between DateSerial(Year(Date()),Month(Date()),0) And DateSerial(Year(Date()),Month(Date())+1,0)))
OR
(([Assignaties]![Assign start]) Between DateSerial(Year(Date()),Month(Date()),1)
And (DateSerial(Year(Date()),Month(Date())+1,0))) AND (([Assignaties]![Assign end])>DateSerial(Year(Date()),Month(Date()),0))
OR
(([Assignaties]![Assign start]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)
AND (([Assignaties]![Assign end]) Between DateSerial(Year(Date()),Month(Date())+1,0) And DateSerial(Year(Date()),Month(Date()),0)))


UNION SELECT
Assignaties.[Project description] AS Opportunity, Assignaties.[Project closing date] AS [CTT Sign Date], "EUR" AS [Currency], Assignaties.[Customer name] AS Account, Assignaties.[Sales Person] AS [Sales Team], "7" AS [Sales stage], "ITS Benelux" AS [Selling BU], "A1..G3" AS Groupskillpractice, "A1..G3" AS Skillpractice, Format(IIf([FTE].[Betaaltijd] Is Null,(AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end])*8*([Rate]*([Assign %]/100))),(AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end])*8*([Rate]*([Assign %]/100)))*([FTE]![Betaaltijd]/100)),"Currency") AS Bookings, Format(IIf(([Rate] Is Not Null Or Str([Rate])<>"0" Or Str([RATE])<>"0.00" Or Str([rate])<>"0,00"),(([Rate]-[DKM])/[Rate]),0),"0.0%") AS [Margin %], IIf([DeliveryUnit]="Subcontractor GDC","Mumbay","No") AS DistributedDelivery, Assignaties.[Assign start] AS [Start Date], AantalWerkdagen([Assignaties]![Assign start],[Assignaties]![Assign end]) AS Duration, "None" AS Alliance, IIf(Len([Empl practice])=6,Right([Empl practice],3),[Empl practice]) AS DeliveryUnit, "GOU ITS" AS SLOrganization, 1 AS CCSBooking, 1 AS CCSDays, Assignaties.[Project Code], Assignaties.[Prj practice], (Format$(CDate(DateSerial(Year(Date()),Month(Date())+1,1)),"mm/yyyy")) AS [Month],Assignaties.[Assign start],
Assignaties.[Assign end]
FROM DKM RIGHT JOIN (FTE RIGHT JOIN Assignaties ON FTE.Persnr = Assignaties.Number) ON DKM.level = FTE.level
WHERE
(([Assignaties]![Assign start])<DateSerial(Year(Date()),Month(Date())+1,1)
AND (([Assignaties]![Assign end])>DateSerial(Year(Date()),Month(Date())+2,0)))
OR
(([Assignaties]![Assign start])<DateSerial(Year(Date()),Month(Date())+1,1)
AND (([Assignaties]![Assign end]) Between DateSerial(Year(Date()),Month(Date())+2,0) And DateSerial(Year(Date()),Month(Date()),0)))

OR
(([Assignaties]![Assign start]) Between DateSerial(Year(Date()),Month(Date())+1,1)
And (DateSerial(Year(Date()),Month(Date())+2,0)) AND (([Assignaties]![Assign end])>DateSerial(Year(Date()),Month(Date())+2,0)))

OR
(([Assignaties]![Assign start]) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+2,0)
AND (([Assignaties]![Assign end]) Between DateSerial(Year(Date()),Month(Date()),0) And DateSerial(Year(Date()),Month(Date())+2,0) ))) AS [%$##@_Alias];[/SQL]

Gaarne hulp

 

[yokiamy]

misschien kun je de code tags gebruiken, dat scheelt een slok op een borrel qua leesbaarheid.

 

[andycorp]

@yokiamy, begrijp niet wat je bedoeld. code tags?

 

[OctaFish]

Als je code plaatst, kun je die met de knop CODE (de knop met het # teken) opmaken, zodat hij voor ons leesbaar is. Jouw code is dat zo eigenlijk niet... Je kunt aan het begin ook deze tekst typen: [ CODE ] en aan het eind [ /CODE ], dan staat de tekst ook gelijk goed. Ik heb overigens wel spaties voor en achter het woord CODE gezet, omdat ik de code anders gelijk toepas. Dus als je dat alsnog wilt doen, dan graag....

Overigens heb je een vergelijkbare code voor SQL, dan vervang je het woord CODE door SQL. En ook daar is een aparte knop voor. (Guess what: daar staat SQL op...)

 

[andycorp]

gedaan!!
alvast dank voor hulp

 

[OctaFish]

Heb je al geprobeerd om eerst de UNION query te maken als aparte query, en die te gebruiken voor de Toevoegquery? En UNION ALL?

 

[andycorp]

@Octafish; ik snap je niet.

 

[OctaFish]

Toch niet zo lastig, dacht ik Je gebruikt een UNION als Toevoegquery. Ik zou eerst proberen om alleen de SELECT query te maken, en van die nieuwe query en UNION. Mijn tweede vraag was of je in je oorspronkelijke query ook UNION ALL had gepobeerd i.p.v. alleen UNION. Dus die variant kun je ook nog uitproberen.