Solved

SQL query to be translated from DB2 to Oracle

Posted on 2013-01-22
12
827 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 400 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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 34

Assisted Solution

by:johnsone
johnsone earned 100 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

825 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