Solved

SQL query to be translated from DB2 to Oracle

Posted on 2013-01-22
12
817 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
Comment Utility
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
Comment Utility
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 15

Accepted Solution

by:
Walter Ritzel earned 400 total points
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
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 15

Expert Comment

by:Walter Ritzel
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 15

Expert Comment

by:Walter Ritzel
Comment Utility
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 15

Expert Comment

by:Walter Ritzel
Comment Utility
Ah, also you are calling the query inside itself...  that could be an issue...
0
 
LVL 15

Expert Comment

by:Walter Ritzel
Comment Utility
Nah, forget it... that should work.
0
 

Author Comment

by:Watnog
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now