substring in sqls

sandhya.karanam · #1 (**permalink**) 12-09-09, 04:47

Hi,

I want to use the host vairables in the SUBSTR function in an SQL. When i tried to do it, it is giving me sqlcode = -171.

Can we use the host variables in an sql for the SUBSTR function. This is in COBOL400.

My sql requirement is :

select * from temtbl
where substr(data, :ws_len1, :ws_len2)

the variables are declared as follows:

ws_len1 pic s9(03).
ws_len2 pic s9(03).

Please let me know as early as possible..

Thank you,
Sandhya.

dr_te_z · #2 (**permalink**) 12-09-09, 05:53

Do not know COBOL400. As a mainframe guy my Pavlov reaction would be:

Code:

ws_len1 pic s9(04) comp.
ws_len2 pic s9(04) comp.

Why don't you give it a try.

umayer · #3 (**permalink**) 12-09-09, 05:55

You can use hostvariables with SUBSTR(field,startpos,length).
startpos and length must be of type INTEGER
( i believe, it's comp-3 in COBOL)

use of LIKE instead of SUBSTR is more efficient in most cases.

dr_te_z · #4 (**permalink**) 12-09-09, 08:05

Quote:

Originally Posted by umayer

( i believe, it's comp-3 in COBOL)

Wrong!

Code:

pic s9(4)  comp = halfword  binary integer
pic s9(9)  comp = fullword  binary integer
pic s9(18) comp = doubleword binary interger

sandhya.karanam · #5 (**permalink**) 12-09-09, 22:50

i tried using comp as well as comp-3 but still i am getting the sqlcode -171, it says the value or data type or length of the argument in function not valid

DB2Plus · #6 (**permalink**) 12-09-09, 23:21

Quote:

Originally Posted by sandhya.karanam

i tried using comp as well as comp-3 but still i am getting the sqlcode -171, it says the value or data type or length of the argument in function not valid

Quote:

Working storage section.
01 start pic s9(4) binary.
01 len pic s9(4) binary.
01 substring pic x(777) value spaces.
01 length-of-string pic s9(4) binary.

Code:

Procedure division.

exec sql Select substr(string, :start, :len) 
        into :substring from temtbl
end-exec
If sqlcode < 0 
   display 'sqlcode = ' sqlcode 'len = '  len ' start = ' start
   exec sql Select length(string) 
              into :length-of-string  from temtbl
       end-exec   
   display 'length of string = ' length-of-string  
end-if

Quote:

1. start > 0 and start <= length(string)
2. len >= 0 and len <= length(string)
3. start + len <= length(string) + 1
4. You can omit len

Kara

Lenny77 · #7 (**permalink**) 12-10-09, 10:26

Quote:

Originally Posted by sandhya.karanam

Hi,

I want to use the host vairables in the SUBSTR function in an SQL. When i tried to do it, it is giving me sqlcode = -171.

Can we use the host variables in an sql for the SUBSTR function. This is in COBOL400.

My sql requirement is :

select * from temtbl
where substr(data, :ws_len1, :ws_len2)

the variables are declared as follows:

ws_len1 pic s9(03).
ws_len2 pic s9(03).

Please let me know as early as possible..

Thank you,
Sandhya.

ws-len1 comp pic s9(04).
ws-len2 comp pic s9(04).

Also I didn't understand what does it mean ?

Code:

select * from temtbl
where substr(data, :ws_len1, :ws_len2)

Maybe

Code:

select * from temtbl
where substr(data, :ws-len1, :ws-len2) = 'something'

Lenny

sandhya.karanam · #8 (**permalink**) 12-11-09, 00:12

hi lenny, yeah i missed it...

wat u have given is right...

i could see that we are not able to use the host variables in a substr function, rather i used a dynamic sql and it worked.

Internet Services

Web Development

Digital Marketing

Consumer Tech