Error while compiling view

[Satish]

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.TE ST" 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_cus t_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_nu m,
sold_to_cust_na me,
cnt_email_adr,
cntry_code,
assrtmt_module_ id,
dlvry_provider_ name,
sap_sales_ord_n um,
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_cn t_prtnr_func ccpf1 where
so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'Z1')
then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'Z1' and
ccpf1.sap_cnt_i d=cnt.sap_cnt_i d

when exists(select 1 from schema1.cust_cn t_prtnr_func ccpf1 where
so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'ZT')
then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'ZT' and
ccpf1.sap_cnt_i d=cnt.sap_cnt_i d

when exists(select 1 from schema1.cust_cn t_prtnr_func ccpf1 where
so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'Y8')
then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
ccpf1,
schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
ccpf1.sap_cnt_p rtnr_func_code= 'Y8' and
ccpf1.sap_cnt_i d=cnt.sap_cnt_i d
end,
cu.cntry_code,
ct.assrtmt_modu le_id,
cu.cust_name,
so.sap_sales_or d_num,
c.cnt_first_nam e,
c.cnt_last_name ,
so.mod_date,
so.add_date,
soli.sales_ord_ billd_flag
from
schema1.sales_o rd so join schema1.sales_o rd_line_item soli on
so.sap_sales_or d_num=soli.sap_ sales_ord_num
join schema1.cust_cn t_prtnr_func ccpf on
so.sold_to_cust _num=ccpf.cust_ num
join schema1.contact c on c.sap_cnt_id =ccpf.sap_cnt_i d
join schema1.custome r cu on c.cust_num=cu.c ust_num
join schema1.ctrct_t erms ct on so.sap_ctrct_nu m=ct.sap_ctrct_ num
where so.line_of_bus_ code='EM' and so.sap_sales_or d_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_nu m and
sds.stat_prfl=' ZDPLORD'
and sdus.line_item_ seq_num=0
and sdus.inact_flag =0
and (sdus.sap_doc_s tat in('E0001','E00 04','E0018','E0 029','E0030') or
sds.ovrl_cred_s tat='B')
)
and so.sap_sales_or d_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_o rd_num
and sds.stat_prfl=' ZDPLORD'
and sdus.line_item_ seq_num<>0
and sdus.inact_flag =0
and sdus.sap_doc_st at in ('E0001','E0002 ')
)

@

[Serge Rielau]

Re: Error while compiling view

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

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

[Knut Stolze]

Re: Error while compiling view

Satish wrote:
[color=blue]
> 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.TE ST" 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_cus t_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[/color]
[...][color=blue]
> case c.cnt_email_adr[/color]

You probably want to remove the "c.cnt_email_ad r" because you don't refer to
it in the WHEN conditions below.
[color=blue]
> when exists(select 1 from schema1.cust_cn t_prtnr_func ccpf1 where
> so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'Z1')
> then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'Z1' and
> ccpf1.sap_cnt_i d=cnt.sap_cnt_i d
>
> when exists(select 1 from schema1.cust_cn t_prtnr_func ccpf1 where
> so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'ZT')
> then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'ZT' and
> ccpf1.sap_cnt_i d=cnt.sap_cnt_i d
>
> when exists(select 1 from schema1.cust_cn t_prtnr_func ccpf1 where
> so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'Y8')
> then select cnt.cnt_email_a dr from schema1.cust_cn t_prtnr_func
> ccpf1,
> schema1.contact cnt where so.sold_to_cust _num=ccpf1.cust _num and
> ccpf1.sap_cnt_p rtnr_func_code= 'Y8' and
> ccpf1.sap_cnt_i d=cnt.sap_cnt_i d
> end,[/color]

I would simplify the whole CASE expression like this:

CASE
WHEN ( SELECT ccpf1.sap_cnt_p rtnr_func_code
FROM schema1.cust_cn t_prtnr_func AS ccpf1
WHERE so.sold_to_cust _num = ccpf1.cust_num ) IN ( 'Z1', 'ZT', 'Y8' )
THEN ( SELECT cnt.cnt_email_a dr
FROM schema1.cust_cn t_prtnr_func AS ccpf1,
schema1.contact AS cnt
WHERE so.sold_to_cust _num = ccpf1.cust_num AND
ccpf1.sap_cnt_i d = 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_a dr
FROM schema1.cust_cn t_prtnr_func AS ccpf1,
schema1.contact AS cnt
WHERE so.sold_to_cust _num = ccpf1.cust_num AND
ccpf1.sap_cnt_i d = cnt.sap_cnt_id )
[color=blue]
> cu.cntry_code,
> ct.assrtmt_modu le_id,
> cu.cust_name,[/color]
[...]

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

[Knut Stolze]

Re: Error while compiling view

Knut Stolze wrote:
[color=blue]
> I would simplify the whole CASE expression like this:
>
> CASE
> WHEN ( SELECT ccpf1.sap_cnt_p rtnr_func_code
> FROM schema1.cust_cn t_prtnr_func AS ccpf1
> WHERE so.sold_to_cust _num = ccpf1.cust_num ) IN ( 'Z1', 'ZT', 'Y8'
> )
> THEN ( SELECT cnt.cnt_email_a dr
> FROM schema1.cust_cn t_prtnr_func AS ccpf1,
> schema1.contact AS cnt
> WHERE so.sold_to_cust _num = ccpf1.cust_num AND
> ccpf1.sap_cnt_i d = 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_a dr
> FROM schema1.cust_cn t_prtnr_func AS ccpf1,
> schema1.contact AS cnt
> WHERE so.sold_to_cust _num = ccpf1.cust_num AND
> ccpf1.sap_cnt_i d = cnt.sap_cnt_id )[/color]

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

( SELECT cnt.cnt_email_a dr
FROM schema1.cust_cn t_prtnr_func AS ccpf1,
schema1.contact AS cnt
WHERE so.sold_to_cust _num = ccpf1.cust_num AND
ccpf1.sap_cnt_p rtnr_func_code IN ( 'Z1', 'ZT', 'Y8' ) AND
ccpf1.sap_cnt_i d = 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