ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SELECT INTO invalid with stored procedure (SQL0109)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SELECT INTO invalid with stored procedure (SQL0109)

    Code:
    Create Procedure LPCAPPDEV.CF_PRODUCER_CD_LU_AC(
      IN p_lookup_type_id INTEGER,
      IN p_lookup_type CHAR(30),
      IN p_secured_type CHAR(1),
      IN p_ltuserid CHAR(10))
        Language SQL
        Specific LPCAPPDEV.CF_PRODUCER_CD_LU_AC
        Result Sets 0
        Modifies SQL Data    
    BEGIN
      DECLARE v_cf_105p02_cnt DECIMAL(3,0) DEFAULT 0;
      DECLARE v_cf_105p02 CHAR(10) DEFAULT 'CF_105P02';
      DECLARE v_newseqid INTEGER DEFAULT 0;
      DECLARE C1 CURSOR FOR
    
      SELECT COUNT(*)
        INTO v_cf_105p02_cnt
        FROM LPCFLEDEV.CF_105P02
      WHERE P2LUTYPEID = p_lookup_type_id;
    
      CASE
        WHEN v_cf_105p02_cnt = 0
        THEN CALL LPCAPPDEV.CF_PRODUCER_INCR_SEQID(v_cf_105p02, v_newseqid);
      END CASE;
    
      MERGE INTO LPCFLEDEV.CF_105P02 A
        USING(
          VALUES(p_lookup_type_id,
                 p_lookup_type,
                 p_secured_type,
                 p_ltuserid,
                 CHAR(DATE(CURDATE()),ISO))
                 )
          INSROW(P2LUTYPEID,
                 P2LUTYPE,
                 P2SECURED,
                 P2CRTUSER,
                 P2CRTDT)
        ON A.P2LUTYPEID = p_lookup_type_id
    
      WHEN MATCHED THEN UPDATE
    
        SET P2LUTYPE = p_lookup_type,
            P2SECURED = p_secured_type,
            P2CHGDT = CHAR(DATE(CURDATE()),ISO),
            P2CHGUSER = p_ltuserid
      WHEN NOT MATCHED THEN INSERT(
                                   P2LUTYPEID,
                                   P2LUTYPE,
                                   P2SECURED,
                                   P2CRTUSER,
                                   P2CRTDT)
                            VALUES(
                                   p_lookup_type_id,
                                   p_lookup_type,
                                   p_secured_type,
                                   p_ltuserid,
                                   CHAR(DATE(CURDATE()),ISO));
    END;
    Hello. This is one of my first post and I need some help. :-)

    I ran into a problem that I don't understand...

    SELECT COUNT(*)
    INTO v_cf_105p02_cnt
    FROM LPCFLEDEV.CF_105P02
    WHERE P2LUTYPEID = p_lookup_type_id;

    I get a SQL0109 error. INTO clause not allowed. What am I missing?

    Thank you.

  • #2
    I found my problem. I was declaring a cursor... :-(

    Comment

    Working...
    X