Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 Go to next message
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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view macs.PProd11_basis =
as<BR>select=20
&nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
- Cost=20
value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;-- =
Site-ID<BR>&nbsp;&nbsp;=20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;--=20
SubGroup-ID (String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR>&nbsp;&nbsp; join prod.RS12T pl on process.proc_id =
=3D=20
pl.proc_id and pl.mx_id is not null<BR>&nbsp;&nbsp; join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and =
site.mx_id is not=20
null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D sgrp.wert and =
sgrp.feld=20
=3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; =
join=20
prod.RS00T subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR>&nbsp;&nbsp; join prod.RS37T ctype on costs.cost_id =3D=20
ctype.cost_id<BR>&nbsp;&nbsp; left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D pplan.year=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =3D pplan.level =
and site.fa=20
=3D pplan.site<BR>where &nbsp;process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous messageGo to next message
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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;-- =
Site-ID<BR>&nbsp;&nbsp;=20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;--=20
SubGroup-ID (String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR>&nbsp;&nbsp; join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR>&nbsp;&nbsp; join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and =
site.mx_id is=20
not null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on pl.subsector =3D =
subsec.mx_id and=20
subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; join prod.RS37T ctype on=20
costs.cost_id =3D ctype.cost_id<BR>&nbsp;&nbsp; left outer join =
prod.RS48T pplan=20
on costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =3D pplan.level =
and=20
site.fa =3D pplan.site<BR>where &nbsp;process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous messageGo to next message
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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; 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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; 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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on =
costs.process_id=20
=3D process.process_id<BR>&nbsp;&nbsp; join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR>&nbsp;&nbsp; join sa.RS10T =
site on=20
pl.site =3D site.fa and site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and =
site.mx_id is=20
not null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; join =
prod.RS37T=20
ctype on costs.cost_id =3D ctype.cost_id<BR>&nbsp;&nbsp; left outer =
join=20
prod.RS48T pplan on costs.process_id =3D pplan.process_id and =
costs.planyear =3D=20
pplan.year <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =
=3D=20
pplan.level and site.fa =3D pplan.site<BR>where =
&nbsp;process.prodlevel =3D=20
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous messageGo to next message
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>&nbsp;&nbsp;&nbsp; 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&nbsp;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>&nbsp;</DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>"Roland Sch=E4tzle" &lt;<A=20
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; =
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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; =
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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =
=3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on=20
costs.process_id =3D process.process_id<BR>&nbsp;&nbsp; join =
prod.RS12T pl=20
on process.proc_id =3D pl.proc_id and pl.mx_id is not =
null<BR>&nbsp;&nbsp;=20
join sa.RS10T site on pl.site =3D site.fa and site.mx_id &lt;&gt;=20
0&nbsp;&nbsp;-- and site.mx_id is not null<BR>&nbsp;&nbsp; join =
prod.RS00T=20
sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join =
prod.RS00T=20
subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR>&nbsp;&nbsp; join prod.RS37T ctype on costs.cost_id =
=3D=20
ctype.cost_id<BR>&nbsp;&nbsp; left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =3D =
pplan.level and=20
site.fa =3D pplan.site<BR>where &nbsp;process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous message
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>&nbsp;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>&nbsp;</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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; 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>&nbsp;&nbsp;&nbsp; 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&nbsp;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>&nbsp;</DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>"Roland Sch=E4tzle" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; =
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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; =
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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =
=3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID=20
(String)<BR>from prod.RS27T costs<BR>&nbsp;&nbsp; join =
prod.RS33T=20
process on costs.process_id =3D =
process.process_id<BR>&nbsp;&nbsp; join=20
prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not=20
null<BR>&nbsp;&nbsp; join sa.RS10T site on pl.site =3D site.fa =
and=20
site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and site.mx_id is not=20
null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on =
pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; =
join=20
prod.RS37T ctype on costs.cost_id =3D =
ctype.cost_id<BR>&nbsp;&nbsp; left=20
outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and=20
costs.planyear =3D pplan.year <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
and=20
costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site<BR>where=20
&nbsp;process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") =
works in=20
DTP without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous message
Hui Cao is currently offline 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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;-- =
Site-ID<BR>&nbsp;&nbsp;=20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;--=20
SubGroup-ID (String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR>&nbsp;&nbsp; join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR>&nbsp;&nbsp; join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and =
site.mx_id is=20
not null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on pl.subsector =3D =
subsec.mx_id and=20
subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; join prod.RS37T ctype on=20
costs.cost_id =3D ctype.cost_id<BR>&nbsp;&nbsp; left outer join =
prod.RS48T pplan=20
on costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =3D pplan.level =
and=20
site.fa =3D pplan.site<BR>where &nbsp;process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous message
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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; 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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; 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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on =
costs.process_id=20
=3D process.process_id<BR>&nbsp;&nbsp; join prod.RS12T pl on =
process.proc_id =3D=20
pl.proc_id and pl.mx_id is not null<BR>&nbsp;&nbsp; join sa.RS10T =
site on=20
pl.site =3D site.fa and site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and =
site.mx_id is=20
not null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; join =
prod.RS37T=20
ctype on costs.cost_id =3D ctype.cost_id<BR>&nbsp;&nbsp; left outer =
join=20
prod.RS48T pplan on costs.process_id =3D pplan.process_id and =
costs.planyear =3D=20
pplan.year <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =
=3D=20
pplan.level and site.fa =3D pplan.site<BR>where =
&nbsp;process.prodlevel =3D=20
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous message
Hui Cao is currently offline 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>&nbsp;&nbsp;&nbsp; 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&nbsp;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>&nbsp;</DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>"Roland Sch=E4tzle" &lt;<A=20
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; =
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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; =
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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =
=3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR>&nbsp;&nbsp; join prod.RS33T process on=20
costs.process_id =3D process.process_id<BR>&nbsp;&nbsp; join =
prod.RS12T pl=20
on process.proc_id =3D pl.proc_id and pl.mx_id is not =
null<BR>&nbsp;&nbsp;=20
join sa.RS10T site on pl.site =3D site.fa and site.mx_id &lt;&gt;=20
0&nbsp;&nbsp;-- and site.mx_id is not null<BR>&nbsp;&nbsp; join =
prod.RS00T=20
sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join =
prod.RS00T=20
subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR>&nbsp;&nbsp; join prod.RS37T ctype on costs.cost_id =
=3D=20
ctype.cost_id<BR>&nbsp;&nbsp; left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D =
pplan.year=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and costs.planlevel =3D =
pplan.level and=20
site.fa =3D pplan.site<BR>where &nbsp;process.prodlevel =3D =
4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") works =
in DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</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 Go to previous message
Hui Cao is currently offline 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>&nbsp;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>&nbsp;</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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; 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>&nbsp;&nbsp;&nbsp; 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&nbsp;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>&nbsp;</DIV>
<DIV>Max (Hui) Cao<BR>Sybase Shanghai RD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>"Roland Sch=E4tzle" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; =
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" &lt;<A=20
href=3D"mailto:hui.cao@sybase.ocm">hui.cao@sybase.ocm</A>&gt; =
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>&nbsp;</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" &lt;<A=20
=
href=3D"mailto:roland.schaetzle@adviion.de">roland.schaetzle@adviion.de</=
A>&gt;=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>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view =
macs.PProd11_basis=20
as<BR>select &nbsp;<BR>&nbsp;&nbsp; costs.planyear as year,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;--=20
Scenario.year<BR>&nbsp;&nbsp; costs.periode as period,=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;-- period =
=3D=20
PlanningStage.slice<BR>&nbsp;&nbsp; costs.value,=20
=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -=
-=20
Cost value<BR>&nbsp;&nbsp; ctype.mx_costtype as=20
costtype,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& amp;nbsp;&nbsp;&nbsp;--=20
Costtype<BR>&nbsp;&nbsp; right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S,&nbsp;&nbsp;-- ProductSector.subsector-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(pl.site)) as=20
site_id_S,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;--=20
Site-ID<BR>&nbsp;&nbsp; rtrim(char(sgrp.mx_id)) as=20
subgroup_id_s,&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;-- SubGroup-ID=20
(String)<BR>&nbsp;&nbsp; rtrim(char(grp.mx_id)) as=20
group_id_s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;-- Group-ID=20
(String)<BR>from prod.RS27T costs<BR>&nbsp;&nbsp; join =
prod.RS33T=20
process on costs.process_id =3D =
process.process_id<BR>&nbsp;&nbsp; join=20
prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not=20
null<BR>&nbsp;&nbsp; join sa.RS10T site on pl.site =3D site.fa =
and=20
site.mx_id &lt;&gt; 0&nbsp;&nbsp;-- and site.mx_id is not=20
null<BR>&nbsp;&nbsp; join prod.RS00T sgrp on site.area =3D =
sgrp.wert and=20
sgrp.feld =3D 'AREA'<BR>&nbsp;&nbsp; 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>&nbsp;&nbsp; join prod.RS00T subsec on =
pl.subsector =3D=20
subsec.mx_id and subsec.feld =3D 'SubSector'<BR>&nbsp;&nbsp; =
join=20
prod.RS37T ctype on costs.cost_id =3D =
ctype.cost_id<BR>&nbsp;&nbsp; left=20
outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and=20
costs.planyear =3D pplan.year <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
and=20
costs.planlevel =3D pplan.level and site.fa =3D =
pplan.site<BR>where=20
&nbsp;process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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>&nbsp;</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>;&lt;query_expr&gt;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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.&nbsp;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&nbsp;(i.e. without "create view ...") =
works in=20
DTP without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Any=20
=
solutions?</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE ></BLOCKQUOT=
E></BODY></HTML>

------=_NextPart_000_0019_01C7D570.0CE86A00--
Previous Topic:50 records returned
Next Topic:50 records returned
Goto Forum:
  


Current Time: Tue Oct 21 07:26:38 GMT 2014

Powered by FUDForum. Page generated in 0.03080 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software