Solved

column ambiguously define error in stored procedure(query works fine though)

Posted on 2007-03-30
1
537 Views
Last Modified: 2013-12-19
Folks

I am trying to set up a stored procedure in oracle


CREATE PROCEDURE InsertserviceMTBF
AS
BEGIN

INSERT INTO TICKET(CLASS,TICKETID,DESCRIPTION,orgid,siteid,ASSETNUM, MTBFINTERVAL,LOCATION,REPORTDATE,CHANGEDATE,
STATUS,STATUSDATE,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,
ACTLABCOST,TICKETUID,
CREATEWORELASSET,LANGCODE,HASLD,
 EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY)
(
SELECT CLASS,(ROWNUM + SEED)seed,description,orgid,siteid,assetnum, avg_delay,location,SYSDATE,SYSDATE,status,SYSDATE,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,

ACTLABCOST,TICKETSEQ.NEXTVAL,CREATEWORELASSET,
LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY
 FROM
(
SELECT CLASS,(SELECT (SEED+1) FROM AUTOKEY WHERE AUTOKEYNAME ='SRID') SEED
,description,orgid,siteid,assetnum ,mtbfwarn,AVG(actfinish) avg_delay,location,SYSDATE,SYSDATE,status,SYSDATE,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,
ACTLABCOST,CREATEWORELASSET,
LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY
FROM
(
SELECT 'SR'AS CLASS,
'MTBF is higher than norm,please inspect asset'AS description,WORKORDER.orgid,
WORKORDER.siteid,WORKORDER.wonum,WORKORDER.assetnum,ASSET.mtbfwarn, lead(WORKORDER.actfinish)
 over(PARTITION BY WORKORDER.assetnum ORDER BY WORKORDER.assetnum,
 WORKORDER.actfinish) - WORKORDER.actfinish actfinish
 ,WORKORDER.location,SYSDATE, SYSDATE ,'NEW'AS status,SYSDATE,'0'AS ISGLOBAL,'0'AS RELATEDTOGLOBAL,
'0'AS SITEVISIT,'0'AS INHERITSTATUS,'0'AS HISTORYFLAG,'0'AS TEMPLATE,'0'AS HASACTIVITY,'0'AS ISKNOWNERROR,
 '0'AS ACTLABHRS,
 '0'AS ACTLABCOST,'0'AS CREATEWORELASSET,'EN'AS LANGCODE,'0'AS HASLD,'0'AS EMAIL_SENT ,'0'AS
 TICKET_COMP,'MAXADMIN' AS REPORTEDBY,'MAXADMIN'AS CHANGEBY
FROM WORKORDER,ASSET WHERE ASSET.assetnum=WORKORDER.assetnum AND WORKORDER.WORKTYPE IN('CMEM','CMCM')
AND WORKORDER.ISTASK='0'
AND WORKORDER.INSERTED='0'
AND WORKORDER.ORIGRECORDCLASS IS NULL
)
GROUP BY assetnum,mtbfwarn,description,orgid,siteid,location,STATUS,ISGLOBAL,RELATEDTOGLOBAL,
ACTLABHRS,ACTLABCOST,CREATEWORELASSET,LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,SITEVISIT,
INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,CLASS
)
WHERE avg_delay < mtbfwarn
)
;

the issue I am faced with,if I just run the query it works fine,however when i run it as an SP then i get a column is ambiguoulsy defined error,the error is poiting towards my sysdate fields,could anyone adjust or advise so that I can get the sp to work

all help will do
0
Comment
Question by:rutgermons
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18823393

INSERT INTO TICKET(CLASS,TICKETID,DESCRIPTION,orgid,siteid,ASSETNUM, MTBFINTERVAL,LOCATION,REPORTDATE,CHANGEDATE,
STATUS,STATUSDATE,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,
ACTLABCOST,TICKETUID,
CREATEWORELASSET,LANGCODE,HASLD,
 EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY)
(
SELECT CLASS,(ROWNUM + SEED)seed,description,orgid,siteid,assetnum, avg_delay,location,SYSDATE,SYSDATE,status,SYSDATE,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,

ACTLABCOST,TICKETSEQ.NEXTVAL,CREATEWORELASSET,
LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY
 FROM
(
SELECT CLASS,(SELECT (SEED+1) FROM AUTOKEY WHERE AUTOKEYNAME ='SRID') SEED
,description,orgid,siteid,assetnum ,mtbfwarn,AVG(actfinish) avg_delay,location,status,
ISGLOBAL,RELATEDTOGLOBAL,SITEVISIT,INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,ACTLABHRS,
ACTLABCOST,CREATEWORELASSET,
LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,CHANGEBY
FROM
(
SELECT 'SR'AS CLASS,
'MTBF is higher than norm,please inspect asset'AS description,WORKORDER.orgid,
WORKORDER.siteid,WORKORDER.wonum,WORKORDER.assetnum,ASSET.mtbfwarn, lead(WORKORDER.actfinish)
 over(PARTITION BY WORKORDER.assetnum ORDER BY WORKORDER.assetnum,
 WORKORDER.actfinish) - WORKORDER.actfinish actfinish
 ,WORKORDER.location,'NEW'AS status, '0'AS ISGLOBAL,'0'AS RELATEDTOGLOBAL,
'0'AS SITEVISIT,'0'AS INHERITSTATUS,'0'AS HISTORYFLAG,'0'AS TEMPLATE,'0'AS HASACTIVITY,'0'AS ISKNOWNERROR,
 '0'AS ACTLABHRS,
 '0'AS ACTLABCOST,'0'AS CREATEWORELASSET,'EN'AS LANGCODE,'0'AS HASLD,'0'AS EMAIL_SENT ,'0'AS
 TICKET_COMP,'MAXADMIN' AS REPORTEDBY,'MAXADMIN'AS CHANGEBY
FROM WORKORDER,ASSET WHERE ASSET.assetnum=WORKORDER.assetnum AND WORKORDER.WORKTYPE IN('CMEM','CMCM')
AND WORKORDER.ISTASK='0'
AND WORKORDER.INSERTED='0'
AND WORKORDER.ORIGRECORDCLASS IS NULL
)
GROUP BY assetnum,mtbfwarn,description,orgid,siteid,location,STATUS,ISGLOBAL,RELATEDTOGLOBAL,
ACTLABHRS,ACTLABCOST,CREATEWORELASSET,LANGCODE,HASLD, EMAIL_SENT ,TICKET_COMP,REPORTEDBY,SITEVISIT,
INHERITSTATUS,HISTORYFLAG,TEMPLATE,HASACTIVITY,ISKNOWNERROR,CLASS
)
WHERE avg_delay < mtbfwarn
)
;



unless you give the SYSDATE in the inner query an alias name, and use that alias later in the outer query, you don't need to specify the SYSDATE (which is a FUNCTION, not a column) in the inner query.
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

Title # Comments Views Activity
query in Oracle forms Builder 2 41
ORA-01008: not all variables bound. 6 38
selective queries 7 22
1 FROM DUAL wont work with additional columns ?? 4 26
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

895 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

18 Experts available now in Live!

Get 1:1 Help Now