asked on

Convert DB2 SQL code to Oracle. Getting error invalid identifier

I have a piece of code that needs to be converted from DB2 to Oracle.
I'm trying to return all queues & sub queues that fall under the parent queue. Each sub queue can have a sub queue.
It runs fine on DB2 but getting an error message ORA-00904: invalid identifier

This is a sample portion in DB2

select * FROM OPENQUERY(DB2,'
WITH RPL(LEVEL, PARENT_QUEUE_ID, Queue_ID, NM)
AS
(SELECT 1
, ROOT.PARENT_QUEUE_ID
, ROOT.QUEUE_ID
, ROOT.NM
FROM schema.tbl_queue ROOT
WHERE ROOT.NM IN(''Auto Current'',''Auto PD'',''Auto Repo'' )
UNION ALL
SELECT PARENT.LEVEL + 1
, CHILD.PARENT_QUEUE_ID
, CHILD.QUEUE_ID
, CHILD.NM
FROM RPL PARENT, schema.tbl_queue CHILD
WHERE PARENT.QUEUE_ID = CHILD.PARENT_QUEUE_ID
)
select * from RPL
')

Jim Horn

( - SQL Server zone )

awking00

I'm often troubled when I see requests to "convert" one DBMS query or procedure to another. While many statements can be readily interchanged, there are some that just plain won't. I would rather see sample data and table structures with expected results and an explanation of how those results were derived. That way, you will find Oracle programmers who will resolve the issue in the same way the DB2 programmers did initially.

Kent Olsen

It looks like you're trying to query DB2's internal tables and trying to apply that directly to the Oracle world. That kind of conversion seldom, if ever, works as the internal table structures and names differ between all of the DBMS engines.

What does the original query do?

Kent

jtrifts

OPENQUERY is sqlserver technology, not Oracle.

Are you running in sqlserver, or on Oracle?

Arana (G.P.)

I think CONNECT BY is what you are looking for,
here is an example of recursive SQL in oracle (for Bill of materials)

SELECT 0 AS "LEVEL"
	,'  --BOM ROOT--' "BOM TREE"
	,:parte AS "PARENT"
	,msi.item_type
	,'P*A*R*E*N*T' AS "COD_COMPONENT"
	,msi.description AS "DESC_COMPONENT"
	,1 AS "COMPONENT_QUANTITY"
	,msi.primary_unit_of_measure
FROM mtl_system_items msi
WHERE organization_id = 332
	AND msi.segment1 = :PARTE
--  ''AAA' as  "ARBOL DEL BOM",
-- sys_connect_by_path(msib.segment1, ' @ ') as "ARBOL DEL BOM",

UNION

SELECT DISTINCT LEVEL
	,sys_connect_by_path(msil.segment1, ' @ ') AS "BOM TREE"
	,msi.segment1
	,msil.item_type
	,
	--bom.alternate_bom_designator alterBom,
	lpad(' ', LEVEL, '') || msil.segment1 Cod_Component
	,msil.description Desc_Component
	,BIC.component_quantity
	,msiL.primary_unit_of_measure
FROM mtl_system_items msi
	,bom_bill_of_materials bom
	,BOM_INVENTORY_COMPONENTS BIC
	,MTL_SYSTEM_ITEMS MSIL
WHERE msi.organization_id = 332
	AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
	AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id
	AND bom.bill_sequence_id = bic.bill_sequence_id
	AND nvl(bic.disable_date, sysdate) >= SYSDATE
	AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
	AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
	AND msil.inventory_item_status_code = 'Active'
	AND msi.inventory_item_status_code = 'Active' connect BY prior bic.component_item_id = bom.assembly_item_id
	--START WITH msi.inventory_item_id = 60334
	START
WITH msi.segment1 = :parte
ORDER BY 2

Open in new window

here is what I had to do in DB@ (SAP)

WITH myquery(
       posnr,
       root,
       matnr,
       bom_tree,
       lvl,
--       parent_stlkn,
       stlkn,
       idnrk,
       meins,
       menge
        )
     AS (SELECT p.posnr, 
     m.matnr root,
                m.matnr,
                CAST (  m.matnr|| ' @ ' || p.idnrk as varchar(200))  bom_tree,
                1 ,
--                p.stlkn,
                p.stlkn,
                p.idnrk,
                p.meins,
                p.menge
           FROM sapr3.mast m
                JOIN sapr3.stko k ON k.stlnr = m.stlnr
                JOIN sapr3.stpo p ON p.stlnr = k.stlnr
          WHERE m.matnr = 'AV2014'
       
         UNION ALL
         
         select myquery.posnr,myquery.root,z.matnr,cast(myquery.bom_tree || ' @ ' ||  z.idnrk as varchar(200)) bom_tree ,myquery.lvl+1,z.stlkn,z.idnrk,z.meins,z.menge from
         (
         SELECT p.posnr,
         
                m.matnr,
--                myquery.bom_tree || ' @ ' || p.idnrk bom_tree,
--                 1 lvl,
--                myquery.parent_stlkn,
                p.stlkn,
                p.idnrk,
                p.meins, p.menge
           FROM sapr3.mast m
                JOIN sapr3.stko k ON k.stlnr = m.stlnr
                JOIN sapr3.stpo p ON p.stlnr = k.stlnr) z
                ,
                myquery
          WHERE z.matnr = myquery.idnrk)


select b.*,
--m.PEINH price_unit,
r.meins buom,
--m.stprs base_price,
d.maktx desc,
r.matkl group
--,m.lplpr listprice

, case
   when r.meins=b.uom then m.lplpr/m.peinh --m.stprs
  else
        m.lplpr/m.peinh/1000
      --m.stprs/m.peinh
  end as comp_price
,
case
   when r.meins=b.uom then m.lplpr/m.peinh*b.qpa  --m.stprs*b.qpa
  else
      m.lplpr/m.peinh/1000*b.qpa
      --m.stprs/m.peinh*b.qpa    
  end as qpaprice
  
from  
  (
  SELECT 
  
  
         posnr,
         root,
         myquery.matnr Parent,
         lvl,
         --stlkn,
         idnrk Component,
         meins UOM,
         menge QPA,
         sapr3.marc.herkl country,
         bom_tree

    FROM myquery,sapr3.marc
    where 
         myquery.idnrk=sapr3.marc.matnr
    and sapr3.marc.werks='1850'
--ORDER BY 
--posnr,bom_tree
) b join  sapr3.mbew m on b.component=m.MATNR
    join sapr3.makt d on d.matnr=m.matnr
    join sapr3.mara r on d.matnr=r.matnr

union

SELECT 
              --p.posnr,
              '0000' posnr,
              m.matnr root,
              '---' PARENT,
              0 LVL,
              '' component,
                m.meins uom,
                1 QPA,
                marc.herkl country,
                --  menge,
                
              ' @ '  bom_tree,
              m.meins buom,
              makt.maktx DESC, 
              g.matkl group,
  --              p.stlkn,
              mb.lplpr cost,
              --mb.peinh  perunit,
              mb.lplpr-mb.peinh QPAPRICE
              --stprs unitprice
              --/1000,
                --p.meins,

           FROM sapr3.mara m
           join sapr3.marc on m.matnr=marc.matnr
           join makt on m.matnr=makt.matnr
           join sapr3.mara g on m.matnr=g.matnr
           join sapr3.mbew mb on m.matnr=mb.matnr
                --JOIN sapr3.stko k ON k.stlnr = m.stlnr
                --JOIN sapr3.stpo p ON p.stlnr = k.stlnr
          WHERE m.matnr = 'AV2014' and marc.werks=1850 and mb.bwkey=1850
          ORDER BY 
posnr,bom_tree

Open in new window

I did it the other way around, Oracle=>DB2, i had a really hard time trying that as I didnt have any db2 experience (let alone SAP cryptic column names in german :P).

Gary Patterson, CISSP

"ORA-00904: invalid identifier" usually means Oracle doesn't like a column name you used in the query. But the message usually includes the identifier it didn't like:

ORA-00904: "X"."problem_id": invalid identifier

Did you omit this information, or was it not available due to the interface you used to run the query? Looks Like SQL Server. I've never run Oracle queries through SQL Server OPENQUERY(): maybe it doesn't return as much diagnostic information? You might get more info if you run the query from Oracle SQL *Plus command line. Knowing the problem identifier would be a great start.

DCUnited

ASKER

We have our main tables in Oracle that some of those tables are brought over into DB2. For the most part we are able to convert the sql codes that link to DB2 to Oracle. The sql server code has more to it than what I posted. The other portions were updated to oracle openquery with not issues. We do have some sql codes that only use openquery hitting Oracle. I know sometime the syntax can change between DB2 & Oracle but this one has me stumped.
What I am trying to do is pull in the queues & sub queues based on the master queue.
So I have a parent Queue of Auto PD.
Under that parent queue there are other queues & queue under those queues
See attached as an example. Those are what should be returnedBook1.xlsx

awking00

@DCUnited,
As you can tell by much of the response, we need you to help us help you. Some sample data (in text format and not pictures) and what you want to do with it gives us a better picture of your issue and provides test data for the experts.

BTW, looks like Wayne Rooney is really working out.

DCUnited

ASKER

I posted a sample output previously.
Attached is a sample of output & a sample of the table.
The output is based if the NM of the Parent queue = Auto PD

Book1.xlsx

ASKER CERTIFIED SOLUTION

DCUnited

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial