Im trying to do a Left outer join sql query for 2 DB2 tables -through aqt editor. Join to happen between char and decimal fields.

皇冠hg0088代理网址For the where clause im providing ,the char and decimal fields always hold only 19 digits - no characters or decimals.

皇冠hg0088代理网址Tried cast function to convert char to decimal so join can happen b/w 2 decimal fields, also tried to cast both char and decimal to integer . Been getting errors like:

  1. 皇冠hg0088代理网址cursor is not in a prepared state,arithmetic overflow

  2. 皇冠hg0088代理网址other arithmetic exception occured.

It is quite a big query- Here is the line where the error occurs-

left outer join aTable on cast(aTable.charField AS Decimal(19,0)) = bTable.decimalField

皇冠hg0088代理网址bTable.decimalField is Decimal type with Length 19

aTable.charField is Char type with Length 30.

  • Please show us the queries that raise the errors. – GMB Sep 16 at 22:34
  • @GMB edited the post to add the line in the query where the error occurs. – user14290486 Sep 17 at 0:06
  • sounds like some of your data can't be cast to a DECIMAL. Depending on your Db2 version, you could use a function like this to show all the values that can't be so cast madebymalwina.com/a/59904676/9525344 – Paul Vernon Sep 18 at 15:11

皇冠hg0088代理网址It's always possible to cast a DECIMAL into a CHAR, but not always possible to cast a CHAR into a DECIMAL. I would suggest you try the first option instead of the second one (that you are using). Unless, of course, you are sure the data is in perfect, pristine condition.

For example you could rephrase your join as:

left outer join aTable on aTable.charField = '' || bTable.decimalField

The example above uses the most simple rendering from a numeric to a char value. Ultimately the exact rendering should take into account aspects such as:

  • Leading zeroes,
  • Possible negative sign of the numeric value,
  • Other formatting quirks such as dashes, commas, or dots.

You'll need to tweak the rendering accordingly.

皇冠hg0088代理网址A final note: the conversion strategy (left to right, or right to left) greatly affects the optimization options of the engine. If you make the change you may end up with a faster or slower query. In any case, you'll end up with a correct solution.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.