SQL query to be translated from DB2 to Oracle

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
WatnogAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
try this...

CREATE and not "DECLARE"  GLOBAL TEMPORARY TABLE SESSION.hierarchy - so it should be CREATE GLOBAL TEMPORARY TABLE SESSION.hierarchy
WatnogAuthor Commented:
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

Walter RitzelSenior Software EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

WatnogAuthor Commented:
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.
Walter RitzelSenior Software EngineerCommented:
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.
WatnogAuthor Commented:
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.
Walter RitzelSenior Software EngineerCommented:
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...
Walter RitzelSenior Software EngineerCommented:
Ah, also you are calling the query inside itself...  that could be an issue...
Walter RitzelSenior Software EngineerCommented:
Nah, forget it... that should work.
WatnogAuthor Commented:
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

johnsoneSenior Oracle DBACommented:
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.
WatnogAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.