× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 08 Apr 2013 11:13, 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?

Either of the following expressions should resolve the blank as zero issue without using a UDF, if a blank value should represent a zero value:

(select case sales_rep__2_wt_id_i
when '' then '0'
else sales_rep__2_wt_id_i
end
from wtentrv1
where account_id = '119488')

(select ifnull(nullif(sales_rep__2_wt_id_i,''),'0')
from wtentrv1
where account_id = '119488')


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.