|
View previous topic :: :: View next topic
|
| Author |
Message |
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
duration can be 1 year or 5 day or 10 day.
old_date format: YYYY-MM-DD |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 1868
Location: UK
|
|
|
|
| What is a -401? |
|
| Back to top |
|
|
dick scherrer
Site Director
Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL
|
|
|
|
| Show the DDL for TABLE1 and TABLE2, and the declarations for the host variables OLD_DATE and DURATION. |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Site Director
Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix
|
|
|
|
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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 1868
Location: UK
|
|
|
|
| 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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
enrico-sorichetti
Global Moderator
Joined: 14 Mar 2007
Posts: 10308
Location: italy
|
|
|
|
we all appreciate Your loyalty to the people who set the requirement for You(*) 
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"  |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1047
Location: Oostende, Belgium
|
|
|
|
| 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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| 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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL
|
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
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 |
|
|
|
