• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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

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
rutgermons
Asked:
rutgermons
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now