IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Db2 error in Pl/1 program


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 2455
Location: Hampshire, UK

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

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

Moderator Emeritus


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

PostPosted: Tue Mar 15, 2011 2:27 am
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: 1788
Location: Bloomington, IL

PostPosted: Tue Mar 15, 2011 2:31 am
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
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

Moderator Emeritus


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

PostPosted: Tue Mar 15, 2011 7:57 am
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
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: 2455
Location: Hampshire, UK

PostPosted: Tue Mar 15, 2011 12:09 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Mar 15, 2011 12:48 pm
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: 1281
Location: Belgium

PostPosted: Tue Mar 15, 2011 1:45 pm
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
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
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: 1281
Location: Belgium

PostPosted: Tue Mar 15, 2011 5:47 pm
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

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Tue Mar 15, 2011 8:13 pm
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
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
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: 1788
Location: Bloomington, IL

PostPosted: Wed Mar 16, 2011 1:20 am
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
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
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 16, 2011 1:41 pm
Reply with quote

For someone who doesn't know how to define an integer host-variable in PL/1 you are very persistent in trying something that will NEVER work.
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 1:42 pm
Reply with quote

Can you please tel me how do i resolve the issue ?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 16, 2011 1:47 pm
Reply with quote

I already told you.
Now take up a manual and search for how to define an integer host variable.

but because spring is in the air, I did it for you :
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.apsg/db2z_equivalentplidatatypes.htm
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 1:55 pm
Reply with quote

thnx for your help.
You mean TBL_DUR should be declared as FIXED BIN equivalent to INTEGER ?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 16, 2011 2:33 pm
Reply with quote

GuyC wrote:
You can not have a hv that contains '5 days' and expect it to work in static SQL.

so forget about TBL_DUR
try the suggestion I gave you :
GuyC wrote:
Code:
...B.Date BETWEEN DATE(:old_DATE) - :Daydur days - :yeardur years
                            AND DATE(:old_DATE) - 1 DAY;     

and define daydur and yeardur as integers
----------------------------------------------------------------------------------
OR:
Oops, I followed my own advice and went back to the manual, following the link Akatsukami posted.
Apparently there is such a thing as duration decimal(8,0) which you can subtract or add to a date.
so :

Code:
DCL DURATION         DEC FIXED(8,0);
..
DURATION  = 00010000; <== 1 year
or
DURATION  = 00000005; <== 5 days
...
B.Date BETWEEN DATE(:old_DATE) - :DURATION
                     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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Using API Gateway from CICS program CICS 0
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts DB2 Event passed to the Application P... DB2 1
Search our Forums:

Back to Top