By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

Error while compiling view

P: n/a
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')
)

@

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.