The OP did not include the DDL for the fields discussed, only words
to describe, but "contact_id" is described as a "number" whereas the
only field clearly described as a "character field" containing a
"number", was the field named "sales_rep__2_wt_id_i".
The given suggestion seems to imply that the contact_id was
understood to be a character field; i.e. per "contact_id contains the
5-digit enrollment number ... is left adjusted".?
Note: Using DIGITS(character_field) gives a result that would be a
huge surprise to just about anyone, if they had not looked at the
documentation to know what to expect. The expression
"substr(digits(contact_id),1,5)" will always return '00000' for *every*
valid representation of a number in an 11-byte character field named
contact_id. To better understand why, see the following query and its
select digits('98765432101') from qsqptabl
; -- report from above SELECT follows:
******** End of data ********
No matter, the problem is well-enough described to be quite confident
that the origin for the issue is the failure of the query to be able to
effect the following; an implicit action by the query, that must occur
in the described scenario:
CAST(' ' AS DECIMAL) /* or AS whatever numeric type */
So... No change to the representation of the contact_id in the
failing predicate [contact_id=(scalar_subselect)] would be able to
resolve the data mapping error that arises as a result of the implicit
attempt by the query to cast from a character-blanks value to a numeric
value; i.e. the expression "substr(digits(contact_id),1,5)" replacing
"contact_id" would [by itself] not assist to prevent the error. HTH to
On 10 Apr 2013 10:22, Peter Dow wrote:
One more way to do this might be to replace "contact_id" with
That's assuming that when contact_id contains the 5-digit enrollment
number, the 5-digit enrollment number is left adjusted. If not, just
change the substr parms as appropriate.
On 4/8/2013 11:13 AM, James H. H. Lampert wrote:
The field "sales_rep__2_wt_id_i" in WTENTRV1 is a 5-character
field, that contains either a 5-digit user enrollment number (not
zero-blanked), or it's all blank.
The field "account_id" in WTENTRV1 is a unique key (albeit without
an SQL primary key constraint), a 9-digit number.
The field "contact_id" in WTCONTV1 is a unique key (again, without
an SQL primary key constraint), an 11-digit number that, for the
contact records pertaining to users, contains the 5-digit user
Given the following SQL select, issued by a BIRT report:
select customer_p_O__no, cdate,
(select count(record_id) from wtex02v1
where wtex01v1.record_id = x02_parent_id),
where x01_parent_id = '119488' and order_type = 'Quote'
and status = 'Pending'
and total_order_value >
(select user_quote_limit from wtcontv1 where contact_id =
(select sales_rep__2_wt_id_i from wtentrv1
where account_id = '119488'))
order by customer_p_O__no
we get a data mapping error because in the record with account_id =
'119488', sales_rep__2_wt_id_i is blank.
Any suggestions on how to get around that?