Saludos, tengo un gran problemon, ojala alguien me pueda ayudar, en la base de datos de una empresa tengo que correr un sp, el problema es que no me dejan instalar un programa administrador de base de datos como el iboconsole o el firebird maestro. entonces lo que siempre hemos hecho es crear aplicaciones en delphi que me corran todos los querys como los de crear tablas y alterarlas y eso. Pero se me presenta el problema con un sp, corriendolo desde un administrador d base de datos jala perfectamente pero a la hora de correrlo desde delphi me genera errores por la sintaxis del sql para firebird, entonces que es lo que le tengo que corregir o como arreglar el sp para que corra desde delphi.
Código SQL
[-]SET TERM ^ ;
RECREATE PROCEDURE SP_REP_ANUAL4 (
FECHAFIN DATE)
RETURNS (
MN INTEGER,
MUH INTEGER,
MUO INTEGER,
REC INTEGER,
ACU INTEGER,
MAHN INTEGER,
MAHU INTEGER,
OAHN INTEGER,
AMOPH FLOAT,
AMOPO FLOAT,
AMOGH FLOAT,
AMOGO FLOAT,
AMOIH FLOAT,
AMOIO FLOAT,
ARPH FLOAT,
ARPO FLOAT,
AOH FLOAT,
AOO FLOAT,
RORPU FLOAT,
AORPU FLOAT,
RORPI FLOAT,
AORPI FLOAT,
RORG FLOAT,
AORG FLOAT,
RORI FLOAT,
AORI FLOAT,
RMEN FLOAT,
AMEN FLOAT,
RMAY FLOAT,
AMAY FLOAT,
ROT FLOAT,
AOT FLOAT,
CMOP FLOAT,
CMOI FLOAT,
CMOG FLOAT,
GF FLOAT,
GS FLOAT,
MPH INTEGER,
SPH INTEGER,
RGH INTEGER,
IPEH INTEGER,
IAH INTEGER,
OT1H INTEGER,
RGAH INTEGER,
TPH INTEGER,
OT2H INTEGER,
MPO INTEGER,
SPO INTEGER,
RGO INTEGER,
SIO INTEGER,
TPO INTEGER,
OT3O INTEGER,
MEC INTEGER,
SER INTEGER,
TC INTEGER)
AS
BEGIN
FOR
SELECT
(SELECT COUNT(IDVTAS) FROM HONDATA WHERE IDVTAS = 1 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS MN,
(SELECT COUNT(IDVTAS) FROM HONDATA WHERE IDVTAS = 2 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS MUH,
(SELECT COUNT(IDVTAS) FROM HONDATA WHERE IDVTAS = 3 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS MUO,
COUNT(H.RECOR) AS REC,
COUNT(H.ACUDIO) AS ACU,
(SELECT COUNT(IDVISITA) FROM HONDATA WHERE IDVISITA = 1 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS MAHN,
(SELECT COUNT(IDVISITA) FROM HONDATA WHERE IDVISITA = 2 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS MAHU,
(SELECT COUNT(IDVISITA) FROM HONDATA WHERE IDVISITA = 3 AND EXTRACT(YEAR FROM(FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))) AS OAHN,
SUM(H.AMOPH) AS AMOPH,
SUM(H.AMOPO) AS AMOPO,
SUM(H.AMOGH) AS AMOGH,
SUM(H.AMOGO) AS AMOGO,
SUM(H.AMOIH) AS AMOIH,
SUM(H.AMOIO) AS AMOIO,
SUM(H.ARPH) AS ARPH,
SUM(H.ARPO) AS ARPO,
SUM(H.AOH) AS AOH,
SUM(H.AOO) AS AOO,
SUM(H.RORPU) AS RORPU,
SUM(H.AORPU) AS AORPU,
SUM(H.RORPI) AS RORPI,
SUM(H.AORPI) AS AORPI,
SUM(H.RORG) AS RORG,
SUM(H.AORG) AS AORG,
SUM(H.RORI) AS RORI,
SUM(H.AORI) AS AORI,
SUM(H.RMEN) AS RMEN,
SUM(H.AMEN) AS AMEN,
SUM(H.RMAY) AS RMAY,
SUM(H.AMAY) AS AMAY,
SUM(H.ROT) AS ROT,
SUM(H.AOT) AS AOT,
SUM(H.CMOP) AS CMOP,
SUM(H.CMOI) AS CMOI,
SUM(H.CMOG) AS CMOG,
SUM(H.GF) AS GF,
SUM(H.GS) AS GS,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =1) AS MPh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =2) AS SPh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =3) AS RGh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =4) AS IPEh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =5) AS IAh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =6) AS OT1h,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =7) AS RGAh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =8) AS TPh,
(SELECT COUNT(S.IDSERVH) FROM SERVMH S WHERE S.IDSERVH =9) AS OT2h,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 1) AS MPo,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 2) AS SPo,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 3) AS RGo,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 4) AS SIo,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 5) AS TPo,
(SELECT COUNT(S.IDSERVO) FROM SERVMO S WHERE S.IDSERVO = 6) AS OT3o,
(SELECT COUNT(R.IDRETSER) FROM RETMR R WHERE R.IDRETSER = 1) AS MEC,
(SELECT COUNT(R.IDRETSER) FROM RETMR R WHERE R.IDRETSER = 2) AS SER,
(SELECT COUNT(R.IDRETSER) FROM RETMR R WHERE R.IDRETSER = 3) AS TC
FROM ENCAOR E, HONDATA H
WHERE E.TIPO <> 'C' AND E.ORDE = H.ORDE AND EXTRACT(YEAR FROM (H.FECHORDE)) = EXTRACT(YEAR FROM(:FECHAFIN))
INTO :MN, :MUH, :MUO, :REC, :ACU, :MAHN, :MAHU, :OAHN,
:AMOPH, :AMOPO, :AMOGH, :AMOGO, :AMOIH, :AMOIO, :ARPH,
:ARPO, :AOH, :AOO, :RORPU, :AORPU, :RORPI, :AORPI, :RORG,
:AORG, :RORI, :AORI, :RMEN, :AMEN, :RMAY, :AMAY, :ROT,
:AOT, :CMOP, :CMOI, :CMOG, :GF, :GS, :MPh, :SPh, :RGh,
:IPEh, :IAh, :OT1h, :RGAh, :TPh, :OT2h, :MPo, :SPo,
:RGo, :SIo, :TPo, :OT3o, :MEC, :SER, :TC
DO SUSPEND;
END^
SET TERM ; ^
por ejemplo me da error con lo del set term, me dice ke no existe. Ojala alguien me pueda ayudar, no se si explique bien lo que me pasa, hagan preguntas para esclarecer algo que no me halla explicado bien.