Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 error in Pl/1 program
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 12:19 am    Post subject: Db2 error in Pl/1 program
Reply with quote

Hi,

I have a DB2 Select statement

Code:
SELECT 'N' INTO :Output_node         
FROM table1 A, table 2 B
WHERE A.KEY = B.KEY AND
           B.Date BETWEEN DATE(:old_DATE) - :duration
                     AND DATE(:old_DATE) - 1 DAY;             


I am getting the following error while Bind.
Code:

SQLCODE=-401 
SQLSTATE=42818
TOKENS=       


Kindly help.
Back to top
View user's profile Send private message

venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 12:22 am    Post subject:
Reply with quote

duration can be 1 year or 5 day or 10 day.
old_date format: YYYY-MM-DD
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Tue Mar 15, 2011 12:46 am    Post subject:
Reply with quote

What is a -401?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 15, 2011 2:27 am    Post subject:
Reply with quote

Hello,

At the top of the page is a link to "IBM Manuals". Among them are some DB2 Messages & Codes manuals.

If you read something about the -401 that is not clear, post what you found and your doubt. Someone will be able to clarify.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Mar 15, 2011 2:31 am    Post subject:
Reply with quote

Show the DDL for TABLE1 and TABLE2, and the declarations for the host variables OLD_DATE and DURATION.
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 7:45 am    Post subject: Declarations
Reply with quote

Please find the declarations below.

Code:
DCL DURATION         CHAR(40) INIT('') VARYING;
DCL OLD_DATE         CHAR(10);        /* YYYY-MM-DD */


Code:
EXEC SQL DECLARE OWNER.TABLE1 TABLE                 
          ( KEY                 INTEGER NOT NULL
          );
DCL 1 DCL_TABLE1,                                                 
     5 KEY  BIN FIXED(31);                                 



Code:
EXEC SQL DECLARE OWNER.TABLE2 TABLE                       
          ( KEY                    INTEGER NOT NULL, 
            DATE                   DATE NOT NULL
          );
DCL 1 DCL_TABLE2,                                                 
     5 KEY  BIN FIXED(31),
     5 Date    CHAR(10); 
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 15, 2011 7:57 am    Post subject:
Reply with quote

Hello,

Why do you believe that db2 (or anything else) can successfully subtract a char(40) variable from anything. . .

You need to do somethng to generate proper variable values or possibly consider dynamic sql that has the code created usinig your char(40) as part of the sql.

Quote:
duration can be 1 year or 5 day or 10 day.
Are these the only possible duration values? If so, you may want to consider selectively using 1 of 3 queries. . .
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 8:13 am    Post subject:
Reply with quote

Since i use VARYING, i get the proper duration in SPOOL when i gave PUT SKIP for that variable.

Quote:
duration can be 1 year or 5 day or 10 day.

These are just an example.


Code:

PUT SKIP LIST('DATA2-' || DURATION || ';' || '-');
Spool:
DATA2-1 YEAR;-
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Tue Mar 15, 2011 12:09 pm    Post subject:
Reply with quote

It may be 'right' for you but it is obviously not 'right' for the computer. Like with most arithmetic it uses numbers - DATA2 is not numeric.
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 12:39 pm    Post subject:
Reply with quote

ok. Then how do i execute the SELECT statement

Code:
SELECT 'N' INTO :Output_node         
FROM table1 A, table 2 B
WHERE A.KEY = B.KEY AND
           B.Date BETWEEN DATE(:old_DATE) - :duration
                     AND DATE(:old_DATE) - 1 DAY;     


Duration is VARYING according to the input.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Mar 15, 2011 12:48 pm    Post subject: Reply to: Db2 error in Pl/1 program
Reply with quote

we all appreciate Your loyalty to the people who set the requirement for You(*) icon_evil.gif

DB2 is right and You are wrong!
the error is yours and DB2 is just reporting it

read the db2 manual to understand what should be the definition of the host variables used in SQL statements with date computations!

Quote:
(*)
"loyalty to the people who set the requirement for You" == "stubbornness" icon_biggrin.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Mar 15, 2011 1:45 pm    Post subject:
Reply with quote

You can not have a hv that contains '5 days' and expect it to work in static SQL. Well, Apparently you do expect it, but you'd be wrong.
one way is having 2 or 3 HostVars

Code:

if ...
  MOVE 5 to Daydur
  MOVE 0 to yeardur
else
  MOVE 0 to Daydur
  MOVE 1 to yeardur
endif

EXEC SQL
   SELECT 'N' INTO :Output_node         
      FROM table1 A, table 2 B
    WHERE A.KEY = B.KEY AND
                B.Date BETWEEN DATE(:old_DATE) - :Daydur days - :yeardur years
                            AND DATE(:old_DATE) - 1 DAY;     
END-EXEC
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 1:46 pm    Post subject:
Reply with quote

Hi,

I understand DB2 is right. But would like to how can i declare a field lenght according to the input received (i.e., Duration).

Please help.
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 2:55 pm    Post subject:
Reply with quote

Code:
if ...
  MOVE 5 to Daydur
  MOVE 0 to yeardur
else
  MOVE 0 to Daydur
  MOVE 1 to yeardur
endif

EXEC SQL
   SELECT 'N' INTO :Output_node         
      FROM table1 A, table 2 B
    WHERE A.KEY = B.KEY AND
          B.Date BETWEEN DATE(:old_DATE) - :Daydur days - :yeardur years
                            AND DATE(:old_DATE) - 1 DAY;     
END-EXEC


Is it possible to code like :Daydur DAY or :yeardur YEAR
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Mar 15, 2011 5:47 pm    Post subject:
Reply with quote

venuhunev wrote:
Is it possible to code like :Daydur DAY or :yeardur YEAR
If you insist.
Why don't you just try it ?
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Tue Mar 15, 2011 8:13 pm    Post subject:
Reply with quote

It's probably far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far and far easier to use the PL/I builtin functions that deal with dates.
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 8:52 pm    Post subject:
Reply with quote

i tried it and am getting this error for variable :daydur

Code:
DCL DAYDUR             PIC'9999999' INIT('0');
DAYDUR  = 1;


UNDEFINED OR UNUSABLE HOST VARIABLE
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 15, 2011 8:56 pm    Post subject:
Reply with quote

Code:
DCL DURATION         CHAR(40) INIT('') [b]VARYING[/b];
DCL OLD_DATE         CHAR(10);        /* YYYY-MM-DD */

EXEC SQL DECLARE OWNER.TABLE1 TABLE                 
          ( KEY                 INTEGER NOT NULL
          );
DCL 1 DCL_TABLE1,                                                 
     5 KEY  BIN FIXED(31);                                 

EXEC SQL DECLARE OWNER.TABLE2 TABLE                       
          ( KEY                    INTEGER NOT NULL,
            DATE                   DATE NOT NULL
          );
DCL 1 DCL_TABLE2,                                                 
     5 KEY  BIN FIXED(31),
     5 Date    CHAR(10);

Code:

SELECT 'N' INTO :Output_node         
FROM table1 A, table 2 B
WHERE A.KEY = B.KEY AND
           B.Date BETWEEN DATE(:old_DATE) - :duration
                     AND DATE(:old_DATE) - 1 DAY;



Code:
duration can be 1 year or 5 day or 10 day - from user input
old_date format: YYYY-MM-DD
PUT SKIP LIST('DATA2-' || DURATION || ';' || '-');
Spool: DATA2-1 YEAR;-




Using VARYING , it is removing the trailing spaces but DB2 is not accepting the variable of 40 chars for date arithmetic.
how do i perform ?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Mar 16, 2011 1:20 am    Post subject:
Reply with quote

Have you read the fine manual?
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Wed Mar 16, 2011 9:14 am    Post subject:
Reply with quote

I also tried this way considering YEAR will be the input for DURATION.
Not sure what should be declaration for TBL_DUR.

Code:
DCL VALUE_END               DEC(15,0)    INIT(0);     
DCL DUR_VAL                 DEC(15,0)    INIT(0);     
DCL STRING_START            CHAR(50)     INIT('0');   
DCL DUR_STR                 CHAR(50)     INIT('0');   
DCL TBL_DUR                 CHAR(19)     INIT('0');   

VALUE_END    = INDEX(DURATION,' ') - 1;         
STRING_START = INDEX(DURATION,' ') + 1;         
DUR_VAL      = SUBSTR(DURATION,1,VALUE_END);   
DUR_STR      = SUBSTR(DURATION,STRING_START);   

IF VERIFY(DUR_VAL,'0123456789') ¬= 0                   
THEN DO;                                               
   ABEND
END;                                                   
IF VERIFY(DUR_STR,'EAR') = 0                           
THEN DO;                                               
   TBL_DUR = DUR_VAL || 'YEAR';                       
END;


Code:
SELECT 'N' INTO :Output_node         
FROM table1 A, table2 B
WHERE A.KEY = B.KEY AND
           B.Date BETWEEN DATE(:old_DATE) - :TBL_DUR
                     AND DATE(:old_DATE) - 1 DAY;
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2, 3  Next
Page 1 of 3

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us