SQL Server 2005 query syntax error: "The column 'id_ctrc' was specified multiple times for 'inner_tbl'."

WITH outer_tbl
AS (
  SELECT ROW_NUMBER() OVER (ORDER BY inner_tbl.ctrc_data DESC ) AS KOHANA_DB_ROWNUM, * 
  FROM (
    SELECT * FROM rkm_sac_ctrc 
      INNER JOIN rkm_sac_nf ON (rkm_sac_ctrc.id_ctrc = rkm_sac_nf.id_ctrc) 
    WHERE rkm_sac_ctrc.pag_cnpj = '46.344.354/0005-88' 
    AND rkm_sac_nf.nf_numero = '2023'
  ) AS inn开发者_C百科er_tbl
) 
SELECT * FROM outer_tbl WHERE KOHANA_DB_ROWNUM BETWEEN 1 AND 15

The error is:

The column 'id_ctrc' was specified multiple times for 'inner_tbl'.

The column 'id_ctrc' was specified multiple times for 'outer_tbl'.

What am I doing wrong?

Don't use select *. You need to have specific column names for each column and you havea join hence two columns with the same name. You should never be using select * in any event but especially when you have a join becasue you are returning extra unneded information.

try to include the schema of the table

Most likely reason is the schema - your code will most likely run against dbo. Your views are possibly in another schema.

By default in Management Studio, your SQL may be running against the Master database.

That would be why it can't find your tables, which are in another database.

Try adding a USE statement before your SQL, such as USE DatabaseName.

If that doesn't solve the problem, then you may need to prefix the table names with the shema and/or owner name. Quite often, that is dbo. But it could be something else. In Management Studio, you can see that easily enough in the Object Explorer pane. Look at the view names there.

are you sure that you are running this in the proper database? run this query to check:

select
    TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME
    from INFORMATION_SCHEMA.Views 
    where TABLE_NAME='rkm_sac_ctrc'

if it is returned, use schema name given in the results