Re: An unrelated SQL question

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 resulting report:
select digits('98765432101') from qsqptabl
; -- report from above SELECT follows:
DIGITS
000000000000000000000987654321010000000000000000000000000000000
******** 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 explain.

Regards, Chuck

On 10 Apr 2013 10:22, Peter Dow wrote:

One more way to do this might be to replace "contact_id" with
"substr(digits(contact_id),1,5)".

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
enrollment number.

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),
total_order_value,x01_parent_id, record_id
from wtex01v1
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?