Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: Order By Clause with the Sort Key as a Cobol Variable

    Hi Everybody.

    I am dealing with a situation , where I need to Sort the keys in a Cursor , depending upon some business logic , which would be embedded in COBOL Program. Example shown Below

    Select
    Student_ID,
    Student_Name,
    Student_Marks,
    Student_Age
    FROM STUDENT

    ORDER BY
    :WS- STUDENT-NAME
    :WS- STUDENT-ID
    :WS- STUDENT-MARKS


    The :WS variable are COBOL variables. I need the Sorting logic to be dynamic based on the variables value . Either I can pass the Column Name in Variable value (Example - "Student_Name") , or the Column No ( 1 or 2). However , whenever I am applying these host variables in Order By Clause , the PRE-COMPLIER is throwing the Error Message

    UNDEFINED or UNUSABLE HOST VARIABLE : WS-STUDENT-NAME .

    Is it any possible way by which I can use Dynamic variables in ORDER by Clause , instead of Only Column Name or Column Number.

    Thanks in Advance !!

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    When asking for help, always post your DB2 server VERSION and FIXPACK and OPERATING-SYSTEM-details because the answers can depend on these facts.

    You should use dynamic-sql - follow the IBM sample programs for COBOL to learn how to do that.

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    Thanks Sir,

    My DB2 version in Db2V8 . I am not fully aware of the Fix Pack , the the O/S is Z/OS . The logic is to be implemented in a Batch Program

    However, Dynamic Sql has it's performance issues , and hence it would not be preferable for the organization to go with Dynamic SQL . Is there any option available for STATIC Sql , as CASE within ORDER BY , etc . I can store the sorting order too in a database , and have

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Himanshu2010 View Post

    Is there any option available for STATIC Sql
    How can it be static SQL if you intend to change the query semantics by supplying different order conditions each time? You could certainly use a CASE expression in the ORDER BY clause, if it fits your logic (which you did not explain).
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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