| Strange ORA-00904 error, please help [message #165155] |
Tue, 28 March 2006 09:45  |
rsingh43
Messages: 1
Registered: March 2006
|
Junior Member |
|
|
Hi,
I have the below query, when I just run the query, I get the results. But if I try creating a table using this query or try to open a cursor with this query, I get the below error.
ORA-00904 P.LOCATION_CODE invalid identifier
This query has been running fine as part of a procedure for the past few months, no table structure changed, nothing changed, and then one fine day this error.
The error is on the table eth_cdlumt.ilm_tb. If I remove that part from the query, all works fine.
The query is run from the eth_cdlcom schema. eth_cdlcom has a synonym for eth_cdlumt.ilm_tb.
Eth_cdlcom also has all kinds of grants on eth_cdlumt.ilm_tb.
So what is the difference between running this query just as a query or running it as part of a create table statement.
Please help.
RSingh.
Query
SELECT MODELID,ITEM_CODE,COUNT(*)
FROM ETH_CDLCOM.pj P
WHERE MODELID LIKE '%DRP%'
AND EXISTS
(
SELECT 1 FROM ETH_CDLJDE.ilo IL,ETH_CDLJDE.it I WHERE
P.ITEM_CODE=IL.ITEMCODE
AND I.ITEMCODE=IL.ITEMCODE
AND P.LOCATION_CODE=IL.LOCATIONCODE
AND IL.MTSMTO='MTS'
AND IL.STOCKTYPE IN ('A','U')
AND IL.LOCATIONCODE NOT IN('GBUGLX','GBUGLT','GBUGLR')
AND I.MAINSTORAGELOC<>'USEHCS'
)
AND EXISTS
(
SELECT 1 FROM eth_cdljde.it i1, eth_cdlumt.ilm_tb ilm WHERE
i1.itemcode=ilm.itemcode
AND ilm.modelid='PDP-DRP'
AND i1.MAINSTORAGELOC='FRAGED'
AND P.LOCATION_CODE=ilm.APSLOCATIONCODE
)
AND TO_DATE(PERIOD_START_DATE)=(SELECT TO_DATE(modelrundate)+Constraineddrpitems.getMaxTransitLeadTime(P.item_code) FROM eth_cdlumt.modelrundate)
AND PROJECTED_INVENTORY <0
GROUP BY MODELID,ITEM_CODE
HAVING COUNT(*)>1;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
