Urgent Help :Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rAinDeEr
    #1

    Urgent Help :Query

    Hi,

    I get the following error in the query

    SQL0415N The data types of corresponding columns are not compatible in
    a
    fullselect that includes a set operator or in the multiple rows of a
    VALUES
    clause of an INSERT or fullselect. SQLSTATE=42825

    How can i specify a combination of values in the IN phrase ? I need a
    combination as
    I need to check against valid combinations of ('test',1)
    ('test1,2),('te st3',3)

    I cant figure out how to specify these in the IN phrase.

    If i give 2 IN phrases a.nme in (''test','test1 ','test3') and a.seq_num
    IN (1,2,3) it will not give me the correct result cause the a.nme and
    a.seq_num are the primary keys and i need to filter out the
    combination.


    SELECT
    a.name,
    a.seq_num
    FROM
    db2test.a
    (
    SELECT
    num,
    seq_num,
    yr
    FROM
    db2test.b
    WHERE
    p_num= '100' AND
    p_seq_num=1 AND
    p_bgt_yr='2006'
    ) as c
    WHERE
    a.num=c.num AND
    a.seq_num=c.seq _num AND
    a.yr=c.yr AND
    (a.nme,a.seq_nu m) IN ('test',1)
    GROUP BY
    nme,seq_num

    Tags: None
    • Serge Rielau
      #2
      Re: Urgent Help :Query

      rAinDeEr wrote:
      Hi,
      >
      I get the following error in the query
      >
      SQL0415N The data types of corresponding columns are not compatible in
      a
      fullselect that includes a set operator or in the multiple rows of a
      VALUES
      clause of an INSERT or fullselect. SQLSTATE=42825
      >
      How can i specify a combination of values in the IN phrase ? I need a
      combination as
      I need to check against valid combinations of ('test',1)
      ('test1,2),('te st3',3)
      >
      I cant figure out how to specify these in the IN phrase.
      >
      If i give 2 IN phrases a.nme in (''test','test1 ','test3') and a.seq_num
      IN (1,2,3) it will not give me the correct result cause the a.nme and
      a.seq_num are the primary keys and i need to filter out the
      combination.
      >
      >
      SELECT
      a.name,
      a.seq_num
      FROM
      db2test.a
      (
      SELECT
      num,
      seq_num,
      yr
      FROM
      db2test.b
      WHERE
      p_num= '100' AND
      p_seq_num=1 AND
      p_bgt_yr='2006'
      ) as c
      WHERE
      a.num=c.num AND
      a.seq_num=c.seq _num AND
      a.yr=c.yr AND
      (a.nme,a.seq_nu m) IN ('test',1)
      GROUP BY
      nme,seq_num
      >
      (a.nme,a.seq_nu m) IN (VALUES('test', 1), ('test2', 2), ....)

      Cheers
      Serge

      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

      IOD Conference
      http://www.ibm.com/software/data/ondemandbusiness/conf2006/

        Comment

        • Brian Tkatch
          #3
          Re: Urgent Help :Query

          rAinDeEr wrote:
          Hi,
          >
          I get the following error in the query
          >
          SQL0415N The data types of corresponding columns are not compatible in
          a
          fullselect that includes a set operator or in the multiple rows of a
          VALUES
          clause of an INSERT or fullselect. SQLSTATE=42825
          >
          How can i specify a combination of values in the IN phrase ? I need a
          combination as
          I need to check against valid combinations of ('test',1)
          ('test1,2),('te st3',3)
          >
          I cant figure out how to specify these in the IN phrase.
          >
          If i give 2 IN phrases a.nme in (''test','test1 ','test3') and a.seq_num
          IN (1,2,3) it will not give me the correct result cause the a.nme and
          a.seq_num are the primary keys and i need to filter out the
          combination.
          >
          >
          SELECT
          a.name,
          a.seq_num
          FROM
          db2test.a
          (
          SELECT
          num,
          seq_num,
          yr
          FROM
          db2test.b
          WHERE
          p_num= '100' AND
          p_seq_num=1 AND
          p_bgt_yr='2006'
          ) as c
          WHERE
          a.num=c.num AND
          a.seq_num=c.seq _num AND
          a.yr=c.yr AND
          (a.nme,a.seq_nu m) IN ('test',1)
          GROUP BY
          nme,seq_num
          According to the documentation, SQL Reference Volume 1, Chapter 2.
          Language Elements, Predicates=>IN predicate, when the left-expression
          has parenthesis (making it expression3 and allowing mutiple values) the
          right-expression must be a full-select.

          Therefore: (a.nme,a.seq_nu m) IN (VALUES ('test',1))

          Or with multiple possibilities:
          (a.nme,a.seq_nu m) IN (VALUES ('test',1), ('test1,2), ('test3',3))

          B.

            Comment

            • rAinDeEr
              #4
              Re: Urgent Help :Query

              Serge, Brian...


              thanks a lot....Solved it and 'values' is great..

              tariq

                Comment

                • rAinDeEr
                  #5
                  Re: Urgent Help :Query

                  Hi,

                  am able to run the query from the command window but when I am tryg to
                  use this from a java application with parameter markers, its giving me
                  an error -418.

                  A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER
                  MARKERS

                  Explanation ::

                  Parameter markers cannot be used in the SELECT list, as the sole
                  argument of a scalar function, or in a concatenation operation.
                  Parameter markers cannot be used in the string expression of an EXECUTE
                  IMMEDIATE SQL statement.

                  I had found this from the group..Serge had replied...

                  Here's your porblem:

                  bitwiseAnd(?,80 5)
                  DB2 supports function overloading. Since the parameter marker is
                  untyped, db2 doesn't know how to resolve the function and how to type
                  the
                  parameter marker. You can do: bitwiseAnd(cast (? as integer),805)

                  Do I need to typecast the parameter marker ?

                  tariq

                    Comment

                    • Brian Tkatch
                      #6
                      Re: Urgent Help :Query


                      rAinDeEr wrote:
                      Hi,
                      >
                      am able to run the query from the command window but when I am tryg to
                      use this from a java application with parameter markers, its giving me
                      an error -418.
                      >
                      A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER
                      MARKERS
                      >
                      Explanation ::
                      >
                      Parameter markers cannot be used in the SELECT list, as the sole
                      argument of a scalar function, or in a concatenation operation.
                      Parameter markers cannot be used in the string expression of an EXECUTE
                      IMMEDIATE SQL statement.
                      >
                      I had found this from the group..Serge had replied...
                      >
                      Here's your porblem:
                      >
                      bitwiseAnd(?,80 5)
                      DB2 supports function overloading. Since the parameter marker is
                      untyped, db2 doesn't know how to resolve the function and how to type
                      the
                      parameter marker. You can do: bitwiseAnd(cast (? as integer),805)
                      >
                      Do I need to typecast the parameter marker ?
                      >
                      tariq
                      What's the exact command you are trying to execute?

                      B.

                        Comment

                        • rAinDeEr
                          #7
                          Re: Urgent Help :Query

                          SELECT
                          a.name,
                          a.seq_num
                          FROM
                          db2test.a
                          (
                          SELECT
                          num,
                          seq_num,
                          yr
                          FROM
                          db2test.b
                          WHERE
                          p_num= ? AND
                          p_seq_num=? AND
                          p_bgt_yr=?
                          ) as c
                          WHERE
                          a.num=c.num AND
                          a.seq_num=c.seq _num AND
                          a.yr=c.yr AND
                          (a.nme,a.seq_nu m) IN (values(?,?) ,values(?,?) )
                          GROUP BY
                          nme,seq_num

                          This is the command am trying to Execute.

                            Comment

                            • Serge Rielau
                              #8
                              Re: Urgent Help :Query

                              I don't think the parameter marker can reach through values and in
                              predicate.
                              So you need to help it out by casting the first row.
                              Note that you only need one VALUES keyword
                              (a.nme,a.seq_nu m) IN (values(CAST(? AS <sometype>),
                              CAST(? AS <someothertype> )),
                              (?,?))

                              Cheers
                              Serge
                              --
                              Serge Rielau
                              DB2 Solutions Development
                              IBM Toronto Lab

                              IOD Conference
                              http://www.ibm.com/software/data/ondemandbusiness/conf2006/

                                Comment

                                Previous template Next
                                Working...