Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: Tricky query for multiple select of cobol variables in db2

    I have a problem in Query when used with cobol and db2 as below

    we are getting list of country codes which is character( 3) from file.
    The country codes are seperated by commas(,).. This will vary each time. It can be 3 country codes or 24 country codes...etc

    e.g IND,CHN,VEN


    we have to write a query which will select student id's based on the country codes.

    TABLE NAME STUDENT

    COLUMNS : NAME( NAME OF STUDENT) ,STUDID( STUDENT IDENTIFICATION NUMBER),CTRY_CODE( COUNTRY CODES).


    I have moved "IND,CHN,VEN" which is got from file to temporary variable. ws-parm-ctry

    and using the query

    select STUDID, NAME from STUDENT where CTRY_CODE in (:ws-parm-ctry).

    some how this doesnt work. pls do suggest me some solution.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select STUDID, NAME from STUDENT where CTRY_CODE in (:ws-parm-ctry)
    Will be translated into something like
    Code:
    select STUDID, NAME from STUDENT where CTRY_CODE in ('IND,CHN,VEN')
    This is not legal SQL. What you really wanted is:
    Code:
    select STUDID, NAME from STUDENT where CTRY_CODE in ('IND', 'CHN', 'VEN')
    Notice the place of the quotes '.

    This should work:
    Code:
    select STUDID, NAME from STUDENT where POSSTR(:ws-parm-ctry, CTRY_CODE) > 0
    It will be translated into something like
    Code:
    select STUDID, NAME from STUDENT where POSSTR('IND,CHN,VEN', CTRY_CODE) > 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would loop through the string byte by byte, once I found the delimiter, I would insert the value into a global temp table. Once you've exhausted thee string, I would then execute your sql as a join to the temp table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2006
    Posts
    3
    Hi Wim,

    sorry for the delay reply.
    Iam getting an error too many arguments. I hope posstr function is taking 'IND,CHN,VEN' as string and searching one country code. Have you tried this by executing. Can you pls execute and send me the result.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you send me the DDL (CREATE TABLE statements) and DML (SELECT statements) ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Oct 2006
    Posts
    3
    Hi WIM,

    I thank you so much for the help you had done to me. I couldnt do that Query with POSSTR, but was able to do with LOCATE.

    I heard that POSSTR will be removed in DB2 V8.2 , It is true?

    LOCATE works only for string, Is there any such Query which will work for Integers for the same condition.

    I am eagerly waiting for your reply.

    I once again thank you very much for helping me.


    The table was a sample table I had choosen for a larger solution. I may not be able to provide the original table as per company restrictiuons. I hope you understand my difficuly.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I couldnt do that Query with POSSTR, but was able to do with LOCATE.
    LOCATE is a special case of POSSTR, in which you can specify the starting position from where to start looking for the search-string. It surprises me that you can't get it working with POSSTR. Can you give the failing SQL and the error?
    I heard that POSSTR will be removed in DB2 V8.2 , It is true?
    That would mean rewriting all the existing programs that use POSSTR. Normally DB2 wants to be back compatible as much as possible. Don't believe a word of it. Doesn't DB2 v8.2 already exist about 2 years?
    LOCATE works only for string, Is there any such Query which will work for Integers for the same condition.
    In this case, you'd better use the solution suggested by Brett Kaiser. His solution is more general, but a bit more complex.
    If you only need strings, I'd use the POSSTR-solution.
    If you need other data types too, you'd better use Brett's solution.
    The table was a sample table I had choosen for a larger solution. I may not be able to provide the original table as per company restrictiuons. I hope you understand my difficuly.
    I'm not interested in the (confidential) data, but in the structure of the table, at least the few columns that would make it possible for somebody else to replicate your problem and find a solution. I hope you understand my difficulty.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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