Stored proc im view verwenden

[messie]

Moin,

ich habe eine stored proc geschrieben, die ich gerne über einen View aufrufen möchte:

Code:

SET TERM ^ ;
ALTER PROCEDURE DEVICECOUNT (
    ID Integer )
RETURNS (
    D_COUNT Integer )
AS
DECLARE VARIABLE COUNTER integer; 
BEGIN
  D_COUNT = 0;
  FOR 
    SELECT POSITION.NUMBER_OF_DEVICES from  POSITION
    where POSITION.ORDER_ID = ID
    INTO :COUNTER
  DO BEGIN
    D_COUNT =: D_COUNT + COUNTER;
  END
  suspend;
END^
;

Die soll mir die Anzahl der Bauteile von n Unterpositionen liefern.

Im View rufe ich das so auf:

Code:

CREATE VIEW V_JOB_OVERVIEW (ORDER_NO, POSITIONEN_COUNT, DEVICES_COUNT)
AS   
select 
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT.D_COUNT

from 
AUFTRAG, DEVICECOUNT(AUFTRAG.ORDERNO)




SET TERM ; ^

Leider bekomme ich beim SELECT des View mit Flamerobin eine Fehlermeldung , dass es keine row zum fetchen gäbe und der Cursor auf eine ungültige row zeigt. Der Aufruf läuft sonst ohne Fehler durch.

Was habe ich da falsch gemacht?

Grüße, Messie

[jobo]

Ich bin kein Firebird Spezi und hätte zunächst auf "Function" statt "Procedure" getippt, aber vielleicht geht das so in Firebird.

Zu den Fehlermeldungen:
Ein Select <Value> ..Into <Variable> wirft z.B. auch in Oracle einen Fehler, wenn kein Datensatz da ist bzw. mehr als einer.

Die Funktionalität würdest Du auch mit so einer Abfrage erreichen:
Select sum(myField) from..
ohne dass Du es selbst zählst.

Das hätte auch den Vorteil, dass immer ein Ergebnis rauskommt.

[hoika]

Hallo,

komische View,
das ist doch kein richtiges SQL ?

select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT.D_COUNT
from
AUFTRAG, DEVICECOUNT(AUFTRAG.ORDERNO)

muss es nicht heissen

select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT(AUFTRAG.ORDERNO)
from
AUFTRAG

?

Heiko

[Sir Rufo]

Nein, weder noch.

AFAIK kann eine SP nicht als Tabelle angesprochen werden, auch wenn dort eine Abfrage generiert wird.
Und als Abfragefeld schon mal gar nicht.

Einzer denkbarer Workaround wäre eine temporäre Tabelle, die von der SP generiert wird.

[TBx]

Die von hoika angegebene Schreibweise ist korrekt.
Die SP kann wie eine Tabelle ausgelesen werden, da sie in diesem Fall selectable ist.
Allerdings stellt sich hier die Frage, warum eine SP und nicht einfach ein SubSelect? Der Inhalt der SP ließe sich ja über ein sum erschlagen.

[messie]

Hallo und Danke schon mal.

Das mit der sum-Funktion ist ein guter Ansatz da sich das bei Firebird wie ein einfaches select verhält.
Leider bekomme ich die Übergabe nicht mehr hin. Gestern hatte es funktioniert, da ich versehentlich den Namen einer Tabellenspalte als In-Variable vergeben hatte. Hat später die ganze DB geschrottet da sich die SP weder ändern noch löschen ließ.

Code:

CREATE
procedure TEST1
(ID_IN integer)
returns
(RESULT integer)
AS
BEGIN
  RESULT =
  (select sum(NUMBER_OF_DEVICES) from POSITIONEN
  where POSITIONEN.ORDER_ID = ID_IN);
  suspend;
END

Jetzt wird ID_IN als unknown angemeckert. Auch der Umweg über eine zusätzlich angelegte Variable klappt nicht. Ersetze ich ID_IN durch eine Zahl, bekomme ich die korrekte Tabelle raus.

Wie muss ich die Übergabe korrekt ausführen?

Den View probiere ich dann später.

Grüße, Messie

[Neumann]

Da fehlt der Doppelpunkt vor dem ID_IN.

CREATE
procedure TEST1
(ID_IN integer)
returns
(RESULT integer)
AS
BEGIN
RESULT =
(select sum(NUMBER_OF_DEVICES) from POSITIONEN
where POSITIONEN.ORDER_ID = :ID_IN);
suspend;
END

Wenn da nur Create Procedure steht, muss man die "alte" Procedure erst löschen, sonst funktioniert es nicht.
Besser ist "CREATE OR ALTER PROCEDURE"

Ich würde hier allerdings auch keine Procedure verwenden, macht nicht viel Sinn für die doch einfache Abfrage.

Gruß

Ralf

[messie]

Zitat von Neumann:

Da fehlt der Doppelpunkt vor dem ID_IN.

Arrrghh...

Bezüglich des Views habe ich das Problem, dass die Syntax von Hoika nicht laufen mag

Code:

select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT(AUFTRAG.ORDERNO)
from
AUFTRAG

Da wird zunächst Function unknown gemeldet. Wenn ich DEVICECOUNT an from Auftrag anhänge, bekomme ich ein input parameter mismatch.

Nehme ich meine obige Syntax, komme ich dann zu einem Ergebnis wenn ich einen Wert explizit angebe:

Code:

CREATE VIEW V_JOB_OVERVIEW (ORDER_NO, POSITIONEN_COUNT, DEVICES_COUNT)
AS  
select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT.D_COUNT

from
AUFTRAG, DEVICECOUNT(0)

Der Wert 0 muss nicht vorhanden sein.
Muss ich da noch eine Variable vergeben oder ist mein Vorhaben doch nicht möglich?
Die Aufgabe: zu jedem Record in Table AUFTRAG können n Records in Table POSITIONEN gehören. Ich brauche die Summe aller Felder DEVICES_COUNT zu einem Auftrag. Ist das möglich?
Wie würde denn so etwas nur als view ohne SP aussehen?

Grüße, Messie

[jobo]

Die SP in der FromClause liefert immer einen Wert, der ungejoint mit der Hauptmenge "permutiert" wird.
Das macht hier nicht viel aus- da es nur ein Wert ist, macht aber auch keinen Sinn. Ist mir grad nicht aufgefallen.

Die Variante mit der From Clause würde Sinn machen, wenn die SP eine echte Datenmenge liefert. Weiß nicht ob firebird das kann.
Die Variante mit dem Aufruf als "Funktion" würde mehr Sinn machen, aber ich krieg da wie gesagt schon Probleme, wenn das Ding als Procedure und nicht als Function deklariert ist, wie auch immer,
so sollte ein View funktionieren:

Code:

create view V_JOB_OVERVIEW as 
select a.ORDERNO, a.POSITIONENCOUNT, grp.NoDevs --, DEVICECOUNT(AUFTRAG.ORDERNO)
  from AUFTRAG a,
       (select p.ORDERNO, sum(NUMBER_OF_DEVICES) as NoDevs
          from POSITIONEN p
         group by ORDERNO) grp
 where a.ORDERNO = grp.ORDERNO;

[p80286]

Zitat von messie:

Bezüglich des Views habe ich das Problem, dass die Syntax von Hoika nicht laufen mag

Code:

select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT(AUFTRAG.ORDERNO)
from
AUFTRAG

Da wird zunächst Function unknown gemeldet.

dann solltest Du einmal überprüfen, ob es diese Function auch gibt, oder ob beim

Code:

Create

etwas daneben gegangen ist.

Zitat von messie:

Nehme ich meine obige Syntax, komme ich dann zu einem Ergebnis wenn ich einen Wert explizit angebe:

Code:

CREATE VIEW V_JOB_OVERVIEW (ORDER_NO, POSITIONEN_COUNT, DEVICES_COUNT)
AS  
select
AUFTRAG.ORDERNO, AUFTRAG.POSITIONENCOUNT, DEVICECOUNT.D_COUNT

from
AUFTRAG, DEVICECOUNT(0)

Wenn ich mich da nicht vertue liefert

Code:

DEVICECOUNT(0)

genau einen Wert, (sinnvoll oder nicht lassen wir mal außen vor) dann sollte dieses Statement auch funktionieren, falls es die Funktion gibt.

Der Wert 0 muss nicht vorhanden sein.
Muss ich da noch eine Variable vergeben oder ist mein Vorhaben doch nicht möglich?
Die Aufgabe: zu jedem Record in Table AUFTRAG können n Records in Table POSITIONEN gehören. Ich brauche die Summe aller Felder DEVICES_COUNT zu einem Auftrag. Ist das möglich?
Wie würde denn so etwas nur als view ohne SP aussehen?

[/QUOTE]

Code:

Select irgendwas
from Auftrag
    ,(select Order_ID,sum(NUMBER_OF_DEVICES) DSUM from POSITIONEN group by order_ID) as DeviceCount
where DeviceCount.Order_ID=Aufrtrag.Order_ID

So ungefähr sollte es funktionieren
Gruß
K-H