Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL query to be translated from DB2 to Oracle

Posted on 2013-01-22
12
Medium Priority
?
874 Views
Last Modified: 2013-01-24
Hi Experts,

I have the below sql query, written to run against a DB2 database.
Question: is it possible (how hard is it) to have this translated into code so that it can run against an Oracle (10g) database?

--
-- Print a list of all the jobs related to the selected one
-- in this case 'MSTR#JS1.PLAN'
--
CONNECT TO DB;
-- If you're running this several times in one session you may need to
-- DROP TABLE session.heirarchy;
--
DECLARE GLOBAL TEMPORARY TABLE SESSION.hierarchy
   ( pkey  VARCHAR(74) NOT NULL,
     ckey  VARCHAR(74))
ON COMMIT PRESERVE ROWS;
COMMIT;
INSERT INTO SESSION.hierarchy
SELECT JS_WRKST_NAME || '#' || JS_NAME || '.' || JB_NAME, 
       DEP_JS_WRKST_NAME || '#' ||  DEP_JS_NAME || '.' ||  DEP_JB_NAME
       FROM JB_DPS_V;
-- You could simply use the temporary table 
-- to print a list of job dependencies
-- with a simple
-- SELECT * FROM SESSION.hierarchy;
-- But now we can also see, for a particular job
-- what relationships it has to any other job
-- in the database
WITH children (kkey, lv1) AS
  ( SELECT ckey, 1
    FROM   SESSION.hierarchy
    WHERE  pkey = 'MSTR#JS1.PLAN'
    UNION  ALL
    SELECT H.ckey, C.lv1 + 1
    FROM   SESSION.hierarchy H,
           children C
    WHERE  H.pkey = C.kkey ),
parents (kkey, lv1) AS
  ( SELECT pkey, -1
    FROM   SESSION.hierarchy
    WHERE  ckey = 'MSTR#JS1.PLAN'
    UNION  ALL
    SELECT H.pkey, P.lv1 - 1
    FROM   SESSION.hierarchy H,
           parents P
    WHERE  H.ckey = P.kkey )
SELECT  kkey, lv1
FROM    children
UNION   ALL
SELECT  kkey, lv1
FROM    parents;

Open in new window



The first error is this one...

Lookup Error
ORA-06550: line 1, column 26: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; not null range default character The symbol ":= was inserted before "TABLE" to continue. ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "VARCHAR" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKE ORA-06550: line 2, column 28
0
Comment
Question by:Watnog
12 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38804566
try this...

CREATE and not "DECLARE"  GLOBAL TEMPORARY TABLE SESSION.hierarchy - so it should be CREATE GLOBAL TEMPORARY TABLE SESSION.hierarchy
0
 

Author Comment

by:Watnog
ID: 38804606
Thanks.
An error was returned:  invalid table name.
I changed the table name to DUMMY, and that worked.

Next error is this one...

Category      Timestamp      Duration      Message      Line      Position
Error      22/01/2013 11:06:12      0:00:00.187      <link> - Oracle Database Error: ORA-32033: unsupported column aliasing      24      14

That refers back to:
WITH children (kkey, lv1) AS

Open in new window

0
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 1600 total points
ID: 38804958
This is working now: Your problem was that you are not giving an alias name for your columns, so the columns with static value 1 or -1 does not have the same name as the columns with c.lv1 + 1 or c.lv1 - 1

--
-- Print a list of all the jobs related to the selected one
-- in this case 'MSTR#JS1.PLAN'
--
CONNECT TO DB;
-- If you're running this several times in one session you may need to
-- DROP TABLE session.heirarchy;
--
CREATE GLOBAL TEMPORARY TABLE SESSION.dummy
   ( pkey  VARCHAR(74) NOT NULL,
     ckey  VARCHAR(74))
ON COMMIT PRESERVE ROWS;
COMMIT;
INSERT INTO SESSION.dummy
SELECT JS_WRKST_NAME || '#' || JS_NAME || '.' || JB_NAME, 
       DEP_JS_WRKST_NAME || '#' ||  DEP_JS_NAME || '.' ||  DEP_JB_NAME
       FROM JB_DPS_V;
-- You could simply use the temporary table 
-- to print a list of job dependencies
-- with a simple
-- SELECT * FROM SESSION.dummy;
-- But now we can also see, for a particular job
-- what relationships it has to any other job
-- in the database
WITH children (kkey, lv1) AS
  ( SELECT ckey, 1 as lv1
    FROM   SESSION.dummy
    WHERE  pkey = 'MSTR#JS1.PLAN'
    UNION  ALL
    SELECT H.ckey, C.lv1 + 1 as lv1
    FROM   SESSION.dummy H,
           children C
    WHERE  H.pkey = C.kkey ),
parents (kkey, lv1) AS
  ( SELECT pkey, -1 as lv1
    FROM   SESSION.dummy
    WHERE  ckey = 'MSTR#JS1.PLAN'
    UNION  ALL
    SELECT H.pkey, P.lv1 - 1 as lv1
    FROM   SESSION.dummy H,
           parents P
    WHERE  H.ckey = P.kkey )
SELECT  kkey, lv1
FROM    children
UNION   ALL
SELECT  kkey, lv1
FROM    parents;

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Watnog
ID: 38805023
We're getting closer, thanks again, but it still doesn't like WITH statements...
Error:22/01/2013 11:58:34 0:00:00.171: Lookup Error - Oracle Database Error: ORA-32033: unsupported column aliasing
[url="http://ora-32033.ora-code.com/"]28:14[/url] WITH children (kkey, lv1) AS
  ( SELECT ckey, 1 as lv1
    FROM   DUMMY
    WHERE  pkey = 'TWSDVLP#FINAL.SWITCHPLAN'
    UNION  ALL
    SELECT H.ckey, C.lv1 + 1 as lv1
    FROM   DUMMY H,
           children C
    WHERE  H.pkey = C.kkey ),
parents (kkey, lv1) AS
  ( SELECT pkey, -1 as lv1
    FROM   DUMMY
    WHERE  ckey = 'TWSDVLP#FINAL.SWITCHPLAN'
    UNION  ALL
    SELECT H.pkey, P.lv1 - 1 as lv1
    FROM   DUMMY H,
           parents P
    WHERE  H.ckey = P.kkey )
SELECT  kkey, lv1
FROM    children
UNION   ALL
SELECT  kkey, lv1
FROM    parents

Open in new window

To be honest, it didn't like the 'SESSION.dummy' very much either.
Error:22/01/2013 12:08:02 0:00:00.000: Lookup Error - Oracle Database Error: ORA-00903: invalid table name
8:30: CREATE GLOBAL TEMPORARY TABLE SESSION.dummy
   ( pkey  VARCHAR(74) NOT NULL,
     ckey  VARCHAR(74))
ON COMMIT PRESERVE ROWS

Open in new window

I changed that to plain DUMMY, and included a 'truncate' and 'drop' before the 'create'.
Hope that is not at root of our alias problem. I'm an  sorcerer's apprentice in this.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 38805781
Unless your database schema is called SESSION, this will give you an error.
I have removed that from the script and it has worked great. So, maybe this is the error.
0
 

Author Comment

by:Watnog
ID: 38806130
This is where it is now...

truncate table DUMMY;
drop table DUMMY;
CREATE GLOBAL TEMPORARY TABLE DUMMY
   ( pkey  VARCHAR(74) NOT NULL,
     ckey  VARCHAR(74))
ON COMMIT PRESERVE ROWS;
COMMIT;
INSERT INTO DUMMY
SELECT  [blablabla]
WITH children AS
  ( SELECT ckey kkey, 1 as lv1
    FROM   DUMMY
    WHERE  pkey = 'blabla'
    UNION  ALL
    SELECT H.ckey, C.lv1 + 1 as lv1
    FROM   DUMMY H,
           children C
    WHERE  H.pkey = C.kkey ),
parents AS
  ( SELECT pkey kkey, -1 as lv1
    FROM   DUMMY
    WHERE  ckey = 'blabla'
    UNION  ALL
    SELECT H.pkey, P.lv1 - 1 as lv1
    FROM   DUMMY H,
           parents P
    WHERE  H.ckey = P.kkey )
SELECT  kkey, lv1
FROM    children
UNION   ALL
SELECT  kkey, lv1
FROM    parents;

Open in new window


The error now is this one:

Oracle Database Error: ORA-32031: illegal reference of a query name in WITH clause  

The cursor is on 'children C' so that maybe the culprit.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 38807462
well, you probably should replace your [bla bla bla] by a real query and hopefully it will have a ; at the end, so it will be a separate command from the query starting below...
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 38807467
Ah, also you are calling the query inside itself...  that could be an issue...
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 38807474
Nah, forget it... that should work.
0
 

Author Comment

by:Watnog
ID: 38809628
It's a recursive query as you say.
Below query throws the
"ORA-32031: illegal reference of a query name in WITH clause".
When googling that, it appears that possibly  this kind WITH does not work in 10G (but would work in 11G).

I'm ready to abandon this, I'm more than willing than granting you the points, but help with grading: would this be an 'A' (for effort)?
Thanks!

--
-- Print a list of all the jobs related to the selected one
-- in this case 'MASTER#FIN.PLAN'
--
-- CONNECT TO DB;
-- If you're running this several times in one session you may need to
-- DROP TABLE session.heirarchy;
--
truncate table DUMMY;
drop table DUMMY;
CREATE GLOBAL TEMPORARY TABLE DUMMY
   ( pkey  VARCHAR(74) NOT NULL,
     ckey  VARCHAR(74))
ON COMMIT PRESERVE ROWS;
COMMIT;
INSERT INTO DUMMY
SELECT WKS_NAME || '#' || STREAM_NAME || '.' || JOB_NAME, 
       STREAM_WKS_NAME || '#' ||  JOB_STREAM_NAME || '.' ||  JOB_NAME
       FROM JB_DPS_V;
-- You could simply use the temporary table 
-- to print a list of job dependencies
-- with a simple
-- SELECT * FROM DUMMY;
-- But now we can also see, for a particular job
-- what relationships it has to any other job
-- in the database
WITH children AS
  ( SELECT ckey, 1
    FROM   DUMMY
    WHERE  pkey = 'MASTER#FIN.PLAN'
    UNION  ALL
    SELECT H.ckey, C.lv1 + 1
    FROM   DUMMY H, 
           children C
    WHERE  H.pkey = C.kkey ),
parents AS
  ( SELECT pkey, -1
    FROM   DUMMY
    WHERE  ckey = 'MASTER#FIN.PLAN'
    UNION  ALL
    SELECT H.pkey, P.lv1 - 1
    FROM   DUMMY H, 
           parents P
    WHERE  H.ckey = P.kkey )
SELECT  kkey, lv1
FROM    children
UNION   ALL
SELECT  kkey, lv1
FROM    parents;

Open in new window

0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 400 total points
ID: 38810391
You should not need to drop and create the temporary table every run.  Temporary tables are meant to be created once and reused.  You may need to do the truncate if you run multiple times in one session, but the create should only be done once.

As for the WITH statement, can't you just move that into the query?  Like this:

SELECT kkey, 
       lv1 
FROM   (SELECT ckey, 
               1 
        FROM   dummy 
        WHERE  pkey = 'MASTER#FIN.PLAN' 
        UNION ALL 
        SELECT H.ckey, 
               C.lv1 + 1 
        FROM   dummy H, 
               children C 
        WHERE  H.pkey = C.kkey) children 
UNION ALL 
SELECT kkey, 
       lv1 
FROM   (SELECT pkey, 
               -1 
        FROM   dummy 
        WHERE  ckey = 'MASTER#FIN.PLAN' 
        UNION ALL 
        SELECT H.pkey, 
               P.lv1 - 1 
        FROM   dummy H, 
               parents P 
        WHERE  H.ckey = P.kkey) parents;

Open in new window


Although I don't see where you are getting the KKEY and LV1 columns from, they aren't referenced in the WITH clause.
0
 

Author Comment

by:Watnog
ID: 38813564
Hi both, I learned that recursion in a WITH clause is not allowed in 10g, though it is in 11g. An option would be to use CONNECT BY clause.
I will not pursue this. Thanks for your efforts.
Cheers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question