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??

scv1977 wrote:



/dg

scv1977 wrote:



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

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)
....

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.

Actually ,DBO Schema is right..

Tonkuma!!
In place of INNER JOIN,
(FROM dbo."FORMTREE" A
, CTE_Tree CTE) is good point!!.