ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Proc Parameter Question

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Proc Parameter Question

    Hi Everyone

    I am trying to get a paramter to be a literal string in a table join for the view that the procedure creates.
    For example I pass in 'D01PAYE1T', 'D01' for the parameters.
    The @PAYLIB works great, it just I can figure out the single quote logic to force the @District to be considered a string in this statement. Wondering if anyone can help me out here.
    Code:
    CREATE PROCEDURE TRANSFER.F5560116JA_CREATE_PROC 
    (IN @PAYLIB VARCHAR(10), IN @District VARCHAR(3) )
    BEGIN 
    DECLARE STMT1 VARCHAR ( 8000 ) ; 
    SET STMT1 = 'Create View ' || @PAYLIB || '.F5560116JA as SELECT DISTINCT F060116.YAAN8 FROM ' || @PAYLIB || '.F060116 AS 
    F060116  LEFT OUTER JOIN D99HRISDV.F56HRISFID F56HRISFID ON ''' ||  @District || ''' RIGHT(''00000'' || F060116.YAAN8,5) = F56HRISFID.GIN'
    EXECUTE IMMEDIATE STMT1 ; 
    END;
    The error I get when I run this is Message: [SQL0010] String constant beginning ', ?)
    Last edited by zschulz; November 24, 2009, 11:52 AM.

  • #2
    Re: SQL Proc Parameter Question

    In a first time create your procedure like this :
    Code:
    CREATE PROCEDURE TRANSFER.F5560116JA_CREATE_PROC 
    (IN @PAYLIB VARCHAR(10), IN @District VARCHAR(3) )
    	LANGUAGE SQL 
    	NOT DETERMINISTIC 
    	MODIFIES SQL DATA 
    	COMMIT = *NONE , 
    	DYNUSRPRF = *OWNER , 
    	BEGIN 
    DECLARE STMT1 VARCHAR ( 8000 ) ; 
    SET STMT1 = 'Create View ' || @PAYLIB || '.F5560116JA as SELECT DISTINCT F060116.YAAN8 FROM ' || @PAYLIB || '.F060116 AS 
    F060116  LEFT OUTER JOIN D99HRISDV.F56HRISFID F56HRISFID ON ''' ||  @District || ''' RIGHT(''00000'' || F060116.YAAN8,5) = F56HRISFID.GIN';
    EXECUTE IMMEDIATE STMT1 ; 
    END;
    Do you call it like this : CALL TRANSFER.F5560116JA_CREATE_PROC('D01PAYE1T', 'D01'); ???
    Patrick

    Comment


    • #3
      Re: SQL Proc Parameter Question

      Thanks K2r400 for answering so quick.
      I just simplified the Procedure so you would get the idea. It does create the proc, so I am not running into any syntax issues in the proc.
      The syntax issue I am running into is when I call the procedure to create the view.
      CALL TRANSFER.F5560116JA_CREATE_PROC ('D01PAYE1T', 'D01')

      Here is there error I am getting now. Found an issue with my call hopefully this explains better.
      SQL State: 42601
      Vendor Code: -199
      Message: [SQL0199] Keyword RIGHT not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. Cause . . . . . : The keyword RIGHT was not expected here. A syntax error was detected at keyword RIGHT. The partial list of valid tokens is < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

      Comment


      • #4
        Re: SQL Proc Parameter Question

        ...ON ''' || @District || ''' RIGHT(''00000''...
        In fact there is a problem here :

        ON 'D01' CONCAT RIGHT....

        you need a concat between your field and the instruction RIGHT
        Patrick

        Comment


        • #5
          Re: SQL Proc Parameter Question

          Thanks again for responding
          I thought || is the same as concat. Let me know how it's different?
          I am a newbie, so any advice would go along way

          Comment


          • #6
            Re: SQL Proc Parameter Question

            if you prefer using || :

            Code:
            SET STMT1 = 'Create View ' || @PAYLIB || 
            '.F5560116JA as SELECT DISTINCT F060116.YAAN8 FROM ' 
            || @PAYLIB || 
            '.F060116 AS F060116  LEFT OUTER JOIN D99HRISDV.F56HRISFID F56HRISFID ON ' ||  
            ''' || @District || ''' || 'RIGHT('''00000''' || F060116.YAAN8,5) = F56HRISFID.GIN';
            EXECUTE IMMEDIATE STMT1 ;
            Look before the RIGHT operation and add one quote before RIGHT and the constant 00000
            The real stattement attemps is :

            ON 'D01' || RIGHT('0000' || F060116.YAAN8, 5) no ?

            also, if your field F060116.YAAN8 is numeric or decimal, prefer the DIGITS function
            Last edited by K2r400; November 24, 2009, 12:28 PM.
            Patrick

            Comment


            • #7
              Re: SQL Proc Parameter Question

              Another possibility to clarify, replace a quote by # (or other) and after replace with a quote

              SET STMT1 = 'Create View '
              || @PAYLIB ||
              '.F5560116JA as SELECT DISTINCT F060116.YAAN8 FROM '
              || @PAYLIB ||
              '.F060116 AS F060116 LEFT OUTER JOIN D99HRISDV.F56HRISFID F56HRISFID ON #'
              || @District ||
              '# CONCAT RIGHT(#00000# CONCAT F060116.YAAN8, 5) = F56HRISFID.GIN';

              SET STMT1 = replace(STMT1, '#', '''');

              EXECUTE IMMEDIATE STMT1 ;
              Last edited by K2r400; November 24, 2009, 12:41 PM.
              Patrick

              Comment


              • #8
                Re: SQL Proc Parameter Question

                Thanks again K2R400

                I tried both suggestions.
                Code:
                ' || ''' ||  @District || ''' || ' RIGHT(''00000''
                This gives me
                SQL State: 42601
                Vendor Code: -199
                Message: [SQL0199] Keyword RIGHT not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT.

                Code:
                ' || ''' ||  @District || ''' || ' RIGHT('''00000'''
                This gives me
                SQL State: 42601
                Vendor Code: -104
                Message: [SQL0104] Token 00000 was not valid. Valid tokens: ;.

                Were you able to get this to work on your side?

                Comment


                • #9
                  Re: SQL Proc Parameter Question

                  it's very difficult to work with multiple quote.
                  Try the second solution with the REPLACE function
                  Last edited by K2r400; November 24, 2009, 12:55 PM.
                  Patrick

                  Comment


                  • #10
                    Re: SQL Proc Parameter Question

                    Hi K2r400

                    I think a great way to troubleshoot this would be to get the SQL script, so instead of getting the PROC executed is there a way to display the output from STMT1?

                    Comment


                    • #11
                      Re: SQL Proc Parameter Question

                      Originally posted by zschulz
                      ...is there a way to display the output from STMT1?
                      • Create the CL program below
                      • Create the SP that wraps the CL
                      • Call the new SP from inside the relevant one


                      Code:
                      [U]CL SNDPGMMSGC[/U]
                      pgm &msg
                      dcl &msg *char 512
                      qsys/sndpgmmsg &msg
                      endpgm
                      Code:
                      [U]Stored Procedure SNDPGMMSGP[/U]
                      Drop Procedure MYLIB.SNDPGMMSGP;
                      
                      CREATE PROCEDURE MYLIB.SNDPGMMSGP(IN Msg CHAR (512 )) 
                      LANGUAGE CL 
                      NOT DETERMINISTIC 
                      NO SQL 
                      EXTERNAL NAME SNDPGMMSGC 
                      PARAMETER STYLE GENERAL;
                      Insert the CALL statement as shown below in the SP
                      Code:
                      SET STMT1 = 'Create View ...
                      [B]CALL SNDPGMMSGP ( STMT1 );[/B]
                      EXECUTE IMMEDIATE STMT1 ;
                      Run the SP then check the job log to find out how the sql stm looks like.
                      Attached Files
                      Last edited by Mercury; November 24, 2009, 06:00 PM. Reason: typo
                      Philippe

                      Comment


                      • #12
                        Re: SQL Proc Parameter Question

                        ... if you are already working with dynamic SQL, why not simply defining a Variable which is initialized with a quote and concat this variable within your statement.

                        BTW just for information, || should be avoided and concat prefered, because || is not international. In some systems you need to code !! instead.

                        Birgitta

                        Comment


                        • #13
                          Re: SQL Proc Parameter Question

                          Originally posted by zschulz View Post
                          Hi K2r400

                          I think a great way to troubleshoot this would be to get the SQL script, so instead of getting the PROC executed is there a way to display the output from STMT1?
                          Did you resolve your problem ?
                          What contains SMT1 ?
                          Did you use REPLACE how I proposed it to you ?
                          Patrick

                          Comment


                          • #14
                            Re: SQL Proc Parameter Question

                            The K2r400's REPLACE function is another way to resolve the quote issue.

                            I was used to use a variable with a defined value of x'7D' (quote) to concat within the sql stm where needed but Birgitta doesn't like that coz x'7D' is not an international coding representation of the quote so I gave up and define henceforth a variable initialized with a quote as she noted.
                            Philippe

                            Comment


                            • #15
                              Re: SQL Proc Parameter Question

                              Thanks Everyone

                              I was weary of trying the replace function solution because it didnt seem like it would do anything that I was not already trying; however, because of the responses I gave it a try and it worked.
                              The following code worked perfectly thanks again K2R400. Sorry for doubting the one solution.
                              I actually am wondering now why the replace function works, does not make sense to me that putting the correct amount of single quotes into the statement would not work but using a replace to put the same amount does work.
                              As well something I also learned from what you showed is that || = CONCAT. I thought when using concat you would have to always go CONCAT(string,string).

                              Thanks again

                              Code:
                              SET STMT1 = 'Create View ' 
                              || @PAYLIB || 
                              '.F5560116JA as SELECT DISTINCT F060116.YAAN8 FROM ' 
                              || @PAYLIB || 
                              '.F060116 AS F060116 LEFT OUTER JOIN D99HRISDV.F56HRISFID F56HRISFID ON #' 
                              || @District || 
                              '# CONCAT RIGHT(#00000# CONCAT F060116.YAAN8, 5) = F56HRISFID.GIN';
                              
                              SET STMT1 = replace(STMT1, '#', '''');
                              
                              EXECUTE IMMEDIATE STMT1 ;

                              Comment

                              Working...
                              X