3 Replies Latest reply on Aug 12, 2016 5:57 AM by Anthony Griffiths

    Custom SQL Query - Column ambiguously defined

    Anthony Griffiths

      Hi all,

       

      I'm new to using SQL and seem to be struggling a lot with this error message. Can't see where I'm going wrong!

       

      I'm connecting to a Oracle DB and as soon as I try to left join some fields from another table I'm getting this error message.

       

      Any help would be greatly appreciated on what I'm doing wrong!

       

      Select

      Createdwhen as CreatedDate,

      IMDARDCWWDWSPRISKS.Needid as NeedID,

      Needreference as NeedRef,

      Latestversion as LatestVersion,

      Workflowstatename as WorkflowState,

      Investmentareaname as InvestmentArea,

      Hazardreferencecode as HazardReference,

      Dwicategorycode as DWICategory,

      Legalinstrumentref as LegalInstrumentRef,

      Dwistagename as DWIStage,

      IMDARNEEDSCORES.Scorevalue as ResidualRisk,

      IMDARNEEDORGANISATIONS.Organisationunit4Id as AssetPlanningAreaID,

      IMDARORGANISATIONUNITS.Description as AssetPlanningArea,

      IMDARLOCATIONTAGS.Value as AssetLocationCode,

      IMDARLOCATIONS.Description as Asset_Location    <---------------------- When I add this field and below left join statement, I get the error msg!

       

      From

       

      IMDARDCWWDWSPRISKS

       

      Left Join IMDARLOCATIONS On IMDARDCWWDWSPRISKS.Assetlocationid = IMDARLOCATIONS.Assetlocationid  <---------------------------

      Left Join IMDARLOCATIONTAGS On IMDARDCWWDWSPRISKS.Assetlocationid = IMDARLOCATIONTAGS.Assetlocationid And IMDARLOCATIONTAGS.Tagname = 'DWSP Asset Reference'

      Left Join IMDARNEEDORGANISATIONS On IMDARDCWWDWSPRISKS.NEEDID = IMDARNEEDORGANISATIONS.NEEDID

      Left Join IMDARORGANISATIONUNITS On IMDARNEEDORGANISATIONS.Organisationunit4Id = IMDARORGANISATIONUNITS.Organisationunitid

       

      Left Join IMDARNEEDSCORES On IMDARDCWWDWSPRISKS.Needid = IMDARNEEDSCORES.Needid  And IMDARNEEDSCORES.Scorecomponentid = 101815 And IMDARNEEDSCORES.Year = extract(year from sysdate)