Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

procedure erroe

hi there,
getting error below while running procedure via command editor, Anu idea whats wrong?

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE dmart_mkt1.spGenDates1 ( IN in_StartDT DATE,IN in_EndDt DATE)
SPECIFIC spGenDates1
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
 DECLARE DateVar DATE;
 DECLARE v_counter INTEGER DEFAULT 0;
 if in_StartDT is null then
   SET in_StartDT = Date('1972-01-01');
 end if;

 if in_EndDt is null then
    SET in_StartDT = current_date;
 end if;

 SET DateVar = in_StartDT;

 insert_loop:
 LOOP
   IF days(DateVar) > days(in_EndDt) THEN
      LEAVE insert_loop;
   end if;
     INSERT INTO DMART_MKT1.STG_DATE_TEST (FISCAL_YR
      ,QUARTER_ID
      ,FISCAL_QTR
      ,QUARTER_DISPLAY
      ,MONTH_ID
        ,FISCAL_MTH
      ,DAYS_IN_MONTH
      ,MONTH_NAME
      ,DATE_DISPLAY
      ,DAY_OF_WEEK
      ,DAY_OF_MONTH
      ,DAY_OF_YEAR
        ,WEEK_OF_MONTH
      ,WEEK_OF_QUARTER
      ,WEEK_OF_YEAR
      ,WEEKDAY
      ,FISCAL_DATE
      ,ETL_LAST_UPD_DT
      ,ETL_CREATE_DT
      ,ETL_ARCHIVED_REC)
     VALUES (YEAR(DateVar),
      INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(quarter(DateVar))))),
      quarter(DateVar),
      concat('Q',rtrim(char(quarter(DateVar)))),
      INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(month(DateVar))))),
      month(DateVar),
      day((DateVar +1 month -day(DateVar) day)) ,
      monthname(DateVar),
      CHAR(DateVar,iso),
      DAYOFWEEK(DateVar),
      DAY(DateVar),
      DAYOFYEAR(DateVar),
      WEEK(DateVar) - WEEK((DateVar - day(DateVar) day +1 day)) + 1,
      WEEK(DateVar) - Week(DateVar  - DAYOFYEAR(DateVar) DAYS + ((QUARTER(DateVar ) - 1) * 3) MONTHS + 1 DAYS) ,
      WEEK(DateVar),DAYNAME(DateVar),DateVar,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'N');
   SET dateVar = dateVar + 1 days;
 END LOOP insert_loop;
END;
------------------------------------------------------------------------------
CREATE PROCEDURE dmart_mkt1.spGenDates1 ( IN in_StartDT DATE,IN in_EndDt DATE)
SPECIFIC spGenDates1
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE DateVar DATE
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "DECLARE
DateVar DATE".  Expected tokens may include:  "<psm_semicolon>".  LINE
NUMBER=6.  SQLSTATE=42601

DECLARE v_counter INTEGER DEFAULT 0
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "0" was found following "NTER INTEGER DEFAULT".  
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

if in_StartDT is null then SET in_StartDT = Date('1972-01-01')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "if in_StartDT is null then" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

if in_EndDt is null then SET in_StartDT = current_date
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "if in_EndDt is null then" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SET DateVar = in_StartDT
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DateVar" was found following "SET ".  Expected
tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

insert_loop: LOOP IF days(DateVar) > days(in_EndDt) THEN LEAVE insert_loop
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "insert_loop: LOOP IF days(DateVar) >" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<create_table>".  SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

INSERT INTO DMART_MKT1.STG_DATE_TEST (FISCAL_YR ,QUARTER_ID ,FISCAL_QTR ,QUARTER_DISPLAY ,MONTH_ID ,FISCAL_MTH ,DAYS_IN_MONTH ,MONTH_NAME ,DATE_DISPLAY ,DAY_OF_WEEK ,DAY_OF_MONTH ,DAY_OF_YEAR ,WEEK_OF_MONTH ,WEEK_OF_QUARTER ,WEEK_OF_YEAR ,WEEKDAY ,FISCAL_DATE ,ETL_LAST_UPD_DT ,ETL_CREATE_DT ,ETL_ARCHIVED_REC) VALUES (YEAR(DateVar), INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(quarter(DateVar))))), quarter(DateVar), concat('Q',rtrim(char(quarter(DateVar)))), INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(month(DateVar))))), month(DateVar), day((DateVar +1 month -day(DateVar) day)) , monthname(DateVar), CHAR(DateVar,iso), DAYOFWEEK(DateVar), DAY(DateVar), DAYOFYEAR(DateVar), WEEK(DateVar) - WEEK((DateVar - day(DateVar) day +1 day)) + 1, WEEK(DateVar) - Week(DateVar  - DAYOFYEAR(DateVar) DAYS + ((QUARTER(DateVar ) - 1) * 3) MONTHS + 1 DAYS) , WEEK(DateVar),DAYNAME(DateVar),DateVar,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'N')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "DATEVAR" is not valid in the context where it is used.  
SQLSTATE=42703

SET dateVar = dateVar + 1 days
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "SET dateVar =" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  
SQLSTATE=42601

END LOOP insert_loop
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "LOOP" was found following "END ".  Expected
tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  Expected tokens may include:  "JOIN <joined_table>                              ".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.  

 As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".  
This list assumes the statement is correct to that point.  

 The statement cannot be processed.  

User Response:

Examine and correct the statement in the area of the specified
token.  

 sqlcode :  -104

 sqlstate :  42601
0
nav29
Asked:
nav29
  • 3
1 Solution
 
momi_sabagCommented:
sure, you need to change your command terminator to be something else than ;
for example #
you can do it using the switch parameters of the db2 command, i think it's the -t switch
0
 
momi_sabagCommented:
by command editor you mean the gui tool?
if so, search under options for the chatacter that terminates a commnd (command separator) and change it from ; to something else
0
 
momi_sabagCommented:
from the command line
db2 -td$ will make $ to be the terminator
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now