SEO

I get the following error when i compile the view given below.

@f3n11:/home/satish/views/> db2 -td@ -f sat.vw
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SCHEMA1.TEST" is an undefined name. SQLSTATE=42704

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "so.sold_to_cust_num,
so.sold_to_cust_name" was
found following "lld_flag ) as select". Expected tokens may include:
"<space>". SQLSTATE=42601


--------the foll. is the view code..........

drop view schema1.test
@
create view schema1.test
(
sold_to_cust_num,
sold_to_cust_name,
cnt_email_adr,
cntry_code,
assrtmt_module_id,
dlvry_provider_name,
sap_sales_ord_num,
cnt_fname,
cnt_lname,
mod_date,
add_date,
sales_ord_billd_flag
)
as select
so.sold_to_cust_num,
so.sold_to_cust_name,
case c.cnt_email_adr
when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='Z1')
then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='Z1' and
ccpf1.sap_cnt_id=cnt.sap_cnt_id

when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='ZT')
then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='ZT' and
ccpf1.sap_cnt_id=cnt.sap_cnt_id

when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='Y8')
then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
ccpf1.sap_cnt_prtnr_func_code='Y8' and
ccpf1.sap_cnt_id=cnt.sap_cnt_id
end,
cu.cntry_code,
ct.assrtmt_module_id,
cu.cust_name,
so.sap_sales_ord_num,
c.cnt_first_name,
c.cnt_last_name,
so.mod_date,
so.add_date,
soli.sales_ord_billd_flag
from
schema1.sales_ord so join schema1.sales_ord_line_item soli on
so.sap_sales_ord_num=soli.sap_sales_ord_num
join schema1.cust_cnt_prtnr_func ccpf on
so.sold_to_cust_num=ccpf.cust_num
join schema1.contact c on c.sap_cnt_id =ccpf.sap_cnt_id
join schema1.customer cu on c.cust_num=cu.cust_num
join schema1.ctrct_terms ct on so.sap_ctrct_num=ct.sap_ctrct_num
where so.line_of_bus_code='EM' and so.sap_sales_ord_num not in
(select sds.sap_doc_num
from schema1.sap_doc_stat sds join schema1.sap_doc_user_stat sdus
on sds.sap_doc_num=sds.sap_doc_num and
sds.stat_prfl='ZDPLORD'
and sdus.line_item_seq_num=0
and sdus.inact_flag=0
and (sdus.sap_doc_stat in('E0001','E0004','E0018','E0029','E0030') or
sds.ovrl_cred_stat='B')
)
and so.sap_sales_ord_num not in
(select sds.sap_doc_num
from schema1.sap_doc_stat sds join schema1.sap_doc_user_stat sdus
on sds.sap_doc_num=so.sap_sales_ord_num
and sds.stat_prfl='ZDPLORD'
and sdus.line_item_seq_num<>0
and sdus.inact_flag=0
and sdus.sap_doc_stat in ('E0001','E0002')
)

@

Satish wrote:
> I get the following error when i compile the view given below.
<snip>
... then select ...
Scalar subqueries need to be in braces:
then (select ..... )

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Satish wrote:

> I get the following error when i compile the view given below.
>
> @f3n11:/home/satish/views/> db2 -td@ -f sat.vw
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it
> returned:
> SQL0204N "SCHEMA1.TEST" is an undefined name. SQLSTATE=42704
>
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it
> returned:
> SQL0104N An unexpected token "so.sold_to_cust_num,
> so.sold_to_cust_name" was
> found following "lld_flag ) as select". Expected tokens may include:
> "<space>". SQLSTATE=42601
>
>
> --------the foll. is the view code..........
>
> drop view schema1.test
> @
> create view schema1.test
[...]
> case c.cnt_email_adr

You probably want to remove the "c.cnt_email_adr" because you don't refer to
it in the WHEN conditions below.

> when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
> so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='Z1')
> then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='Z1' and
> ccpf1.sap_cnt_id=cnt.sap_cnt_id
>
> when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
> so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='ZT')
> then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='ZT' and
> ccpf1.sap_cnt_id=cnt.sap_cnt_id
>
> when exists(select 1 from schema1.cust_cnt_prtnr_func ccpf1 where
> so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='Y8')
> then select cnt.cnt_email_adr from schema1.cust_cnt_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust_num=ccpf1.cust_num and
> ccpf1.sap_cnt_prtnr_func_code='Y8' and
> ccpf1.sap_cnt_id=cnt.sap_cnt_id
> end,

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 )

> cu.cntry_code,
> ct.assrtmt_module_id,
> cu.cust_name,
[...]

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

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