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')
)
@ 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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
****************************************************************...
|
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...
|
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:...
|
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...
|
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 ...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |