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: 2455 Location: Hampshire, UK
|
|
|
|
What is a -401? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 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: 1788 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
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 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: 2455 Location: Hampshire, 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
Superior Member
Joined: 14 Mar 2007 Posts: 10873 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: 1306 Location: Vilnius, Lithuania
|
|
|
|
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: 1788 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
venuhunev
New User
Joined: 26 May 2007 Posts: 70 Location: chennai
|
|
|
|
Can you please tel me how do i resolve the issue ? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007 Posts: 70 Location: chennai
|
|
|
|
thnx for your help.
You mean TBL_DUR should be declared as FIXED BIN equivalent to INTEGER ? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|