You could wrap the following function around the "sales_rep__2_wt_id_i" field:
Coalesce(nullif(sales_rep__2_wt_id_i,''),0)
The nullif function changes the blanks to a NULL and the Coalesce chooses the first non-null result.
You might only need the NULLIF function, as that will not be equal to a numeric value.
Alternatively, you could add a WHERE sales_rep__2_wt_id_i <> '' clause in that last subselect.
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Monday, April 08, 2013 11:13 AM
To: Midrange Systems Technical Discussion
Subject: An unrelated SQL question
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?
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
