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
 

 

Type Casting to integer format

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri Oct 17, 2008 5:43 pm    Post subject: Type Casting to integer format
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 17, 2008 6:12 pm    Post subject:
Reply with quote

do not waste time casting host variables. move them to appropriate fields/definitions before using them in your SQL.
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri Oct 17, 2008 6:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 17, 2008 6:43 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri Oct 17, 2008 6:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Mon Oct 20, 2008 10:46 am    Post subject:
Reply with quote

Hi Guys,

Anybody who is having the answer to the above Querry please reply me .


Thanks in advance
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: Mon Oct 20, 2008 11:04 am    Post subject:
Reply with quote

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
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Mon Oct 20, 2008 1:46 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Mon Oct 20, 2008 8:54 pm    Post subject:
Reply with quote

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
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


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