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
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
I thought I will try a proc, if you have any other solution, let me know.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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_TYP E_CDE = 'PLT'
CONNECT BY PRIOR TQM_EQ_EQUIPMENT.PARENT_EQ UIP_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.
SELECT distinct TQM_EQ_EQUIPMENT.EQUIP_PK AS EQUIP_PK
FROM AQMEQ01.TQM_EQ_EQUIPMENT
where TQM_EQ_EQUIPMENT.EQUIP_TYP
CONNECT BY PRIOR TQM_EQ_EQUIPMENT.PARENT_EQ
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
You could put the query in a procedure or function and return a reference cursor.