Home » Eclipse Projects » DTP » cannot create view (DB2) -- correct SQL statement does not work
| cannot create view (DB2) -- correct SQL statement does not work [message #33493] |
Thu, 26 July 2007 14:53  |
Eclipse User |
|
|
|
Originally posted by: roland.schaetzle.adviion.de
This is a multi-part message in MIME format.
------=_NextPart_000_0018_01C7CFA5.89340630
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID (String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and grp.feld =
=3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. Expected =
tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at the =
specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement that =
preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of the =
specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and costs.planlevel =
=3D pplan.level" expression. If it is omitted, then the statement works =
also with DTP.
Apart from that, DTP is very problematic if comments are used within an =
SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0018_01C7CFA5.89340630
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16481" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following view =
on a DB2=20
database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view macs.PProd11_basis =
as<BR>select=20
<BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
-=
- Cost=20
value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, -- =
Site-ID<BR> =20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s, &n bsp;--=20
SubGroup-ID (String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR> join prod.RS12T pl on process.proc_id =
=3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is not=20
null<BR> join prod.RS00T sgrp on site.area =3D sgrp.wert and =
sgrp.feld=20
=3D 'AREA'<BR> join prod.RS00T grp on site.konzernbereich =
=3D grp.wert=20
and grp.feld =3D 'K-BEREICH' and grp.mx_id is not null<BR> =
join=20
prod.RS00T subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR> join prod.RS37T ctype on costs.cost_id =3D=20
ctype.cost_id<BR> left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D pplan.year=20
<BR> and costs.planlevel =3D pplan.level =
and site.fa=20
=3D pplan.site<BR>where process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following error =
message from=20
DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis =
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE: 42601,=20
SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token token =
was found=20
following text. Expected tokens may include: token-list. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in the =
SQL statement=20
was detected at the specified token following the text text. <BR>The =
text=20
field indicates the 20 characters of the SQL statement that preceded =
the token=20
that is not valid. <BR>As an aid to the programmer, a partial list of =
valid=20
tokens is provided in the SQLERRM field of the SQLCA as token-list. =
<BR>This=20
list assumes the statement is correct to that point. The statement =
cannot be=20
processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and correct =
the statement=20
in the area of the specified token. sqlcode: -104 sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the SQL =
statement is=20
syntactically</STRONG> <STRONG>correct</STRONG>. It works with other SQL =
tools=20
(not DTP) without problems. So it is not a problem of the statement or=20
DB2. I suspect that DTP changes the statement in some way. But how =
and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: The=20
Select-statement alone (i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic part. =
It is the=20
"<FONT face=3D"Courier New">and costs.planlevel =3D pplan.level</FONT>" =
expression.=20
If it is omitted, then the statement works also with DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases to =
the same=20
problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any =
solutions?</FONT></DIV></BODY></HTML>
------=_NextPart_000_0018_01C7CFA5.89340630--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #33669 is a reply to message #33493] |
Fri, 27 July 2007 06:59  |
| Eclipse User |
|
|
|
Originally posted by: hui.cao.sybase.ocm
This is a multi-part message in MIME format.
------=_NextPart_000_00C6_01C7D05E.B9B7EB50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Roland,
When you execute those statements, which kind of database server type do =
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id =
is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- =
and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at the =
specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement that =
preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of the =
specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and costs.planlevel =
=3D pplan.level" expression. If it is omitted, then the statement works =
also with DTP.
Apart from that, DTP is very problematic if comments are used within =
an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_00C6_01C7D05E.B9B7EB50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff><FONT face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database server =
type do=20
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor=20
framework? I asked this because by default, SQL Editor simply returns =
the=20
original SQL statements, unless a parser is found, which can be used to =
separate=20
the SQL statements. In case you want to debug it for yourself, you can =
look at=20
SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following =
view on a DB2=20
database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, -- =
Site-ID<BR> =20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s, &n bsp;--=20
SubGroup-ID (String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR> join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is=20
not null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on =
site.konzernbereich=20
=3D grp.wert and grp.feld =3D 'K-BEREICH' and grp.mx_id is not=20
null<BR> join prod.RS00T subsec on pl.subsector =3D =
subsec.mx_id and=20
subsec.feld =3D 'SubSector'<BR> join prod.RS37T ctype on=20
costs.cost_id =3D ctype.cost_id<BR> left outer join =
prod.RS48T pplan=20
on costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR> and costs.planlevel =3D pplan.level =
and=20
site.fa =3D pplan.site<BR>where process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following error =
message from=20
DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis =
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE: 42601,=20
SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token token =
was found=20
following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in the =
SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL statement =
that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of the =
SQLCA=20
as token-list. <BR>This list assumes the statement is correct to =
that point.=20
The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and correct =
the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the SQL =
statement is=20
syntactically</STRONG> <STRONG>correct</STRONG>. It works with other =
SQL tools=20
(not DTP) without problems. So it is not a problem of the statement or =
DB2. I suspect that DTP changes the statement in some way. But =
how and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: The=20
Select-statement alone (i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is the=20
"<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases to =
the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
solutions?</FONT></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_00C6_01C7D05E.B9B7EB50--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #33772 is a reply to message #33669] |
Tue, 31 July 2007 16:05 |
| Eclipse User |
|
|
|
Originally posted by: roland.schaetzle.adviion.de
This is a multi-part message in MIME format.
------=_NextPart_000_0009_01C7D39D.706FA680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server type =
do you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id =
is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- =
and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at =
the specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement =
that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of =
the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used within =
an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0009_01C7D39D.706FA680
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16481" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) =
driver=20
definition in Eclipse. Therefore I wonder why the statement doesen't =
work with=20
DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several other =
statements (in=20
the form "create table xxxx as (select ...)") which don't work either =
with DTP,=20
but without problems if I'm using other tools :-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> schrieb =
im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database server =
type do=20
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor=20
framework? I asked this because by default, SQL Editor simply returns =
the=20
original SQL statements, unless a parser is found, which can be used =
to=20
separate the SQL statements. In case you want to debug it for =
yourself, you=20
can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following =
view on a=20
DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id=20
=3D process.process_id<BR> join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T =
site on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is=20
not null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on=20
site.konzernbereich =3D grp.wert and grp.feld =3D 'K-BEREICH' and =
grp.mx_id is=20
not null<BR> join prod.RS00T subsec on pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR> join =
prod.RS37T=20
ctype on costs.cost_id =3D ctype.cost_id<BR> left outer =
join=20
prod.RS48T pplan on costs.process_id =3D pplan.process_id and =
costs.planyear =3D=20
pplan.year <BR> and costs.planlevel =
=3D=20
pplan.level and site.fa =3D pplan.site<BR>where =
process.prodlevel =3D=20
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error message=20
from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was found=20
following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in =
the SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of =
the SQLCA=20
as token-list. <BR>This list assumes the statement is correct to =
that=20
point. The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the =
SQL statement=20
is syntactically</STRONG> <STRONG>correct</STRONG>. It works with =
other SQL=20
tools (not DTP) without problems. So it is not a problem of the =
statement or=20
DB2. I suspect that DTP changes the statement in some way. But =
how and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: =
The=20
Select-statement alone (i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is=20
the "<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases =
to the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY ></HTML>
------=_NextPart_000_0009_01C7D39D.706FA680--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #33845 is a reply to message #33772] |
Thu, 02 August 2007 15:14 |
| Eclipse User |
|
|
|
Originally posted by: hui.cao.sybase.ocm
This is a multi-part message in MIME format.
------=_NextPart_000_0034_01C7D55A.F263D3F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Roland,
I could not figure out what has happened... There was a bug in the =
SQLService several month ago, but it was fixed. Please make sure you're =
using DTP 1.5 and you're running these statements in DTP Scrapbook =
Editor: in Database Development perspective, there's a "Open Scrapbook" =
workbench toolbar button to create a Scrapbook Editor.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8nnre$p6b$1@build.eclipse.org...
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server =
type do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using =
DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and =
pl.mx_id is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 =
-- and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =
=3D 'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at =
the specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement =
that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of =
the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. =
It works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used =
within an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0034_01C7D55A.F263D3F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Roland,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> I could not figure =
out what has=20
happened... There was a bug in the SQLService several month ago, but it =
was=20
fixed. Please make sure you're using DTP 1.5 and you're running these =
statements=20
in DTP Scrapbook Editor: in Database Development perspective, =
there's a=20
"Open Scrapbook" workbench toolbar button to create a Scrapbook=20
Editor.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>"Roland Sch=E4tzle" <<A=20
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
href=3D"news:f8nnre$p6b$1@build.eclipse.org">news:f8nnre$p6b$1@build.ecli=
pse.org</A>...</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) =
driver=20
definition in Eclipse. Therefore I wonder why the statement doesen't =
work with=20
DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several other =
statements=20
(in the form "create table xxxx as (select ...)") which don't work =
either with=20
DTP, but without problems if I'm using other tools :-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> =
Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> =
schrieb im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database =
server type=20
do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL=20
Editor framework? I asked this because by default, SQL Editor simply =
returns=20
the original SQL statements, unless a parser is found, which can be =
used to=20
separate the SQL statements. In case you want to debug it for =
yourself, you=20
can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the =
following view on a=20
DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =
=3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on=20
costs.process_id =3D process.process_id<BR> join =
prod.RS12T pl=20
on process.proc_id =3D pl.proc_id and pl.mx_id is not =
null<BR> =20
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <>=20
0 -- and site.mx_id is not null<BR> join =
prod.RS00T=20
sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'<BR> join=20
prod.RS00T grp on site.konzernbereich =3D grp.wert and grp.feld =
=3D=20
'K-BEREICH' and grp.mx_id is not null<BR> join =
prod.RS00T=20
subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR> join prod.RS37T ctype on costs.cost_id =
=3D=20
ctype.cost_id<BR> left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR> and costs.planlevel =3D =
pplan.level and=20
site.fa =3D pplan.site<BR>where process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error message=20
from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was=20
found following text. Expected tokens may include: token-list.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in =
the SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of =
the=20
SQLCA as token-list. <BR>This list assumes the statement is =
correct to=20
that point. The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the =
SQL=20
statement is syntactically</STRONG> <STRONG>correct</STRONG>. It =
works=20
with other SQL tools (not DTP) without problems. So it is not a =
problem of=20
the statement or DB2. I suspect that DTP changes the =
statement in=20
some way. But how and why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: =
The=20
Select-statement alone (i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is=20
the "<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many =
cases to the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
=
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE ></BODY></HT=
ML>
------=_NextPart_000_0034_01C7D55A.F263D3F0--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #33906 is a reply to message #33845] |
Thu, 02 August 2007 17:46 |
| Eclipse User |
|
|
|
Originally posted by: hui.cao.sybase.ocm
This is a multi-part message in MIME format.
------=_NextPart_000_0019_01C7D570.0CE86A00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Sorry, spoke too fast. This is really a bug and I have fixed it and =
attached the test case. You can go to =
https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D198719 for details. Tell =
me if you have further questions. Thanks for the information.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Hui Cao" <hui.cao@sybase.ocm> wrote in message =
news:f8ssdm$f8q$1@build.eclipse.org...
Hi Roland,
I could not figure out what has happened... There was a bug in the =
SQLService several month ago, but it was fixed. Please make sure you're =
using DTP 1.5 and you're running these statements in DTP Scrapbook =
Editor: in Database Development perspective, there's a "Open Scrapbook" =
workbench toolbar button to create a Scrapbook Editor.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8nnre$p6b$1@build.eclipse.org...
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server =
type do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in =
message news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using =
DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID =
(String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and =
pl.mx_id is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 =
-- and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =
=3D 'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected =
at the specified token following the text text.=20
The text field indicates the 20 characters of the SQL =
statement that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area =
of the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. =
It works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone =
(i.e. without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used =
within an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0019_01C7D570.0CE86A00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Sorry, spoke too fast. This is really a =
bug and I=20
have fixed it and attached the test case. You can go to <A=20
href=3D"https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D198719">https://b=
ugs.eclipse.org/bugs/show_bug.cgi?id=3D198719</A> for=20
details. Tell me if you have further questions. Thanks for the=20
information.</FONT></DIV><FONT face=3DArial size=3D2>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> wrote in =
message=20
<A=20
=
href=3D"news:f8ssdm$f8q$1@build.eclipse.org">news:f8ssdm$f8q$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Hi Roland,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> I could not figure =
out what=20
has happened... There was a bug in the SQLService several month ago, =
but it=20
was fixed. Please make sure you're using DTP 1.5 and you're running =
these=20
statements in DTP Scrapbook Editor: in Database Development =
perspective,=20
there's a "Open Scrapbook" workbench toolbar button to create a =
Scrapbook=20
Editor.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8nnre$p6b$1@build.eclipse.org">news:f8nnre$p6b$1@build.ecli=
pse.org</A>...</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V =
8.2)=20
driver definition in Eclipse. Therefore I wonder why the statement =
doesen't=20
work with DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several =
other statements=20
(in the form "create table xxxx as (select ...)") which don't work =
either=20
with DTP, but without problems if I'm using other tools =
:-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> =
Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> =
schrieb im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database =
server type=20
do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL=20
Editor framework? I asked this because by default, SQL Editor =
simply=20
returns the original SQL statements, unless a parser is found, =
which can=20
be used to separate the SQL statements. In case you want to debug =
it for=20
yourself, you can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the =
following view on=20
a DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =
=3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID=20
(String)<BR>from prod.RS27T costs<BR> join =
prod.RS33T=20
process on costs.process_id =3D =
process.process_id<BR> join=20
prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not=20
null<BR> join sa.RS10T site on pl.site =3D site.fa =
and=20
site.mx_id <> 0 -- and site.mx_id is not=20
null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on=20
site.konzernbereich =3D grp.wert and grp.feld =3D 'K-BEREICH' =
and grp.mx_id=20
is not null<BR> join prod.RS00T subsec on =
pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR> =
join=20
prod.RS37T ctype on costs.cost_id =3D =
ctype.cost_id<BR> left=20
outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and=20
costs.planyear =3D pplan.year <BR> =
and=20
costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site<BR>where=20
process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error=20
message from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view =
macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was=20
found following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error =
in the SQL=20
statement was detected at the specified token following the =
text text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement=20
that preceded the token that is not valid. <BR>As an aid to =
the=20
programmer, a partial list of valid tokens is provided in the =
SQLERRM=20
field of the SQLCA as token-list. <BR>This list assumes the =
statement=20
is correct to that point. The statement cannot be processed.=20
</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that =
<STRONG>the SQL=20
statement is syntactically</STRONG> <STRONG>correct</STRONG>. It =
works=20
with other SQL tools (not DTP) without problems. So it is not a =
problem=20
of the statement or DB2. I suspect that DTP changes the =
statement=20
in some way. But how and why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting =
observation: The=20
Select-statement alone (i.e. without "create view ...") =
works in=20
DTP without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the =
problematic part. It=20
is the "<FONT face=3D"Courier New">and costs.planlevel =3D=20
pplan.level</FONT>" expression. If it is omitted, then the =
statement=20
works also with DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many =
cases to=20
the same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
=
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE ></BLOCKQUOT=
E></BODY></HTML>
------=_NextPart_000_0019_01C7D570.0CE86A00--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #586908 is a reply to message #33493] |
Fri, 27 July 2007 06:59 |
 Hui Cao
Messages: 29
Registered: July 2009 |
Junior Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_00C6_01C7D05E.B9B7EB50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Roland,
When you execute those statements, which kind of database server type do =
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id =
is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- =
and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at the =
specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement that =
preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of the =
specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and costs.planlevel =
=3D pplan.level" expression. If it is omitted, then the statement works =
also with DTP.
Apart from that, DTP is very problematic if comments are used within =
an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_00C6_01C7D05E.B9B7EB50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff><FONT face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database server =
type do=20
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor=20
framework? I asked this because by default, SQL Editor simply returns =
the=20
original SQL statements, unless a parser is found, which can be used to =
separate=20
the SQL statements. In case you want to debug it for yourself, you can =
look at=20
SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following =
view on a DB2=20
database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, -- =
Site-ID<BR> =20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s, &n bsp;--=20
SubGroup-ID (String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR> join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is=20
not null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on =
site.konzernbereich=20
=3D grp.wert and grp.feld =3D 'K-BEREICH' and grp.mx_id is not=20
null<BR> join prod.RS00T subsec on pl.subsector =3D =
subsec.mx_id and=20
subsec.feld =3D 'SubSector'<BR> join prod.RS37T ctype on=20
costs.cost_id =3D ctype.cost_id<BR> left outer join =
prod.RS48T pplan=20
on costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR> and costs.planlevel =3D pplan.level =
and=20
site.fa =3D pplan.site<BR>where process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following error =
message from=20
DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis =
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE: 42601,=20
SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token token =
was found=20
following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in the =
SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL statement =
that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of the =
SQLCA=20
as token-list. <BR>This list assumes the statement is correct to =
that point.=20
The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and correct =
the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the SQL =
statement is=20
syntactically</STRONG> <STRONG>correct</STRONG>. It works with other =
SQL tools=20
(not DTP) without problems. So it is not a problem of the statement or =
DB2. I suspect that DTP changes the statement in some way. But =
how and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: The=20
Select-statement alone (i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is the=20
"<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases to =
the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
solutions?</FONT></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_00C6_01C7D05E.B9B7EB50--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #586945 is a reply to message #33669] |
Tue, 31 July 2007 16:05 |
| Eclipse User |
|
|
|
Originally posted by: roland.schaetzle.adviion.de
This is a multi-part message in MIME format.
------=_NextPart_000_0009_01C7D39D.706FA680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server type =
do you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id =
is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- =
and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at =
the specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement =
that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of =
the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used within =
an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0009_01C7D39D.706FA680
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16481" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) =
driver=20
definition in Eclipse. Therefore I wonder why the statement doesen't =
work with=20
DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several other =
statements (in=20
the form "create table xxxx as (select ...)") which don't work either =
with DTP,=20
but without problems if I'm using other tools :-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> schrieb =
im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database server =
type do=20
you use in the SQL Editor? Do you have any DB2 extension to the SQL =
Editor=20
framework? I asked this because by default, SQL Editor simply returns =
the=20
original SQL statements, unless a parser is found, which can be used =
to=20
separate the SQL statements. In case you want to debug it for =
yourself, you=20
can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following =
view on a=20
DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id=20
=3D process.process_id<BR> join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T =
site on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is=20
not null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on=20
site.konzernbereich =3D grp.wert and grp.feld =3D 'K-BEREICH' and =
grp.mx_id is=20
not null<BR> join prod.RS00T subsec on pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR> join =
prod.RS37T=20
ctype on costs.cost_id =3D ctype.cost_id<BR> left outer =
join=20
prod.RS48T pplan on costs.process_id =3D pplan.process_id and =
costs.planyear =3D=20
pplan.year <BR> and costs.planlevel =
=3D=20
pplan.level and site.fa =3D pplan.site<BR>where =
process.prodlevel =3D=20
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error message=20
from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was found=20
following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in =
the SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of =
the SQLCA=20
as token-list. <BR>This list assumes the statement is correct to =
that=20
point. The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the =
SQL statement=20
is syntactically</STRONG> <STRONG>correct</STRONG>. It works with =
other SQL=20
tools (not DTP) without problems. So it is not a problem of the =
statement or=20
DB2. I suspect that DTP changes the statement in some way. But =
how and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: =
The=20
Select-statement alone (i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is=20
the "<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases =
to the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY ></HTML>
------=_NextPart_000_0009_01C7D39D.706FA680--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #586969 is a reply to message #33772] |
Thu, 02 August 2007 15:14 |
Hui Cao
Messages: 29
Registered: July 2009 |
Junior Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0034_01C7D55A.F263D3F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Roland,
I could not figure out what has happened... There was a bug in the =
SQLService several month ago, but it was fixed. Please make sure you're =
using DTP 1.5 and you're running these statements in DTP Scrapbook =
Editor: in Database Development perspective, there's a "Open Scrapbook" =
workbench toolbar button to create a Scrapbook Editor.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8nnre$p6b$1@build.eclipse.org...
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server =
type do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using =
DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and =
pl.mx_id is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 =
-- and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =
=3D 'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at =
the specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement =
that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of =
the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. =
It works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used =
within an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0034_01C7D55A.F263D3F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Roland,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> I could not figure =
out what has=20
happened... There was a bug in the SQLService several month ago, but it =
was=20
fixed. Please make sure you're using DTP 1.5 and you're running these =
statements=20
in DTP Scrapbook Editor: in Database Development perspective, =
there's a=20
"Open Scrapbook" workbench toolbar button to create a Scrapbook=20
Editor.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>"Roland Sch=E4tzle" <<A=20
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
href=3D"news:f8nnre$p6b$1@build.eclipse.org">news:f8nnre$p6b$1@build.ecli=
pse.org</A>...</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) =
driver=20
definition in Eclipse. Therefore I wonder why the statement doesen't =
work with=20
DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several other =
statements=20
(in the form "create table xxxx as (select ...)") which don't work =
either with=20
DTP, but without problems if I'm using other tools :-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> =
Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> =
schrieb im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database =
server type=20
do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL=20
Editor framework? I asked this because by default, SQL Editor simply =
returns=20
the original SQL statements, unless a parser is found, which can be =
used to=20
separate the SQL statements. In case you want to debug it for =
yourself, you=20
can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the =
following view on a=20
DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =
=3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on=20
costs.process_id =3D process.process_id<BR> join =
prod.RS12T pl=20
on process.proc_id =3D pl.proc_id and pl.mx_id is not =
null<BR> =20
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <>=20
0 -- and site.mx_id is not null<BR> join =
prod.RS00T=20
sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'<BR> join=20
prod.RS00T grp on site.konzernbereich =3D grp.wert and grp.feld =
=3D=20
'K-BEREICH' and grp.mx_id is not null<BR> join =
prod.RS00T=20
subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR> join prod.RS37T ctype on costs.cost_id =
=3D=20
ctype.cost_id<BR> left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR> and costs.planlevel =3D =
pplan.level and=20
site.fa =3D pplan.site<BR>where process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error message=20
from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was=20
found following text. Expected tokens may include: token-list.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in =
the SQL=20
statement was detected at the specified token following the text =
text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement that=20
preceded the token that is not valid. <BR>As an aid to the =
programmer, a=20
partial list of valid tokens is provided in the SQLERRM field of =
the=20
SQLCA as token-list. <BR>This list assumes the statement is =
correct to=20
that point. The statement cannot be processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the =
SQL=20
statement is syntactically</STRONG> <STRONG>correct</STRONG>. It =
works=20
with other SQL tools (not DTP) without problems. So it is not a =
problem of=20
the statement or DB2. I suspect that DTP changes the =
statement in=20
some way. But how and why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: =
The=20
Select-statement alone (i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic =
part. It is=20
the "<FONT face=3D"Courier New">and costs.planlevel =3D =
pplan.level</FONT>"=20
expression. If it is omitted, then the statement works also with=20
DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many =
cases to the=20
same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
=
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE ></BODY></HT=
ML>
------=_NextPart_000_0034_01C7D55A.F263D3F0--
|
|
|
| Re: cannot create view (DB2) -- correct SQL statement does not work [message #586994 is a reply to message #33845] |
Thu, 02 August 2007 17:46 |
Hui Cao
Messages: 29
Registered: July 2009 |
Junior Member |
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0019_01C7D570.0CE86A00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Sorry, spoke too fast. This is really a bug and I have fixed it and =
attached the test case. You can go to =
https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D198719 for details. Tell =
me if you have further questions. Thanks for the information.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Hui Cao" <hui.cao@sybase.ocm> wrote in message =
news:f8ssdm$f8q$1@build.eclipse.org...
Hi Roland,
I could not figure out what has happened... There was a bug in the =
SQLService several month ago, but it was fixed. Please make sure you're =
using DTP 1.5 and you're running these statements in DTP Scrapbook =
Editor: in Database Development perspective, there's a "Open Scrapbook" =
workbench toolbar button to create a Scrapbook Editor.
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in message =
news:f8nnre$p6b$1@build.eclipse.org...
Hi Max,
I'm acessing DB2 simply via the DB2 JDBC driver that comes with DB2 =
(db2jcc.jar) using the "IBM DB2 Universal" (V 8.2) driver definition in =
Eclipse. Therefore I wonder why the statement doesen't work with DTP.=20
In the meantime I have several other statements (in the form "create =
table xxxx as (select ...)") which don't work either with DTP, but =
without problems if I'm using other tools :-(
Best regards,
Roland
"Hui Cao" <hui.cao@sybase.ocm> schrieb im Newsbeitrag =
news:f8c54f$hhu$1@build.eclipse.org...
Hi Roland,
When you execute those statements, which kind of database server =
type do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL Editor framework? I asked this because by default, SQL Editor simply =
returns the original SQL statements, unless a parser is found, which can =
be used to separate the SQL statements. In case you want to debug it for =
yourself, you can look at SQLServer.splitSQL(String sql).
--=20
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
"Roland Sch=E4tzle" <roland.schaetzle@adviion.de> wrote in =
message news:f8adnh$a4c$1@build.eclipse.org...
I'm trying to create the following view on a DB2 database using =
DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D =
PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID =
(String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID =
(String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D =
process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and =
pl.mx_id is not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 =
-- and site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =
=3D 'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and =
grp.feld =3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. =
Expected tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected =
at the specified token following the text text.=20
The text field indicates the 20 characters of the SQL =
statement that preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area =
of the specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. =
It works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone =
(i.e. without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and =
costs.planlevel =3D pplan.level" expression. If it is omitted, then the =
statement works also with DTP.
Apart from that, DTP is very problematic if comments are used =
within an SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0019_01C7D570.0CE86A00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3132" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Sorry, spoke too fast. This is really a =
bug and I=20
have fixed it and attached the test case. You can go to <A=20
href=3D"https://bugs.eclipse.org/bugs/show_bug.cgi?id=3D198719">https://b=
ugs.eclipse.org/bugs/show_bug.cgi?id=3D198719</A> for=20
details. Tell me if you have further questions. Thanks for the=20
information.</FONT></DIV><FONT face=3DArial size=3D2>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> wrote in =
message=20
<A=20
=
href=3D"news:f8ssdm$f8q$1@build.eclipse.org">news:f8ssdm$f8q$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Hi Roland,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> I could not figure =
out what=20
has happened... There was a bug in the SQLService several month ago, =
but it=20
was fixed. Please make sure you're using DTP 1.5 and you're running =
these=20
statements in DTP Scrapbook Editor: in Database Development =
perspective,=20
there's a "Open Scrapbook" workbench toolbar button to create a =
Scrapbook=20
Editor.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8nnre$p6b$1@build.eclipse.org">news:f8nnre$p6b$1@build.ecli=
pse.org</A>...</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>Hi Max,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm acessing DB2 simply via the DB2 =
JDBC driver=20
that comes with DB2 (db2jcc.jar) using the "IBM DB2 Universal" (V =
8.2)=20
driver definition in Eclipse. Therefore I wonder why the statement =
doesen't=20
work with DTP. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In the meantime I have several =
other statements=20
(in the form "create table xxxx as (select ...)") which don't work =
either=20
with DTP, but without problems if I'm using other tools =
:-(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> =
Roland</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Hui Cao" <<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>> =
schrieb im=20
Newsbeitrag <A=20
=
href=3D"news:f8c54f$hhu$1@build.eclipse.org">news:f8c54f$hhu$1@build.ecli=
pse.org</A>...</DIV><FONT=20
face=3DArial size=3D2>
<DIV>Hi Roland,</DIV>
<DIV>When you execute those statements, which kind of database =
server type=20
do you use in the SQL Editor? Do you have any DB2 extension to the =
SQL=20
Editor framework? I asked this because by default, SQL Editor =
simply=20
returns the original SQL statements, unless a parser is found, =
which can=20
be used to separate the SQL statements. In case you want to debug =
it for=20
yourself, you can look at SQLServer.splitSQL(String sql).</DIV>
<DIV><BR>-- <BR>Best Regards!</DIV>
<DIV> </DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Roland Sch=E4tzle" <<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>>=20
wrote in message <A=20
=
href=3D"news:f8adnh$a4c$1@build.eclipse.org">news:f8adnh$a4c$1@build.ecli=
pse.org</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the =
following view on=20
a DB2 database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select <BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =
=3D=20
PlanningStage.slice<BR> costs.value,=20
=
-=
-=20
Cost value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, --=20
Site-ID<BR> rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s, &n bsp;-- SubGroup-ID=20
(String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID=20
(String)<BR>from prod.RS27T costs<BR> join =
prod.RS33T=20
process on costs.process_id =3D =
process.process_id<BR> join=20
prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not=20
null<BR> join sa.RS10T site on pl.site =3D site.fa =
and=20
site.mx_id <> 0 -- and site.mx_id is not=20
null<BR> join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR> join prod.RS00T grp on=20
site.konzernbereich =3D grp.wert and grp.feld =3D 'K-BEREICH' =
and grp.mx_id=20
is not null<BR> join prod.RS00T subsec on =
pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR> =
join=20
prod.RS37T ctype on costs.cost_id =3D =
ctype.cost_id<BR> left=20
outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and=20
costs.planyear =3D pplan.year <BR> =
and=20
costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site<BR>where=20
process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following =
error=20
message from DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view =
macs.PProd11_basis=20
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE:=20
42601, SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token =
token was=20
found following text. Expected tokens may include: token-list. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error =
in the SQL=20
statement was detected at the specified token following the =
text text.=20
<BR>The text field indicates the 20 characters of the SQL =
statement=20
that preceded the token that is not valid. <BR>As an aid to =
the=20
programmer, a partial list of valid tokens is provided in the =
SQLERRM=20
field of the SQLCA as token-list. <BR>This list assumes the =
statement=20
is correct to that point. The statement cannot be processed.=20
</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and =
correct the=20
statement in the area of the specified token. sqlcode: -104 =
sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that =
<STRONG>the SQL=20
statement is syntactically</STRONG> <STRONG>correct</STRONG>. It =
works=20
with other SQL tools (not DTP) without problems. So it is not a =
problem=20
of the statement or DB2. I suspect that DTP changes the =
statement=20
in some way. But how and why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting =
observation: The=20
Select-statement alone (i.e. without "create view ...") =
works in=20
DTP without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the =
problematic part. It=20
is the "<FONT face=3D"Courier New">and costs.planlevel =3D=20
pplan.level</FONT>" expression. If it is omitted, then the =
statement=20
works also with DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many =
cases to=20
the same problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
=
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE ></BLOCKQUOT=
E></BODY></HTML>
------=_NextPart_000_0019_01C7D570.0CE86A00--
|
|
|
Goto Forum:
Current Time: Tue Apr 16 21:50:24 GMT 2024
Powered by FUDForum. Page generated in 1.18559 seconds |
] 
Search
Help
Register
Login
Home
