Link to home
Create AccountLog in
Avatar of Rao_S
Rao_S

asked on

How do I rewrite this query with using CONNECT BY?

Can this query be written in a different way?
The CONNECT BY PRIOR statement has a issue in Informatica.

SELECT corp_plant_cde
  FROM  aqmeq01.tqm_eq_plants p,    
      (SELECT equip_pk
          FROM aqmeq01.tqm_eq_equipment
                  WHERE equip_type_cde = 'PLT'
                  CONNECT BY PRIOR parent_equip_pk = equip_pk
                  start with equip_pk = &equip_pk
          ) e
 WHERE p.equip_pk = e.equip_pk;

Here is a sample data:
"EQUIP_PK"      "PARENT_EQUIP_PK"      "EQUIP_ID"      "EQUIP_TYPE_CDE"
5,033      5,007      WMP      PLT
5,034      5,007      TPS      PLT
5,035      5,007      TTI      PLT
5,007            USST      CPX
5,008      5,007      TXO      PLT
1,014      5,007      LOR      PLT
1,015      5,007      FFT      PLT
5,036      5,007      CHC      PLT
Avatar of Sean Stuber
Sean Stuber

what version of Oracle?  11gR2  allows recursive with-clauses.  But if Informatica won't support your connect by, then it probably won't support those either.


You could put the query in a procedure or function and return a reference cursor.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Rao_S

ASKER

Oracle 10g and a odbc connection. I wonder if anything can be changed in connect by..
I thought I will try a proc, if you have any other solution, let me know.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of Rao_S

ASKER

Discovered that the CONNECT BY PRIOR is not that the problem, but found that when full table names are used in the CONNECT BY PRIOR query, the query works correctly, we just cannot use aliases when referencing tables.
Avatar of Rao_S

ASKER

Here is what we did in Informatica:
SELECT distinct TQM_EQ_EQUIPMENT.EQUIP_PK AS EQUIP_PK
 FROM AQMEQ01.TQM_EQ_EQUIPMENT
 where TQM_EQ_EQUIPMENT.EQUIP_TYPE_CDE = 'PLT'
CONNECT BY PRIOR   TQM_EQ_EQUIPMENT.PARENT_EQUIP_PK = TQM_EQ_EQUIPMENT.EQUIP_PK
START WITH TQM_EQ_EQUIPMENT.EQUIP_PK in $$Equip
------------------------------
Where $$Equip is a parameter which supplies the equip_pk
------------------------------
We are generating the output file, hopefully we should not have any issues.

SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of Rao_S

ASKER

We could not rewrite the query as the query was correct, but instead used the same query and used full table names and did not use alises.