How to merge this two firebird select procedure using this REFERENCE variable thru if else, case, or other method. If REFERENCE = 1 then the procedure 1 will display, if REFERENCE = 2 then the procedure 2 will display. I am trying to have 1 select procedure with conditions rather than 2 procedure.
CREATE PROCEDURE PRINT_NON_REF1(
M VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
Y VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
REFERENCE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
RETURNS(
AP_PSTIONLVL_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
AP_POSTION_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
RANKING_MONTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
RANKING_YEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
AS
BEGIN
FOR
SELECT
'',
'',
RANKING_MONTH,
RANKING_YEAR
FROM APPLICANT
WHERE RANKING_MONTH = :M AND RANKING_YEAR = :Y
GROUP BY
RANKING_MONTH,
RANKING_YEAR
INTO
:AP_PSTIONLVL_NON,
:AP_POSTION_NON,
:RANKING_MONTH,
:RANKING_YEAR
DO
BEGIN
SUSPEND;
END
END;
and
CREATE PROCEDURE PRINT_NON_REF2(
M VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
Y VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
REFERENCE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
RETURNS(
AP_PSTIONLVL_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
AP_POSTION_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
RANKING_MONTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
RANKING_YEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
AS
BEGIN
FOR
SELECT
AP_PSTIONLVL_NON,
AP_POSTION_NON,
RANKING_MONTH,
RANKING_YEAR
FROM APPLICANT
WHERE RANKING_MONTH = :M AND RANKING_YEAR = :Y
GROUP BY
AP_PSTIONLVL_NON,
AP_POSTION_NON,
RANKING_MONTH,
RANKING_YEAR
INTO
:AP_PSTIONLVL_NON,
:AP_POSTION_NON,
:RANKING_MONTH,
:RANKING_YEAR
DO
BEGIN
SUSPEND;
END
END;
You may try a construct like this:
WITH
Q_2 as (
SELECT
AP_PSTIONLVL_NON,
AP_POSTION_NON,
RANKING_MONTH,
RANKING_YEAR
FROM APPLICANT
WHERE RANKING_MONTH = :M
AND RANKING_YEAR = :Y
GROUP BY
AP_PSTIONLVL_NON,
AP_POSTION_NON,
RANKING_MONTH,
RANKING_YEAR
),
Q_1 as (
SELECT
'',
'',
RANKING_MONTH,
RANKING_YEAR
FROM APPLICANT
WHERE RANKING_MONTH = :M
AND RANKING_YEAR = :Y
GROUP BY
RANKING_MONTH,
RANKING_YEAR
)
SELECT * FROM Q_2 WHERE :REFERENCE=2
UNION ALL
SELECT * FROM Q_1 WHERE :REFERENCE=1
Things to notice:
SELECT
is bad idea in Firebird/Interbase. It will disable SQL optimizer job on joining, and will add nothing of value. Firebird is not MS SQL.union
ed chain, because it is the first query where UNION
takes column names and data types from. Would Q_1
go first - the two anonymous columns would be a problem.:M
into :M_1
and :M_2
, etc. Or maybe not, test your library.:name
used above. Especially, that Firebird itself on SQL level only supports unnamed parameters. Check your library documentation. You might also get insights reading code examples at https://bobby-tables.com/UNION
and CTE (Common Table Expressions) at https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html