1

I just came across scenario when occasionally (not for all sets of data) I'm getting "Error: SQL0802 - Data conversion or data mapping error." exception when adding ORDER BY to simple query. For example, this works:

SELECT
    market,
    locationCode,
    locationName
FROM locations

and the following is failing miserably:

SELECT
    market,
    locationCode,
    locationName
FROM locations
ORDER BY locationName

I'm getting: Error: SQL0802 - Data conversion or data mapping error. (State:S1000, Native Code: FFFFFCDE)

I get the same error if I try to sort by name, or population, or anything really.... but only sometimes, meaning, when it errors on name or code, it would error if sorted by any field in locations subset. If it works for particular subset of locations, then it works for any sort order.

There are no null values in any of the fields, code and name fields are character fields.

Initially, I got this error when I added ROW_NUMBER column:

ROW_NUMBER() OVER(PARTITION BY market ORDER BY locationCode) as rowNumber

since, I narrowed it down to failing order case. I don't know which direction to go with it. Any thoughts?

update: there are no blank values for location name field. And even if I remove all fields in this subset and leave only 7 digit numeric id and sort by that field. I still get the same error. 

WITH locs as (
    SELECT id
    FROM locations
)
SELECT *
FROM locs
ORDER BY id

I get this error when I SELECT DISTINCT any field from the subset too.

InitK
  • 1,261
  • 13
  • 21
  • 3
    Perhaps related to: http://stackoverflow.com/questions/13075585/why-am-i-getting-a-sql0802-data-conversion-of-data-mapping-error-exception – xQbert May 13 '15 at 15:05
  • 1
    Does locationName appear blank on any record? if so you may have a data quality problem It may not really be blank and the system is unable to process the data stored in the value when sorting. continue to subset data until you can identify the offending record. For example find the 1/2 way point on location code and search for those < that and order if it works then look the other way, and keep dividing in 1/2. Should take about 10 tries or less to identify the offending record(s) (could be more than 1!) – xQbert May 13 '15 at 15:08
  • 1
    Can you explain the use of `tsql` and `db2` and `ibm-midrange` all together for this question? Was `tsql` unintentional? It doesn't quite fit and is potentially confusing. – user2338816 May 13 '15 at 15:17
  • to user2338816: Well, maybe I'm wrong about tags, but this is SQL query running agaist db2 database. Innitially I added iSeries tag (because it is i 7 database), but it was changed to ibm-midrange automatically. Is it wrong to use tsql tag in this case? Which tag would you use? – InitK May 13 '15 at 15:26
  • to xQbert: I updated the question with some more details and examples. This is what I'm trying to do now - narrow down to breaking point. – InitK May 13 '15 at 15:28
  • 1
    The _I'm getting:_ seems suspect; the apparent sqlstate is not a DB2, but a user-defined state. The query is apparently being run from a client; run the query on the server directly, and if the same error occurs, include the failing joblog spooled with LOG(4 0 *SECLVL) from the job run with debug active [i.e. after STRDBG was issues]. – CRPence May 13 '15 at 15:32
  • 1
    @xQbert has the right idea. It sounds as if MARKET has non-decimal characters in it for some rows / orders. You might consider using the HEX() scalar to try to locate the bad rows, or it might turn out that the server doesn't update MARKET until an order has reached a certain status; you may need to add an additional test to your WHERE clause to include only the 'good' orders. – Buck Calabro May 13 '15 at 15:37
  • any indexes on locs and can it be rebuilt? – xQbert May 13 '15 at 16:09
  • It looks like I narrowed my problem down to some bad character in location code character field. To prepare locations subset I'm doing various "matching" as data is coming from two different systems, and in the process attempting "trimming" of the data, removing leading zeros and trailing spaces, last character and stuff like that. This last bit seems to be what is tripping the query as it is trying to remove some invalid character and everything that follows just goes south. Thanks everyone! Comments were very helpful to look at the problem from different angle. – InitK May 13 '15 at 16:17

1 Answers1

2

I had/have the exact same situation as described. The error seemed to be random, but would always appear when sorting was added. Although I can't precisely describe the technical details, what I think is occurring is the "randomness" was actually due to the size of the tables, and the size of the cached chunks of returned rows from the query.

The actual cause of the problem is junk values and/or blanks in the key fields used by the join. If there was no sorting, and the first batch of cached results didn't hit the records with the bad fields, the error wouldn't occur at first...but eventually always it did.

And the two things that ALWAYS drew out the error IMMEDIATELY were sorting or paging through the results. That's because in order to sort, it has to hit every one of the those key fields, and then cache the complete results. I think. Like I said, I don't know the complete technobabble, but I'm pretty sure that's close in laygeek terms.

I was able to solve the error by force-casting the key columns to integer. I changed the join from this...

FROM DAILYV INNER JOIN BXV ON DAILYV.DAITEM=BXV.BXPACK

...to this...

FROM DAILYV INNER JOIN BXV ON CAST(DAILYV.DAITEM AS INT)=CAST(BXV.BXPACK AS INT)

...and I didn't have to make any corrections to the tables. This is a database that's very old, very messy, and has lots of junk in it. Corrections have been made, but it's a work in progress.

spinjector
  • 3,121
  • 3
  • 26
  • 56
  • Thank you for your answer, I cannot test it anymore though, the issue is quite old and I don't have the same data available anymore. The problem I had was at the end caused by "bad" character as far as I recall, but this is interesting scenario that you are describing. – InitK Jan 03 '18 at 16:36