473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Error while compiling view

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
3 1786
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: tevans | last post by:
I'm using Slackware GNU/Linux 9.0 on an Intel PII 266 machine that's SMP capable, but only with one CPU installed, and 64MB of RAM. It's basically a small test system that I play around with in...
0
by: Matt | last post by:
Just out of the blue all of my code has given a 'Could not instantiate the resource processor.' error on compiling. This apparently means your installation of VS .Net has lost the plot. So I've...
2
by: Keith Bottner | last post by:
I just reinstalled my system and am in the process of getting PostgreSQL up and running again. During compilation of Postgres I received the following error: .... checking for main in...
2
by: Henrik | last post by:
im reciving an error when i tries to read multidimensional XML data, into my system. I'm receving the same errors discriped at: http://support.microsoft.com/default.aspx?scid=kb;en-us;325695...
3
by: Gary Wessle | last post by:
Hi I tried so long, so many ways to find out why this code is not compiling, I appreciate any help with it, thank you ****************************************************************...
6
by: Gaijinco | last post by:
I'm having a weird error compiling a multiple file project: I have three files: tortuga.h where I have declared 5 global variables and prototypes for some functions. tortuga.cpp where I...
3
eyeofsoul
by: eyeofsoul | last post by:
hello..i am really new to java..i am using netbean 4.1.i also install netbean 5. the problem is i got this error when compiling my CopyFile.java..the error said "java.lang.NoClassDefFoundError:...
13
by: Albert | last post by:
Hi I'm using the lcc compiler for win32. I tried compiling a program but there's an error stating: "cpp: Can't open input file clrscr()" I don't get it - I've included <tcconio.h>. (strange why...
6
by: JR | last post by:
The code below compiles and runs perfectly in Windows XP Pro, Using MS VS 2005. If I compile with g++ (cygwin) using the following command line: g++ -pedantic -Weffc++ -Wall -Wctor-dtor-privacy ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.