Knut Stolze wrote:

> I would simplify the whole CASE expression like this:
>
> CASE
> WHEN ( SELECT ccpf1.sap_cnt_prtnr_func_code
> FROM schema1.cust_cnt_prtnr_func AS ccpf1
> WHERE so.sold_to_cust_num = ccpf1.cust_num ) IN ( 'Z1', 'ZT', 'Y8'
> )
> THEN ( SELECT cnt.cnt_email_adr
> FROM schema1.cust_cnt_prtnr_func AS ccpf1,
> schema1.contact AS cnt
> WHERE so.sold_to_cust_num = ccpf1.cust_num AND
> ccpf1.sap_cnt_id = cnt.sap_cnt_id )
> END
>
> And if you happen to have a check constraint on the
> "SAP_CNT_PRTNR_FUNC_CODE" column so that those values can't be anything
> besides Z1, ZT, or Y8, then a simple scalar subselect will also work:
>
> ( SELECT cnt.cnt_email_adr
> FROM schema1.cust_cnt_prtnr_func AS ccpf1,
> schema1.contact AS cnt
> WHERE so.sold_to_cust_num = ccpf1.cust_num AND
> ccpf1.sap_cnt_id = cnt.sap_cnt_id )

Now that I read it again, even without the constraint, you can stick to a
simple subselect:

( SELECT cnt.cnt_email_adr
FROM schema1.cust_cnt_prtnr_func AS ccpf1,
schema1.contact AS cnt
WHERE so.sold_to_cust_num = ccpf1.cust_num AND
ccpf1.sap_cnt_prtnr_func_code IN ( 'Z1', 'ZT', 'Y8' ) AND
ccpf1.sap_cnt_id = cnt.sap_cnt_id )

The additional condition will cause the subselect to return only the
"cnt_email_adr" value for Z1, ZT and Y8. For all others, the sub-query
will yield a NULL.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena