sqlstored-proceduresfirebirdprocedural-programmingfirebird-3.0

Merging multiple related firebird select procedure using If else or case method


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;

Solution

  • 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: