| cannot create view (DB2) -- correct SQL statement does not work [message #586837] |
Thu, 26 July 2007 14:53 |
Eclipse User |
|
|
|
Originally posted by: roland.schaetzle.adviion.de
This is a multi-part message in MIME format.
------=_NextPart_000_0018_01C7CFA5.89340630
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm trying to create the following view on a DB2 database using DTP:
create view macs.PProd11_basis as
select =20
costs.planyear as year, -- Scenario.year
costs.periode as period, -- period =3D PlanningStage.slice
costs.value, -- Cost value
ctype.mx_costtype as costtype, -- Costtype
right(rtrim(char(subsec.mx_id)), 2) as subsector_id_S, -- =
ProductSector.subsector-ID (String)
rtrim(char(pl.site)) as site_id_S, -- Site-ID
rtrim(char(sgrp.mx_id)) as subgroup_id_s, -- SubGroup-ID (String)
rtrim(char(grp.mx_id)) as group_id_s -- Group-ID (String)
from prod.RS27T costs
join prod.RS33T process on costs.process_id =3D process.process_id
join prod.RS12T pl on process.proc_id =3D pl.proc_id and pl.mx_id is =
not null
join sa.RS10T site on pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is not null
join prod.RS00T sgrp on site.area =3D sgrp.wert and sgrp.feld =3D =
'AREA'
join prod.RS00T grp on site.konzernbereich =3D grp.wert and grp.feld =
=3D 'K-BEREICH' and grp.mx_id is not null
join prod.RS00T subsec on pl.subsector =3D subsec.mx_id and =
subsec.feld =3D 'SubSector'
join prod.RS37T ctype on costs.cost_id =3D ctype.cost_id
left outer join prod.RS48T pplan on costs.process_id =3D =
pplan.process_id and costs.planyear =3D pplan.year=20
and costs.planlevel =3D pplan.level and site.fa =3D pplan.site
where process.prodlevel =3D 4
which results in the following error message from DB2:
create view macs.PProd11_basis as
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: =
END-OF-STATEMENT;s.PProd11_basis as
;<query_expr>
Which means:
SQL0104N An unexpected token token was found following text. Expected =
tokens may include: token-list.=20
Explanation: A syntax error in the SQL statement was detected at the =
specified token following the text text.=20
The text field indicates the 20 characters of the SQL statement that =
preceded the token that is not valid.=20
As an aid to the programmer, a partial list of valid tokens is =
provided in the SQLERRM field of the SQLCA as token-list.=20
This list assumes the statement is correct to that point. The =
statement cannot be processed.=20
User Response: Examine and correct the statement in the area of the =
specified token. sqlcode: -104 sqlstate: 42601
The problem is, that the SQL statement is syntactically correct. It =
works with other SQL tools (not DTP) without problems. So it is not a =
problem of the statement or DB2. I suspect that DTP changes the =
statement in some way. But how and why?
Another interesting observation: The Select-statement alone (i.e. =
without "create view ...") works in DTP without problems.
I could identify the problematic part. It is the "and costs.planlevel =
=3D pplan.level" expression. If it is omitted, then the statement works =
also with DTP.
Apart from that, DTP is very problematic if comments are used within an =
SQL statement. This leads in many cases to the same problem.
Any solutions?
------=_NextPart_000_0018_01C7CFA5.89340630
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16481" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>I'm trying to create the following view =
on a DB2=20
database using DTP:</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create view macs.PProd11_basis =
as<BR>select=20
<BR> costs.planyear as year,=20
--=20
Scenario.year<BR> costs.periode as period,=20
-- period =3D=20
PlanningStage.slice<BR> costs.value,=20
-=
- Cost=20
value<BR> ctype.mx_costtype as=20
costtype, & amp;nbsp; --=20
Costtype<BR> right(rtrim(char(subsec.mx_id)), 2) as=20
subsector_id_S, -- ProductSector.subsector-ID=20
(String)<BR> rtrim(char(pl.site)) as=20
site_id_S, -- =
Site-ID<BR> =20
rtrim(char(sgrp.mx_id)) as =
subgroup_id_s, &n bsp;--=20
SubGroup-ID (String)<BR> rtrim(char(grp.mx_id)) as=20
group_id_s -- Group-ID =
(String)<BR>from=20
prod.RS27T costs<BR> join prod.RS33T process on =
costs.process_id =3D=20
process.process_id<BR> join prod.RS12T pl on process.proc_id =
=3D=20
pl.proc_id and pl.mx_id is not null<BR> join sa.RS10T site =
on=20
pl.site =3D site.fa and site.mx_id <> 0 -- and =
site.mx_id is not=20
null<BR> join prod.RS00T sgrp on site.area =3D sgrp.wert and =
sgrp.feld=20
=3D 'AREA'<BR> join prod.RS00T grp on site.konzernbereich =
=3D grp.wert=20
and grp.feld =3D 'K-BEREICH' and grp.mx_id is not null<BR> =
join=20
prod.RS00T subsec on pl.subsector =3D subsec.mx_id and subsec.feld =3D=20
'SubSector'<BR> join prod.RS37T ctype on costs.cost_id =3D=20
ctype.cost_id<BR> left outer join prod.RS48T pplan on=20
costs.process_id =3D pplan.process_id and costs.planyear =3D pplan.year=20
<BR> and costs.planlevel =3D pplan.level =
and site.fa=20
=3D pplan.site<BR>where process.prodlevel =3D 4</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>which results in the following error =
message from=20
DB2:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>create view macs.PProd11_basis =
as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>DB2 SQL error: SQLCODE: -104, =
SQLSTATE: 42601,=20
SQLERRMC: END-OF-STATEMENT;s.PProd11_basis=20
as<BR>;<query_expr></FONT></DIV></BLOCKQUOTE >
<DIV><FONT face=3DArial size=3D2>Which means:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV><FONT face=3DArial size=3D2>SQL0104N An unexpected token token =
was found=20
following text. Expected tokens may include: token-list. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Explanation: A syntax error in the =
SQL statement=20
was detected at the specified token following the text text. <BR>The =
text=20
field indicates the 20 characters of the SQL statement that preceded =
the token=20
that is not valid. <BR>As an aid to the programmer, a partial list of =
valid=20
tokens is provided in the SQLERRM field of the SQLCA as token-list. =
<BR>This=20
list assumes the statement is correct to that point. The statement =
cannot be=20
processed. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>User Response: Examine and correct =
the statement=20
in the area of the specified token. sqlcode: -104 sqlstate:=20
42601</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The problem is, that <STRONG>the SQL =
statement is=20
syntactically</STRONG> <STRONG>correct</STRONG>. It works with other SQL =
tools=20
(not DTP) without problems. So it is not a problem of the statement or=20
DB2. I suspect that DTP changes the statement in some way. But how =
and=20
why?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another interesting observation: The=20
Select-statement alone (i.e. without "create view ...") works in =
DTP=20
without problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I could identify the problematic part. =
It is the=20
"<FONT face=3D"Courier New">and costs.planlevel =3D pplan.level</FONT>" =
expression.=20
If it is omitted, then the statement works also with DTP.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Apart from that, DTP is very =
problematic if=20
comments are used within an SQL statement. This leads in many cases to =
the same=20
problem.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Any =
solutions?</FONT></DIV></BODY></HTML>
------=_NextPart_000_0018_01C7CFA5.89340630--
|
|
|
Powered by
FUDForum. Page generated in 0.02488 seconds
] 
Search
Help
Register
Login
Home
