Re: An unrelated SQL question

case sales_rep__2_wt_id_i when '' then 0 else sales_rep__2_wt_id_i end as
sales_rep__2_wt_id_i


On Mon, Apr 8, 2013 at 2:13 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> 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?

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