Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
bkraider
Starting Member
2 Posts |
Posted - 2007-11-15 : 17:58:24
|
The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?I am getting the below errorServer: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53Ambiguous column name 'REINS_TYPE_CD'.below is the query i changed. ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION] @RECON_TRNSCTN_ID int=NULL, @RECON_ITEM_ID int=NULL, @DIVISION_ID int=NULL, @REINS_TYPE_CD int=NULL, @TRANSACTION_NO char(10)=NULL, @TRANSACTION_NAME varchar(100)=NULL, @REF_NUMBER varchar(20)=NULL, @POLICY_CRS_REF_NO varchar(20)=NULL, @GL_DTFrom datetime=NULL, @GL_DTTo datetime=NULL, @TRANSACTION_CD int=NULL, @AMOUNT money=NULL, @FILE_STATUS_CD int=NULL, @TRNSCTN_OWR_CD int=NULL, @ISSUE_CD int=NULL, @SUPP_ISSUE_CD int=NULL, @IRC_CLASSIFICATION_CD int=NULL, @UNDER_90_DAYS money=NULL, @OVER_90_DAYS money=NULL, @AGING_DAYS_CNT int=NULL, @VOCHER_NO varchar(50)=NULL, @LOADED_DTFrom datetime=NULL, @LOADED_DTTo datetime=NULL, @SPUsageMode TINYINT = 0 -- This should be the last parameterAS--------------------------------------------------------------------------------- SP Usage Audit Info -- DO NOT REMOVE-- All Stored Procedure code MUST be placed between Section 1 and 2 of-- SP Usage Audit code--------------------------------------------------------------------------------- SP Usaged Section 1 - Declare-------------------------------------------------------------------------------DECLARE @SPStartTime DATETIME SELECT @SPStartTime = GETDATE()DECLARE @SPEndTime DATETIMEDECLARE @AuditCount INT-------------------------------------------------------------------------------SELECT @GL_DTFROM = ISNULL(@GL_DTFROM, CONVERT(DATETIME,'1/1/1900'))SELECT @GL_DTTO = ISNULL(@GL_DTTO, CONVERT(DATETIME,'12/31/9999'))SELECT @LOADED_DTFROM = ISNULL(@LOADED_DTFROM, CONVERT(DATETIME,'1/1/1900'))SELECT @LOADED_DTTO = ISNULL(@LOADED_DTTO, CONVERT(DATETIME,'12/31/9999'))Begin SELECT RECON_TRNSCTN_ID, RECON_ITEM_ID, DIVISION_ID, REINS_TYPE_CD, TRANSACTION_NO, TRANSACTION_NAME, REF_NUMBER, POLICY_CRS_REF_NO, GL_DT, TRANSACTION_CD, AMOUNT, FILE_STATUS_CD, TRNSCTN_OWR_CD, ISSUE_CD, SUPP_ISSUE_CD, IRC_CLASSIFICATION_CD, UNDER_90_DAYS, OVER_90_DAYS, AGING_DAYS_CNT, VOCHER_NO, LOADED_DT, REI.REINS_TYPE_DS FROM AIGNET.dbo.RECON_TRANSACTION AS RE INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD WHERE (@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID) AND (@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID) AND (@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID) AND (@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD) AND (@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO) AND (@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME) AND (@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER) AND (@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO) AND ((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo)) AND (@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD) AND (@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT) AND (@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD) AND (@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD) AND (@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD) AND (@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD) AND (@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD) AND (@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS) AND (@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS) AND (@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT) AND (@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO) AND ((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo)) End--------------------------------------------------------------------------------- SP Usage Audit Info -- DO NOT REMOVE-- SP Usage Section 2 - INSERT Audit Info-------------------------------------------------------------------------------SELECT @AuditCount = @SPUsageMode + (SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK) WHERE Parameter = 'SPUsageByPass')IF @AuditCount < 1 Begin SELECT @SPEndTime = GETDATE() INSERT DBPerfMon.dbo.SPUsage ( DatabaseName , Duration , ObjectID , ObjectName , UserName ) SELECT DB_NAME() , DATEDIFF(ms, @SPStartTime, @SPEndTime) , OBJECT_ID(OBJECT_NAME(@@PROCID)) , OBJECT_NAME(@@PROCID) , dbo.fncGetLastUpdatedBy () End------------------------------------------------------------------------------- -- Absolutely NO Stored Procedure code written beyond this point------------------------------------------------------------------------------- |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 18:30:49
|
The problem is right here:BeginSELECTRECON_TRNSCTN_ID,RECON_ITEM_ID,DIVISION_ID,REINS_TYPE_CD, Notice how you didn't specify which table to grab the column from. Add the alias to it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
bkraider
Starting Member
2 Posts |
Posted - 2007-11-16 : 08:44:12
|
Thank you it worked for me |
|
|
|
|
|
|
|