Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: substring in sqls

    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.

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    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.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by umayer View Post
    ( 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

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    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

  6. #6
    Join Date
    Jul 2009
    Posts
    150

    Smile

    Quote Originally Posted by sandhya.karanam View Post
    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
    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
    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
    Last edited by DB2Plus; 12-10-09 at 00:44.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question What does it mean

    Quote Originally Posted by sandhya.karanam View Post
    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

  8. #8
    Join Date
    Nov 2009
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •