Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: How to replace the java logic in sql store proc?

    Hello all,

    I need a small help from you. May be big

    I have a java code which they want to translate to Store Proc. Now the logic of the code is as below:

    1. Make a Db connection and execute the query below, with theatre_id passed on from the java code.

    2. Select * from theatre_list where unit_no = theatre_id;

    3. Get the resultset (a list of records that match the criteria ) and using a while loop, iterate through the result set to check each and every row. If in any row, the field_theatre_main_phone is not populated, assign the value from field_theatre_guest_phone to it.
    //////////
    if (field_theatre_main_phone == null)
    field_theatre_main_phone = field_theatre_guest_phone;
    ////////

    4. Do this validation and population till the end of resultset.

    Issue is, they dont want to use cursors, and i dont get any idea on how to iterate through row by row, without a cursor. Could you please help me in this?

    Believe me, I am new to informix and to advanced sql. I am only a Java fellow.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT your
         , list
         , of
         , fields
         , Coalesce(field_theatre_main_phone, field_theatre_guest_phone) As phone_number
    FROM   theatre_list
    WHERE  unit_no = theatre_id
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, just realised this is in the Informix forum and I don't know if Coalesce() is a valid function of this RDBMS...

    If it's not then try
    Code:
    SELECT your
         , list
         , of
         , fields
         , CASE WHEN field_theatre_main_phone IS NULL THEN
             field_theatre_guest_phone
           ELSE
             field_theatre_main_phone
           END As phone_number
    FROM   theatre_list
    WHERE  unit_no = theatre_id
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A quick Google around suggests that the NVL() function is the equivalent in Informix. In the first query I posted replace the word "Coalesce" with "NVL" and that should work
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2011
    Posts
    32

    Smile Thank You.

    Hi George,

    Thanks for responding back immediately and apologize for the delay in replying. I am using your approach of Case When expression, and so far...good. If I run into any exception, will surely ping back. Thank you for your time n efforts.

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
  •