|
View previous topic :: View next topic
|
| Author |
Message |
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi all,
I m having a requirement like below,
A variable is defined as,
ws-rprt-num pic x(5)
I just need to use this value in the DB2 query by type casting it to integer format.
I usee like below,
Integer(ws-rprt-num) <=another value
Noe i need the first three characters need to be type casted to integer and that need to be checked with the another value.
how to do this???
Please help me. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| do not waste time casting host variables. move them to appropriate fields/definitions before using them in your SQL. |
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi Dick,
As u said i just made the changes for the host variable and it was done
But the problem in a querry is i m having a column whose length = 5 and of character type.
| Code: |
Rpt_cd CHAR(5) NOT NULL
|
I just need the first 3 characters of the RPT_CD to be checked with my another variable which is of integer type of length = 3.
I just tried with as below,
INTEGER(SUBSTR(Rpt_cd,1,3) >= WS-rpt-CD
But it is abending with SQL = -170 too many or too few arguments.
Could u please tell me how to solve this issue.
[/b][/quote] |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
INTEGER(SUBSTR(Rpt_cd,1,3)
could use a close parenthesis.
why are you casting? if you have to SUBSTR something, at least have your host variable of PIC X type for the length of the SUBSTR.
SUBSTR of a CHAR field returns CHAR data. redefine you host variable so that it is also char (pic x) for the length of the substr.
then you predicate would be
SUBSTR(RPT_CD,1,3) >= :WS-RPT-CD-ALPHA |
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi Dick,
What you have suggest was correct only. But in my case it's some thing different.
For e.g:-
WS-RPT-CD-ALPHA PIC X(3)
Actully i m getting this value from another table.
Now My codition is like WS-RPT-CD-ALPHA should liew between RPT_CD and MSR_CD.
Those two are the attributes of a table of character tupe having length = 50.
So my querry is like below,
| Code: |
SELECT * from tabname
where
INTEGER(SUBSTR(RPT_CD,1,3)) >= INTEGER(:WS-RPT-CD-ALPHA)
AND
INTEGER(SUBSTR(MSR_CD,1,3)) <= INTEGER(:WS-RPT-CD-ALPHA)
|
But above querry is abending with SQLCODE = -170.
Could you please tell me how to get the first three character of a varible in integer format .
I suppose i m clear with my question.
Please help |
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi Guys,
Anybody who is having the answer to the above Querry please reply me .
Thanks in advance |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is there some reason you continue to use integer? DBZ has already explained that you need to use host variables of pic x.
| Quote: |
| Could you please tell me how to get the first three character of a varible in integer format . |
I believe you will get where you want quicker if you use PIC X(3). |
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi all,
I suppose i was not clear with my question to all of you.
Let my table name is EMPLY which is having attribute like
| Code: |
RPT_CD CHAR(20)
CD_KEY CHAR(40)
Name_TXT CHAR(50) |
I m having a variable in cobol prg as WS-ST-NUM PIC 9(03)
So my querry should be like below,
SELECT * From EMPLY
WHERE
WS-ST-NUM should be greater than First three character of RPT_CD
and Less than the First three character of CD_KEY.
With ur;
Now i think i m clear with my question. If anybody knows please help me. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I m having a variable in cobol prg as WS-ST-NUM PIC 9(03) |
Is there some reason you cannot redefine this as
| Code: |
| nn WS-ST-NUM-x REDEFINES WS-ST-NUM PIC x(03). |
You would then use the -X field in your SELECT. . . |
|
| Back to top |
|
|
|
|
