SQL Server Prozedur und Accessaufruf

B

Babsi

SQL-Guru
Beiträge
147
Guten Morgen,

es geht um eine SQL Server Prozedur und den Aufruf in ACCESS.

Hier der Aufruf in Access:
If isMonth = True Then
' was soll hier noch passieren?
' DoCmd.OpenQuery "ZV Provision -> 00 letzte Rechnung", acNormal, acEdit
If GetProvIsSpecial() Then
kindofProvValue = 5
lResIDValue = Forms![wPU -> Provisionen erstellen]!kmb_SpecialProv
Else
kindofProvValue = 1
End If
Else
kindofProvValue = 0
End If


' Open the connection
conn.Open
' Create a new command object
Set cmd = CreateObject("ADODB.Command")

' Set the command properties
With cmd

cmd.CommandTimeout = 1000 ' Um TimeOutfehler zu umngehen
.ActiveConnection = conn
.CommandType = 4 ' adCmdStoredProc
.CommandText = "PrepairProvision"
End With

' parameter objects
' Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
Set param1 = cmd.CreateParameter("@DateFrom", 7, 1, , DateValue(dBegin))
Set param2 = cmd.CreateParameter("@DateTo", 7, 1, , DateValue(dEnd))
Set kindofProvi = cmd.CreateParameter("@ProviKind", 16, 1, , kindofProvValue)
Set lResID = cmd.CreateParameter("@SpzProvi", 16, 1, , lResIDValue)
' parameters to the command
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append kindofProvi
cmd.Parameters.Append lResID
' cmd.Parameters.Value = kindofProvValue
' Execute
cmd.Execute
und Hier der Teil der SQL Server Prozedur, der nicht richtig angesprochen wird...

USE [FENG]
GO
/****** Object: StoredProcedure [dbo].[PrepairProvision] Script Date: 18.11.2024 09:35:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PrepairProvision]

@DateFrom datetime,
@DateTo datetime,
@ProviKind as int,
@SpzProvi as int
AS

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
-- Wir benötigen hier eine Zwischentabelle da es in SQL Server kein DISTINTROW gibt
-- Daher erst => [zzHilfstabelle->Prov LastProv1]
-- dann => [zzHilfstabelle->Prov LastProv]
DELETE FROM dbo.[zzHilfstabelle->Prov LastProv1]
DELETE FROM dbo.[zzHilfstabelle->Prov LastProv]
DELETE FROM dbo.[zzHilfstabelle->ProvAnzTerm]
DELETE FROM dbo.[zzHilfstabelle->Prov Reseller]
DELETE FROM [zzHilfstabelle->Provision]
DELETE FROM [zzHilfstabelle->ProvAnzTerm]

-- Es fehlt der Teil, der angeblich die EC Provisonen zusammensucht, die berücksichtigen wir aber nicht mehr

-- Welche Art Provisioen soll erstellt werden, monatli, Quartal
--set @ProviKind= 0 --|| @ProviKind=0 ?

if @ProviKind=0
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE (RESELLER.RESELLER_PAYPROVI=1)
AND (RESELLER.RESELLER_PROVMONTH=0
AND (RESELLER.RESELLER_ISHV=0))
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT,[RESELLER_WHENNEW],RESELLER_ProvFrom
HAVING (((RESELLER.RESELLER_ID)<>11))
end
else if @ProviKind=5
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE RESELLER.RESELLER_PAYPROVI=1
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
HAVING (((RESELLER.RESELLER_ID)=@SpzProvi))
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;
end

else
begin
---INSERT INTO [zzHilfstabelle->Prov LastProv1]()
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 as isGWK, @ProviKind AS monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE ( (RESELLER.RESELLER_PAYPROVI=1)
AND RESELLER.RESELLER_PROVMONTH= 1

)
AND RESELLER.RESELLER_ISHV=0
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID<>11
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;
end
Ich hoffe das ist jetzt nicht zuviel Code und damit zu unübersichtlich. Ich weiß nur soviel, wenn
kindofProvValue = 1
oder kindofProvValue = 0
dann läuft alles. Wenn ich den Wert 5 übergebe, dann bejkomme ich kein Ergebnis, als wenn bei der If- else Anweisung was nicht greift.

Ein Ergebnis bekommt man, da ich den Teil für sich in einem neuen SQL Editor ausgeführt habe um zu kontrollieren.
1731922069738.webp
 
Werbung:
Hallo
Bei kindofProvValue 5 muss auch die Variable @SpzProvi einen Treffer liefern.
Bei den restlichen werden alle Daten angezeigt die eine Reseller_ID <> 11 haben.
Ich gehe davon aus, dass bei deiner Abfrage da einfach kein gültiges Ergebnis zustande kommt.
 
Hallo MDDaniel,

'Bei kindofProvValue 5 muss auch die Variable @SpzProvi einen Treffer liefern.'
Doch das ergibt eimn Ergebnis..

Wenn ich

end
--------else if @ProviKind=5
-------- begin
-------- INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
-------- SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
-------- 0 AS HVs, RESELLER.RESELLER_ISHV,
-------- Max(
-------- IIf([PROVAB_DATE] IS NUll,
-------- IIf([RESELLER_ProvFrom]IS Null,
-------- IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
-------- [RESELLER_WHENNEW]
-------- ),[RESELLER_ProvFrom]
-------- ),[PROVAB_DATE])
-------- )AS [Last],
-------- Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
-------- @DateTo AS bis, RESELLER.RESELLER_SHORT
-------- FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
-------- WHERE RESELLER.RESELLER_PAYPROVI=1
-------- GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
-------- HAVING (((RESELLER.RESELLER_ID)=@SpzProvi))
-------- ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
-------- RESELLER.RESELLER_SHORT DESC;
-------- end

else

auskommentiere, läuft das ganze Prpgramm wieder :-(

Ich habe das ja getestet, den Teil mit kindofProvValue 5 und @SpzProvi, das ergibt ein Result
1731923779608.webp

P.S.
wie bekomme ich denn hier eine Formatierung hin, das der Quellcode vernnftig abgebildet werden kann
 
Wenn ich in einem neuen SQL Fenster folgendes eingebe und ProviKind entsprechend des Access Programms ändere klappt alles :(
DECLARE @DateTo datetime,
@ProviKind as int,
@SpzProvi as int
set @DateTo = '30.07.2024'
set @ProviKind= 5
set @SpzProvi = 2019

DELETE FROM dbo.[zzHilfstabelle->Prov LastProv1]

if @ProviKind=0
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE (RESELLER.RESELLER_PAYPROVI=1)
AND (RESELLER.RESELLER_PROVMONTH=0
AND (RESELLER.RESELLER_ISHV=0))
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT,[RESELLER_WHENNEW],RESELLER_ProvFrom
HAVING (((RESELLER.RESELLER_ID)<>11))
end
if @ProviKind=5
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE RESELLER.RESELLER_PAYPROVI=1
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID=@SpzProvi
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;

end
if @ProviKind=1
begin
---INSERT INTO [zzHilfstabelle->Prov LastProv1]()
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 as isGWK, @ProviKind AS monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE ( (RESELLER.RESELLER_PAYPROVI=1)
AND RESELLER.RESELLER_PROVMONTH= 1

)
AND RESELLER.RESELLER_ISHV=0
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID<>11
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;
end
 
Babsi schrieb:
Wenn ich in einem neuen SQL Fenster folgendes eingebe und ProviKind entsprechend des Access Programms ändere klappt alles :(
DECLARE @DateTo datetime,
@ProviKind as int,
@SpzProvi as int
set @DateTo = '30.07.2024'
set @ProviKind= 5
set @SpzProvi = 2019

DELETE FROM dbo.[zzHilfstabelle->Prov LastProv1]

if @ProviKind=0
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE (RESELLER.RESELLER_PAYPROVI=1)
AND (RESELLER.RESELLER_PROVMONTH=0
AND (RESELLER.RESELLER_ISHV=0))
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT,[RESELLER_WHENNEW],RESELLER_ProvFrom
HAVING (((RESELLER.RESELLER_ID)<>11))
end
if @ProviKind=5
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE RESELLER.RESELLER_PAYPROVI=1
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID=@SpzProvi
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;

end
if @ProviKind=1
begin
---INSERT INTO [zzHilfstabelle->Prov LastProv1]()
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 as isGWK, @ProviKind AS monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE ( (RESELLER.RESELLER_PAYPROVI=1)
AND RESELLER.RESELLER_PROVMONTH= 1

)
AND RESELLER.RESELLER_ISHV=0
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID<>11
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;
end
Zum Vergrößern anklicken....
Also es wird für ProviKind =1,ProviKind= 0 und ProviKind =5 entsprechend in diese Tabelle geschrieben
 
Babsi schrieb:
ich mach da irgendwas falsch bei der If-Then-Else Konstruktion, denke ich..?!
Zum Vergrößern anklicken....
Also bei reinem MSSQL, und ich gehe mal davon aus das das auch auf Access zutrifft, empfiehlt es sich, IF / ELSE sauber zu kapseln. Ich nutze immer IF BEGIN END ELSE BEGIN END. Du hast jetzt in deinem letzten Post kein ELSE drin, davor ELSE IF, was aber aus meiner Sicht eigentlich laufen müsste.
 
ukulele schrieb:
Also bei reinem MSSQL, und ich gehe mal davon aus das das auch auf Access zutrifft, empfiehlt es sich, IF / ELSE sauber zu kapseln. Ich nutze immer IF BEGIN END ELSE BEGIN END. Du hast jetzt in deinem letzten Post kein ELSE drin, davor ELSE IF, was aber aus meiner Sicht eigentlich laufen müsste.
Zum Vergrößern anklicken....
Hallo ukulele, ja, das letzte läuft ja auch. Aber nur in dem gekappselten SQL, Wo ich händisch die Werte für
@ProviKind as int,
@SpzProvi as int
set @DateTo = '30.07.2024'
set @ProviKind= 5
set @SpzProvi = 2019

aber das Programm geht ja weiter udn irgendwie scheint die Auswertung nicht zu klappen also dieses
If-Then- else.

Sobald ich den Teil mit @SpzProvi = 2019 asuführen will klappt nichts mehr, Der löscht ja noch nicht einmal aus den Tabellen...
 
Ich habe das jetzt mal geändert auf

if @SpzProvi <> 0 and @ProviKind=5
begin

INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE RESELLER.RESELLER_PAYPROVI=1
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID=@SpzProvi
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC

end
else if @ProviKind=0
begin

INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche,


und der springt schon gar nicht hierrein
if @SpzProvi <> 0 and @ProviKind=5

Kann es sein, dass es hier darn liegt?

Set param1 = cmd.CreateParameter("@DateFrom", 7, 1, , DateValue(dBegin))
Set param2 = cmd.CreateParameter("@DateTo", 7, 1, , DateValue(dEnd))
Set kindofProvi = cmd.CreateParameter("@ProviKind", 16, 1, , kindofProvValue)
Set lResID = cmd.CreateParameter("@SpzProvi", 16, 1, , lResIDValue)
' parameters to the command
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append kindofProvi
cmd.Parameters.Append lResID
 
Moin ukulele,

leider nein, so wei folgt werden
PoviKind=0 und ProviKind=1 ausgeführt, der erste jedoch nicht

if @SpzProvi <> 0 and @ProviKind=5
Begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche , RESELLER.RESELLER_PROVMONTH,
0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE RESELLER.RESELLER_PAYPROVI=1
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID=@SpzProvi
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC

end
else
begin
if @ProviKind=0
begin
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 AS isGWK, @ProviKind as monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE (RESELLER.RESELLER_PAYPROVI=1)
AND (RESELLER.RESELLER_PROVMONTH=0
AND (RESELLER.RESELLER_ISHV=0))
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV, RESELLER.RESELLER_SHORT,[RESELLER_WHENNEW],RESELLER_ProvFrom
HAVING (((RESELLER.RESELLER_ID)<>11))
end
else
begin
---INSERT INTO [zzHilfstabelle->Prov LastProv1]()
INSERT INTO [zzHilfstabelle->Prov LastProv1] ( RESELLER_ID, isGWK,monatliche, RESELLER_PROVMONTH, HVs, RESELLER_ISHV, [Last], Angelegt, bis, RESELLER_SHORT )
SELECT DISTINCT RESELLER.RESELLER_ID, 0 as isGWK, @ProviKind AS monatliche, RESELLER.RESELLER_PROVMONTH, 0 AS HVs, RESELLER.RESELLER_ISHV,
Max(
IIf([PROVAB_DATE] IS NUll,
IIf([RESELLER_ProvFrom]IS Null,
IIf([RESELLER_WHENNEW] IS Null Or ([RESELLER_WHENNEW] < CAST(CONCAT('31.12.' , Year(Getdate())-5 )AS DATE)),CAST(CONCAT('31.12.' , Year(Getdate())-5 ) AS DATE),
[RESELLER_WHENNEW]
),[RESELLER_ProvFrom]
),[PROVAB_DATE])
)AS [Last],
Min(IIf(([RESELLER_WHENNEW]IS NULL),CAST('01.01.2000' AS Date),[RESELLER_WHENNEW])) AS Angelegt,
@DateTo AS bis, RESELLER.RESELLER_SHORT
FROM data.RESELLER LEFT JOIN [ZV Provision -> 00 letzte Rechnung hilf] ON RESELLER.RESELLER_ID = [ZV Provision -> 00 letzte Rechnung hilf].RESELLER_ID
WHERE ( (RESELLER.RESELLER_PAYPROVI=1) AND RESELLER.RESELLER_PROVMONTH= 1)AND RESELLER.RESELLER_ISHV=0
GROUP BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,RESELLER.RESELLER_SHORT
HAVING RESELLER.RESELLER_ID<>11
ORDER BY RESELLER.RESELLER_ID, RESELLER.RESELLER_PROVMONTH, RESELLER.RESELLER_ISHV,
RESELLER.RESELLER_SHORT DESC;

end
end
 
Die Prozedur nimmt den Wert für KindProvi=5 nicht an. Selbst wenn ich nur 5 als Wert übergebe, Wir nichts ausgeführt.
Da stimmt doch was mit der Übergaben nicht.
Das verstehe ich nicht, da die selbe Variable ja für die übergebenen Werte 0 || 1 läuft.

Das doofe ist, das ich den Code auf dem SQL Server ja in dem Sinne nicht anhalten kann um zu sehen, was für Werte die Variablen haben.
 
Alles funktioniert wenn ich die eine ID nicht übergebe

Set param1 = cmd.CreateParameter("@DateFrom", 7, 1, , DateValue(dBegin))
Set param2 = cmd.CreateParameter("@DateTo", 7, 1, , DateValue(dEnd))
Set kindofProvi = cmd.CreateParameter("@ProviKind", 16, 1, , kindofProvValue)
'Set lResID = cmd.CreateParameter("@SpzProvi", 16, 1, , lResIDValue)
' parameters to the command
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append kindofProvi
' cmd.Parameters.Append lResID

:eek:
 
Babsi schrieb:
Alles funktioniert wenn ich die eine ID nicht übergebe

Set param1 = cmd.CreateParameter("@DateFrom", 7, 1, , DateValue(dBegin))
Set param2 = cmd.CreateParameter("@DateTo", 7, 1, , DateValue(dEnd))
Set kindofProvi = cmd.CreateParameter("@ProviKind", 16, 1, , kindofProvValue)
'Set lResID = cmd.CreateParameter("@SpzProvi", 16, 1, , lResIDValue)
' parameters to the command
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append kindofProvi
' cmd.Parameters.Append lResID

:eek:
Zum Vergrößern anklicken....
Also die lResID nicht übergebe
 
Werbung:
Es funktioniert nun.
Ich habe für
  • Set lResID = cmd.CreateParameter("@SpzProvi", 16, 1, , lResIDValue)
das
  • Set lResID = cmd.CreateParameter("@SpzProvi", 18, 1, , lResIDValue)
eingegeben
 

Ähnliche Themen

B
IIF vs. Where Bedingung
Antworten
7
Aufrufe
281
ukulele
ukulele
B
Access Migration auf SQL Server
Antworten
8
Aufrufe
367
Babsi
B
B
Abfrage SQL Server vs. MS Access, unterschidliche Ergebnisse
Antworten
11
Aufrufe
129
Babsi
B
MegaV0lt
'Case when' und Teilstring
Antworten
13
Aufrufe
474
MegaV0lt
MegaV0lt
T
Probleme mit erster procedure
Antworten
8
Aufrufe
353
akretschmer
akretschmer
Zurück
Oben