Re: An unrelated SQL question

[CRPence <CRPbottle@xxxxxxxxx>]

  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?