Solved

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

Posted on 2007-03-30
1
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

739 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