Solved

SQL query to be translated from DB2 to Oracle

Posted on 2013-01-22
12
823 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 15

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
 

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 15

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

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 15

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

911 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

16 Experts available now in Live!

Get 1:1 Help Now