| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
08-09-11, 05:43
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
|
Convert recursive query Oracle to DB2/UDB
|
|
Hello guys I need help for convert a SQL Query Oracle for DB2/UDB database.
My query is recursive and I have some difficulties to make that.
This is the Oracle query :
Code:
WITH matable AS (
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql FROM dual UNION ALL
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql FROM dual UNION ALL
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql FROM dual
),
sr1 AS (
SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable
),
sr2 AS (
SELECT level, table1, table2, sys_connect_by_path(sql, ' AND ') AS chemin FROM sr1 WHERE table2 = 'SVFAV'
START WITH table1 = 'SGART' CONNECT BY NOCYCLE table1 = PRIOR table2 ORDER BY level ASC
)
SELECT chemin FROM sr2 WHERE rownum = 1
and this is my DB2 query for the moment :
Code:
WITH matable AS (
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql FROM dual UNION ALL
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql FROM dual UNION ALL
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql FROM dual
),
sr1 AS ( SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable ),
sr2(TA, TB, STEP, SQL, MOI) AS
(
SELECT sr1.table1, sr1.table2, 0, sr1.sql, '' FROM sr1 WHERE sr1.table1 = 'SGART'
UNION ALL
SELECT sr1.table1, sr1.table2, sr2.STEP + 1, sr1.sql, sr2.sql || 'AND ' || sr1.sql FROM sr1, sr2 WHERE sr1.table1 = sr2.TB
)
SELECT * FROM sr2 WHERE sr2.TB = 'SVFAV'
If someone have an idea, that will be great
Thanks
|
Last edited by ghosty177; 08-09-11 at 08:55.
|
08-09-11, 05:47
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Do you get any error message ?
or are you not getting the same data as Oracle ?
Can you post table definitions also ?
If you don't provide enough information, no one can help
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
08-09-11, 08:18
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
|
|
No I don't have any error message
The query works but doesnt return the good results, I thinks it's boucling to infinity.
Code:
WITH matable AS (
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql FROM dual UNION ALL
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql FROM dual UNION ALL
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql FROM dual UNION ALL
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql FROM dual UNION ALL
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql FROM dual
),
sr1 AS ( SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable ),
sr2(TA, TB, STEP, SQL, MOI) AS
(
SELECT sr1.table1, sr1.table2, 0, sr1.sql, '' FROM sr1 WHERE sr1.table1 = 'SGART'
UNION ALL
SELECT sr1.table1, sr1.table2, sr2.STEP + 1, sr1.sql, sr2.sql || 'AND ' || sr1.sql FROM sr1, sr2 WHERE sr1.table1 = sr2.TB
)
SELECT * FROM sr2 WHERE sr2.TB = 'SVFAV'
In fact I have to define the way beetween two tables. The upper part in the query ("WITH matable") generate a temporary table with my usefull data.
If you analyse the relations beetween the data, you 'll see their is a unique shortest path beetween two tables. And I try to found this with the recursive query. This query works fine on oracle DB like this and I can't obtein the same result on DB2.
|
|
08-09-11, 08:28
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
What is the result you want for the data you are using here ?
---
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
08-09-11, 08:46
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
The result is :
Code:
sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv
it's the concatenation of all sql fields of the path. For this example it's the path between 'SGART' and 'SVFAV'
For the path beetween 'SGART' and 'CDLCV'. the result will be :
Code:
sgart.numsgart = svlcv.artsvlcv
|
|
08-09-11, 09:34
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I thought that you need to simulate "NOCYCLE" of Oracle.
Here is an example.
I added a column "tables" and the following condition to simulate "NOCYCLE".
Code:
AND LOCATE(sr1.table1 , sr2.tables) = 0
The following condition was added to prevent a warning message
SQL0347W The recursive common table expression "DB2ADMIN.SR2" may contain an infinite loop. SQLSTATE=01605
Code:
------------------------------ Commands Entered ------------------------------
WITH
matable(table1 , table2 , sql) AS (
SELECT 'GTETS' , 'SVCDV' , 'gtets.numgtets = svcdv.etssvcdv'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SGART' , 'SVLCV' , 'sgart.numsgart = svlcv.artsvlcv'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SVCDV' , 'SKDRE' , 'svcdv.ecvsvcdv <= 158'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SVFAV' , 'SVCDV' , 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SVCDV' , 'SVCLI' , 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SVCVE' , 'SVCDV' , 'svcve.nuisvcve = svcdv.nuisvcdv'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SVLCV' , 'SVCDV' , 'svlcv.nuisvlcv = svcdv.nuisvcdv'
FROM sysibm.sysdummy1 UNION ALL
SELECT 'SGART' , 'SGAAE' , 'sgart.numsgart = sgaae.artsgaae'
FROM sysibm.sysdummy1
)
, sr1 AS (
SELECT table1 , table2 , sql FROM matable
UNION ALL
SELECT table2 , table1 , sql FROM matable
)
, sr2(level , tables, table2 , chemin) AS (
SELECT 0
, CAST(table1 AS VARCHAR(40) )
, table2
, CAST(sql AS VARCHAR(400) )
FROM sr1
WHERE table1 = 'SGART'
UNION ALL
SELECT level + 1
, sr2.tables || ' , ' || sr1.table1
, sr1.table2
, sr2.chemin || ' AND ' || sr1.sql
FROM sr1
, sr2
WHERE sr1.table1 = sr2.table2
AND LOCATE(sr1.table1 , sr2.tables) = 0
AND level < 10
)
SELECT chemin
FROM sr2
WHERE table2 = 'SVFAV'
ORDER BY
level
;
------------------------------------------------------------------------------
CHEMIN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv
1 record(s) selected.
|
|
08-09-11, 10:40
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
Wow great it's looks good. I'll test that, but a big thank you to you.
About the limit of 10, if I understand well, it's just for avoid the database loop to the limit of allowed loop, thats right?
And do you know how much loop the database allowed in standard configuration?
And I have an other question. For result, the request give me :
Code:
sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv
As you can see, it put me a lot of spaces beetween the results, did you know why? Because in you extract their is no such spaces?
|
Last edited by ghosty177; 08-09-11 at 10:49.
|
08-09-11, 19:07
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
..., it put me a lot of spaces beetween the results, did you know why?
|
I don't know why.
But, columns chemin and/or sql must be interpreted as fixed length string at the time of concatenation.
How about to use RTRIM() functions, like...
CAST(RTRIM(sql) AS VARCHAR(400) )
and
RTRIM(sr2.chemin) || ' AND ' || RTRIM(sr1.sql)
|
|
08-10-11, 04:13
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
Hy,
I add the RTRIM to your query and it works fine, no more spaces.
But I have an other problem when I adapt this in my program, the query return an error to me :
Code:
Error: DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC=GTETS , SVCDV , SVCLI , DRIVER=3.59.81
So I put the VARCHAR limit to 150 and 2048, is it right?
And, is it possible to format the sql to only keep the first path between to tables (first row of result)?
Because after run some test, I see only the first result is interesting for me, so the database don't need to search after.
|
|
08-10-11, 04:40
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
I put the VARCHAR limit to 150 and 2048, is it right?
|
Yes.
The datatype of table1 and table2 must be CHAR(16).
So, length of concatenated string should be at least
18 * (number of tables between <from table> and <to table>).
Quote:
|
is it possible to format the sql to only keep the first path between to tables (first row of result)?
|
Add "FETCH FIRST 1 ROW ONLY" clause at the last of the query.
|
|
08-10-11, 05:51
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
Ok for the lenght of the string.
About the "FETCH FIRST 1 ROW ONLY", it's works but it isn't solve my problem.
I test to change the number put in "level < 10", and that impact the time in a big way :
for an exemple with the level result is 1, if I put level at
level = 5 ==> 0,016s
level = 7 ==> 0,234s
level = 9 ==> 4,359s
level = 10 ==> 17,609s
Is it possible to break the query when the the DB found the first result?
Like this I can put the level limit high, and the DB will use what they need.
|
|
08-10-11, 08:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
How many pathes are there between from_table and to_table?
If you stop searching next tables by a condition "LOCATE(sr1.table1 , sr2.tables) = 0",
then recursion may not take so much times(except the case having millions of pathes between from_table and to_table),
even if levels get high(query time may get longer proportionally to levels).
I guessed that you might join next tables by removing join conditions(or loosened the conditions) on your test.
So, it joined many tables to existing pathes on each level
and number of intermidiate result rows grow by multiplication.
|
|
08-10-11, 10:58
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
Their is only one (shortest) way beetween two table.
And the maximun level beetween two table is around 10 and 20(big max) interval table.
|
|
08-10-11, 13:39
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
Their is only one (shortest) way beetween two table.
|
I asked number of all the ways(including shortest and longest) beetween two table.
Anyhow,
how about the result and the execution time of this query?
Code:
WITH
sr1(table1 , table2 , sql) AS (
SELECT RTRIM(table1) , RTRIM(table2) , sql FROM matable
UNION ALL
SELECT RTRIM(table2) , RTRIM(table1) , sql FROM matable
)
, sr2(level , table_next , tables) AS (
SELECT 1
, table2
, VARCHAR(table1 , 200)
FROM sr1
WHERE table1 = 'SGART'
UNION ALL
SELECT level + 1
, table2
, tables || ',' || table1
FROM sr1
, sr2
WHERE table1 = table_next
AND LOCATE(table1 , tables) = 0
AND level < 10 /*1000000000*/
)
SELECT
level
, COUNT(*) AS count_rows
FROM sr2
GROUP BY
level
ORDER BY
level
;
|
Last edited by tonkuma; 08-10-11 at 13:46.
Reason: Moved RTRIM to sr1 from sr2
|
08-11-11, 09:09
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 9
|
|
Hy,
Their is only one path between two table, their is no circle in the pattern.
However, times of query are shortest.
But one thinks is wired I can't change the result I want to show : the "chemin" of the first query
And I don't see where you put the second table for the end of path.
|
Last edited by ghosty177; 08-11-11 at 09:23.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Convert recursive query Oracle to DB2/UDB
