Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    8

    Question Unanswered: JDBC -- using the SUM() built-in SQL function

    Hello,

    Here's the code,

    String sql = "SELECT sum(max(seq_sequence_num) + 1) FROM seq_tbl";
    res = query.executeQuery(sql);

    while(res.next()) {
    System.out.println("2");
    seq_number = res.getInt("seq_sequence_num");
    System.out.println("Seq Number : " + seq_number);
    }

    In the "while" loop, it gives me an error saying "seq_sequence_num" column in invalid.
    "S0022: Invalid column name 'seq_sequence_num'."
    That column exists in the "seq_tbl" table, but still i get this error.

    Please could anyone help me in getting this issue resolved ...

    Thanks,
    - Jay.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Soln for u

    String sql = "SELECT sum(max(seq_sequence_num) + 1) AS MAXSUM FROM seq_tbl";
    res = query.executeQuery(sql);

    while(res.next()) {
    System.out.println("2");
    seq_number = res.getInt("MAXSUM");
    System.out.println("Seq Number : " + seq_number);
    }


    Cheers
    Keep the things simple as world is already complex

  3. #3
    Join Date
    Feb 2004
    Posts
    18

    Re: JDBC -- using the SUM() built-in SQL function

    Hi Jaya,
    In SQL statement that you have mentioned the query will return the result under a column named SUM(MAX(SEQ....)+1)... This is because you have not used an alias... Hence the column name seq_sequence_num is not recognised...

    Originally posted by Jayathirtha
    Hello,

    Here's the code,

    String sql = "SELECT sum(max(seq_sequence_num) + 1) FROM seq_tbl";
    res = query.executeQuery(sql);

    while(res.next()) {
    System.out.println("2");
    seq_number = res.getInt("seq_sequence_num");
    System.out.println("Seq Number : " + seq_number);
    }

    In the "while" loop, it gives me an error saying "seq_sequence_num" column in invalid.
    "S0022: Invalid column name 'seq_sequence_num'."
    That column exists in the "seq_tbl" table, but still i get this error.

    Please could anyone help me in getting this issue resolved ...

    Thanks,
    - Jay.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: JDBC -- using the SUM() built-in SQL function

    Originally posted by Jayathirtha
    Hello,

    Here's the code,

    String sql = "SELECT sum(max(seq_sequence_num) + 1) FROM seq_tbl";
    res = query.executeQuery(sql);

    while(res.next()) {
    System.out.println("2");
    seq_number = res.getInt("seq_sequence_num");
    System.out.println("Seq Number : " + seq_number);
    }

    In the "while" loop, it gives me an error saying "seq_sequence_num" column in invalid.
    "S0022: Invalid column name 'seq_sequence_num'."
    That column exists in the "seq_tbl" table, but still i get this error.

    Please could anyone help me in getting this issue resolved ...

    Thanks,
    - Jay.
    That's because there's no "seq_sequence_num" in your result set, but "sum(max(seq_sequence_num))". You can solve your problem by a) using a correlation name for the field, like "SELECT SUM(MAX(seq_sequence_num)) AS FIELD1 FROM ..." and then using res.getInt("FIELD1") or b) fetching the field by its number rather than the name: res.getInt(1);

    By the way, what is the purpose of SUM() in your query? MAX() gives you a single row anyway...

Posting Permissions

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