Prev: Shutdown all instances
Next: DB2 V8.2 on Power7
From: scv1977 on 26 Apr 2010 00:10
Hi
I have the following build problem with SQL executing the following.
(Conversion MSSQL to DB2)
The simplified code does the following(SQL0345N)
DB2 Verson is DBUDB9.7(Fix1)


WITH CTE_Tree(Seq1, Seq2,Type, TypeSeq, ItemSeq,CostType,Sort,
Level)
AS
(

SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
A.CostType, A.Sort, A.Level
FROM dbo."FORMTREE" A
WHERE A.Seq1 = 1
AND A.Seq2 = 1277
AND A.TypeSeq = 2
AND A.ItemSeq = 926
AND A.CostType = 0

UNION ALL

SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
A.CostType, A.Sort, A.Level

FROM dbo."FORMTREE" A
INNER JOIN CTE_Tree CTE
ON A.TypeSeq = CTE.TypeSeq
AND A.ItemSeq = CTE.ItemSeq
AND A.CostType = CTE.CostType
WHERE A.Seq1 = 1
AND A.Seq2 = 1277)
select Seq1,Seq2 from CTE_Tree

--->SQL0345N The fullselect of the recursive common table expression
" CTE_Tree" must be the UNION of two or more fullselects and
cannot include column functions, GROUP BY clause, HAVING clause,
ORDER BY clause, or an explicit join including an ON clause.

Can't I change MSSQL code to DB2??




From: danfan46 on 26 Apr 2010 01:06
scv1977 wrote:
> Hi
> I have the following build problem with SQL executing the following.
> (Conversion MSSQL to DB2)
> The simplified code does the following(SQL0345N)
> DB2 Verson is DBUDB9.7(Fix1)
>
>
> WITH CTE_Tree(Seq1, Seq2,Type, TypeSeq, ItemSeq,CostType,Sort,
> Level)
> AS
> (
>
> SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
> A.CostType, A.Sort, A.Level
> FROM dbo."FORMTREE" A
> WHERE A.Seq1 = 1
> AND A.Seq2 = 1277
> AND A.TypeSeq = 2
> AND A.ItemSeq = 926
> AND A.CostType = 0
>
> UNION ALL
>
> SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
> A.CostType, A.Sort, A.Level
>
> FROM dbo."FORMTREE" A
> INNER JOIN CTE_Tree CTE
> ON A.TypeSeq = CTE.TypeSeq
> AND A.ItemSeq = CTE.ItemSeq
> AND A.CostType = CTE.CostType
> WHERE A.Seq1 = 1
> AND A.Seq2 = 1277)
> select Seq1,Seq2 from CTE_Tree
>
> --->SQL0345N The fullselect of the recursive common table expression
> " CTE_Tree" must be the UNION of two or more fullselects and
> cannot include column functions, GROUP BY clause, HAVING clause,
> ORDER BY clause, or an explicit join including an ON clause.
>
> Can't I change MSSQL code to DB2??
>
>
>
>
Is dbo a valid schema name in your database



/dg
From: danfan46 on 26 Apr 2010 01:11
scv1977 wrote:
> Hi
> I have the following build problem with SQL executing the following.
> (Conversion MSSQL to DB2)
> The simplified code does the following(SQL0345N)
> DB2 Verson is DBUDB9.7(Fix1)
>
>
> WITH CTE_Tree(Seq1, Seq2,Type, TypeSeq, ItemSeq,CostType,Sort,
> Level)
> AS
> (
>
> SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
> A.CostType, A.Sort, A.Level
> FROM dbo."FORMTREE" A
> WHERE A.Seq1 = 1
> AND A.Seq2 = 1277
> AND A.TypeSeq = 2
> AND A.ItemSeq = 926
> AND A.CostType = 0
>
> UNION ALL
>
> SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
> A.CostType, A.Sort, A.Level
>
> FROM dbo."FORMTREE" A
> INNER JOIN CTE_Tree CTE
> ON A.TypeSeq = CTE.TypeSeq
> AND A.ItemSeq = CTE.ItemSeq
> AND A.CostType = CTE.CostType
> WHERE A.Seq1 = 1
....



WITH T1 (Seq1, Seq2,Type, TypeSeq, ItemSeq,CostType,Sort,Level)
AS
(
SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
A.CostType, A.Sort, A.Level
FROM "FORMTREE" A
WHERE A.Seq1 = 1
AND A.Seq2 = 1277
AND A.TypeSeq = 2
AND A.ItemSeq = 926
AND A.CostType = 0
UNION ALL
SELECT A.Seq1, A.Seq2, A.Type, A.TypeSeq, A.itemSeq,
A.CostType, A.Sort, A.Level
FROM "FORMTREE" A
INNER JOIN CTE_Tree CTE
ON A.TypeSeq = CTE.TypeSeq
AND A.ItemSeq = CTE.ItemSeq
AND A.CostType = CTE.CostType
WHERE A.Seq1 = 1
AND A.Seq2 = 1277
)
select Seq1,Seq2
from T1
;
/dg
From: Tonkuma on 26 Apr 2010 02:04
The message text explained the cause of the error.

--->SQL0345N The fullselect of the recursive common table expression
"CTE_Tree" .....
cannot include ..... or an explicit join including an ON clause.

Please try
....
....
FROM dbo."FORMTREE" A
, CTE_Tree CTE
WHERE A.TypeSeq = CTE.TypeSeq
AND A.ItemSeq = CTE.ItemSeq
AND A.CostType = CTE.CostType
AND A.Seq1 = 1
AND A.Seq2 = 1277)
....

From: Tonkuma on 26 Apr 2010 02:12
Another issue I thought was that both of first and second selects in
CTE "CTE_Tree" have actualy same conditions for table dbo."FORMTREE".

So, the CTE is nonsense and may fall into infinite loop.

 |  Next  |  Last
Pages: 1 2
Prev: Shutdown all instances
Next: DB2 V8.2 on Power7