Column name ambiguity error? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column name ambiguity error?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-24 : 07:55:52

I have this code for paging through data via a php script:


DECLARE
							@PageSize INT,
							@PageNumber INT,
							@FirstRow INT,
							@LastRow INT
						   
							SELECT	@PageSize = $pageLength,
							@PageNumber = $pageNumber;
						   
							SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
							@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
					
					
							WITH results AS
					
					
						 (  SELECT
						   
						   t1.[Converted_Mortgage_Number],
				                   t1.[Converted_Advance_Number],
						   t1.[Original_Mortgage_Number],
						   CASE
						     WHEN t4.description IS NOT NULL
						       THEN t4.description
						       ELSE t1.CACS_Location
						   END AS CACS_Location,
						   t1.[Batch_Activity_Date],
						   t1.[Activity_Date],
						   t1.[Activity_Time],
						   CASE
						     WHEN t3.description IS NOT NULL
						       THEN t3.description
						       ELSE t1.CACS_State
						   END AS CACS_State,
						   t1.[Letter_ID],
						   t1.[Actvity_Code],
						   t1.[Placed_Code],
						   t1.[Party_Contacted],
						   t1.[Promise_Amt_1],
						   t1.[Promise_Date_1],
						   t1.[Promise_Amount_2],
						   t1.[Promise_Date_2],
						   t1.[Non_Pay_Excuse_Code],
						   t1.[Arrears_Amount],
						   t1.[Payment_Type],
						   t1.[User_ID/Transaction_Type],
						   t1.[Payment_Amount],
						   t2.[Sequence_Number],
						   t2.[History_Text],
						   ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
						   
						    FROM format_cacs_archive_cch AS t1
						   
						    
						    LEFT OUTER JOIN format_cacs_archive_history AS t2
						     ON   t1.Converted_Mortgage_Number = t2.Converted_Mortgage_Number
						     AND t1.Activity_Date = t2.Activity_Date
						     AND t1.Activity_Time = t2.Activity_Time
						    							 
						    LEFT JOIN sd_cacs_states AS t3
						     ON   t1.CACS_Location = t3.location
						     AND t1.CACS_State = t3.state
						    							  
						    LEFT JOIN sd_cacs_Location_details AS t4
						     ON t1.CACS_Location = t4.code  
							
						    WHERE (t1.Converted_Mortgage_Number = '$mortgage' $additional_date_params)
							 
						   
							
							)
							
						   SELECT  
								  
						   [Converted_Mortgage_Number],
		                           [Converted_Advance_Number],
						   [Original_Mortgage_Number],
						   CACS_Location,
						   [Batch_Activity_Date],
						   [Activity_Date],
						   [Activity_Time],
						   CACS_State,
						   [Letter_ID],
						   [Actvity_Code],
						   [Placed_Code],
						   [Party_Contacted],
						   [Promise_Amt_1],
						   [Promise_Date_1],
						   [Promise_Amount_2],
						   [Promise_Date_2],
						   [Non_Pay_Excuse_Code],
						   [Arrears_Amount],
						   [Payment_Type],
						   [User_ID/Transaction_Type],
						   [Payment_Amount],
						   [Sequence_Number],
						   [History_Text]
								  
						   FROM results
						   
						   WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
						   ORDER BY [$criteria] $direction ;

An error 'Invalid column name 't1.Converted_Advance_Number' occurs but, as I've named everything, I can't see where I've gone wrong.
Any pointers appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 08:25:04

Have checked whether you have Converted_Advance_Number column in table format_cacs_archive_cch? Error states that it cant find Converted_Advance_Number column in table.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-24 : 09:42:33

Yes, the column does exist and it contains data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 09:51:59

Can also try giving column names in CTE definition and see if it works

i.e With Results (Converted_Mortgage_Number,Converted_Advance_Number,....) AS
.....

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-24 : 10:03:19

Thanks for your suggestion. I'm a little unsure as to the correct syntax required. Does this modified script look correct?


DECLARE
							@PageSize INT,
							@PageNumber INT,
							@FirstRow INT,
							@LastRow INT
						   
							SELECT	@PageSize = $pageLength,
							@PageNumber = $pageNumber;
						   
							SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
							@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
					
					
							WITH results
							(
							
						   [Converted_Mortgage_Number],
		                   [Converted_Advance_Number],
						   [Original_Mortgage_Number],
						   CACS_Location,
						   [Batch_Activity_Date],
						   [Activity_Date],
						   [Activity_Time],
						   CACS_State,
						   [Letter_ID],
						   [Actvity_Code],
						   [Placed_Code],
						   [Party_Contacted],
						   [Promise_Amt_1],
						   [Promise_Date_1],
						   [Promise_Amount_2],
						   [Promise_Date_2],
						   [Non_Pay_Excuse_Code],
						   [Arrears_Amount],
						   [Payment_Type],
						   [User_ID/Transaction_Type],
						   [Payment_Amount],
						   [Sequence_Number],
						   [History_Text],
						    ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber)		  
							
							)
							 AS
					
					
						 (  SELECT
						   
						   t1.[Converted_Mortgage_Number],
				           t1.[Converted_Advance_Number],
						   t1.[Original_Mortgage_Number],
						   CASE
						     WHEN t4.description IS NOT NULL
						       THEN t4.description
						       ELSE t1.CACS_Location
						   END AS CACS_Location,
						   t1.[Batch_Activity_Date],
						   t1.[Activity_Date],
						   t1.[Activity_Time],
						   CASE
						     WHEN t3.description IS NOT NULL
						       THEN t3.description
						       ELSE t1.CACS_State
						   END AS CACS_State,
						   t1.[Letter_ID],
						   t1.[Actvity_Code],
						   t1.[Placed_Code],
						   t1.[Party_Contacted],
						   t1.[Promise_Amt_1],
						   t1.[Promise_Date_1],
						   t1.[Promise_Amount_2],
						   t1.[Promise_Date_2],
						   t1.[Non_Pay_Excuse_Code],
						   t1.[Arrears_Amount],
						   t1.[Payment_Type],
						   t1.[User_ID/Transaction_Type],
						   t1.[Payment_Amount],
						   t2.[Sequence_Number],
						   t2.[History_Text],
						   ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
						   
						    FROM format_cacs_archive_cch AS t1
						   
						    
						    LEFT OUTER JOIN format_cacs_archive_history AS t2
						     ON   t1.Converted_Mortgage_Number = t2.Converted_Mortgage_Number
						     AND t1.Activity_Date = t2.Activity_Date
						     AND t1.Activity_Time = t2.Activity_Time
						    							 
						    LEFT JOIN sd_cacs_states AS t3
						     ON   t1.CACS_Location = t3.location
						     AND t1.CACS_State = t3.state
						    							  
						    LEFT JOIN sd_cacs_Location_details AS t4
						     ON t1.CACS_Location = t4.code  
							
						    WHERE (t1.Converted_Mortgage_Number = '$mortgage' $additional_date_params)
							 
						   
							
							)
							
						   SELECT  
								  
						   [Converted_Mortgage_Number],
		                   [Converted_Advance_Number],
						   [Original_Mortgage_Number],
						   CACS_Location,
						   [Batch_Activity_Date],
						   [Activity_Date],
						   [Activity_Time],
						   CACS_State,
						   [Letter_ID],
						   [Actvity_Code],
						   [Placed_Code],
						   [Party_Contacted],
						   [Promise_Amt_1],
						   [Promise_Date_1],
						   [Promise_Amount_2],
						   [Promise_Date_2],
						   [Non_Pay_Excuse_Code],
						   [Arrears_Amount],
						   [Payment_Type],
						   [User_ID/Transaction_Type],
						   [Payment_Amount],
						   [Sequence_Number],
						   [History_Text]
								  
						   FROM results
						   
						   WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
						   ORDER BY [$criteria] $direction ;

I'm getting a 'Incorrect syntax near '('' error now. But I am not sure which parentheses it refers to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 11:56:09

quote:

Originally posted by OldMySQLUser

Thanks for your suggestion. I'm a little unsure as to the correct syntax required. Does this modified script look correct?


DECLARE
							@PageSize INT,
							@PageNumber INT,
							@FirstRow INT,
							@LastRow INT
						   
							SELECT	@PageSize = $pageLength,
							@PageNumber = $pageNumber;
						   
							SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
							@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
					
					
							WITH results
							(
							
						   [Converted_Mortgage_Number],
		                   [Converted_Advance_Number],
						   [Original_Mortgage_Number],
						   CACS_Location,
						   [Batch_Activity_Date],
						   [Activity_Date],
						   [Activity_Time],
						   CACS_State,
						   [Letter_ID],
						   [Actvity_Code],
						   [Placed_Code],
						   [Party_Contacted],
						   [Promise_Amt_1],
						   [Promise_Date_1],
						   [Promise_Amount_2],
						   [Promise_Date_2],
						   [Non_Pay_Excuse_Code],
						   [Arrears_Amount],
						   [Payment_Type],
						   [User_ID/Transaction_Type],
						   [Payment_Amount],
						   [Sequence_Number],
						   [History_Text],
						    RowNumber)		  
							
							)
							 AS
					
					
						 (  SELECT
						   
						   t1.[Converted_Mortgage_Number],
				           t1.[Converted_Advance_Number],
						   t1.[Original_Mortgage_Number],
						   CASE
						     WHEN t4.description IS NOT NULL
						       THEN t4.description
						       ELSE t1.CACS_Location
						   END AS CACS_Location,
						   t1.[Batch_Activity_Date],
						   t1.[Activity_Date],
						   t1.[Activity_Time],
						   CASE
						     WHEN t3.description IS NOT NULL
						       THEN t3.description
						       ELSE t1.CACS_State
						   END AS CACS_State,
						   t1.[Letter_ID],
						   t1.[Actvity_Code],
						   t1.[Placed_Code],
						   t1.[Party_Contacted],
						   t1.[Promise_Amt_1],
						   t1.[Promise_Date_1],
						   t1.[Promise_Amount_2],
						   t1.[Promise_Date_2],
						   t1.[Non_Pay_Excuse_Code],
						   t1.[Arrears_Amount],
						   t1.[Payment_Type],
						   t1.[User_ID/Transaction_Type],
						   t1.[Payment_Amount],
						   t2.[Sequence_Number],
						   t2.[History_Text],
						   ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
						   
						    FROM format_cacs_archive_cch AS t1
						   
						    
						    LEFT OUTER JOIN format_cacs_archive_history AS t2
						     ON   t1.Converted_Mortgage_Number = t2.Converted_Mortgage_Number
						     AND t1.Activity_Date = t2.Activity_Date
						     AND t1.Activity_Time = t2.Activity_Time
						    							 
						    LEFT JOIN sd_cacs_states AS t3
						     ON   t1.CACS_Location = t3.location
						     AND t1.CACS_State = t3.state
						    							  
						    LEFT JOIN sd_cacs_Location_details AS t4
						     ON t1.CACS_Location = t4.code  
							
						    WHERE (t1.Converted_Mortgage_Number = '$mortgage' $additional_date_params)
							 
						   
							
							)
							
						   SELECT  
								  
						   [Converted_Mortgage_Number],
		                   [Converted_Advance_Number],
						   [Original_Mortgage_Number],
						   CACS_Location,
						   [Batch_Activity_Date],
						   [Activity_Date],
						   [Activity_Time],
						   CACS_State,
						   [Letter_ID],
						   [Actvity_Code],
						   [Placed_Code],
						   [Party_Contacted],
						   [Promise_Amt_1],
						   [Promise_Date_1],
						   [Promise_Amount_2],
						   [Promise_Date_2],
						   [Non_Pay_Excuse_Code],
						   [Arrears_Amount],
						   [Payment_Type],
						   [User_ID/Transaction_Type],
						   [Payment_Amount],
						   [Sequence_Number],
						   [History_Text]
								  
						   FROM results
						   
						   WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
						   ORDER BY [$criteria] $direction ;