SQL 2005 - the Column Was Specified Multiple Times

SQL 2005 - The column was specified multiple times

The problem, as mentioned, is that you are selecting PEID from two tables, the solution is to specify which PEID do you want, for example

 SELECT tb.*
    FROM (
        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
    ) AS tb;

That aside, as Chris Lively cleverly points out in a comment the outer SELECT is totally superfluous. The following is totally equivalent to the first.

        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

or even

        SELECT * 
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

but please avoid using SELECT * whenever possible. It may work while you are doing interactive queries to save typing, but in production code never use it.

SQL The column 'Id' was specified multiple times

Instead of select * use select table.columnname or tablename.*.

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

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.

MS SQL 2005: Problems with alias in subquery

Update: Added business logic

select 
    serial, [13] as [TRth0], [24] as [TRth1],[35] as [TRth2]
from
    (
        select serial,Calvalue,CalID
            from
            (
                select 
                    Serial,
                    Rank() OVER (Partition by Serial order by RunId desc) as rank,
                    calvalue,
                    calid 
                from
                (
                    Select *,count(1) OVER (partition by Serial,runid ) as count from Calibrationdata 
                        where 
                            calValue<>0.00 and 
                                case 
                                    when (calid=13 and calvalue =-400)
                                    or (calid=24 and calvalue =0.03)
                                    then 0 ELSE 1 END =1
                ) d where d.count=3
            ) c
            where c.rank=1
    ) s
PIVOT
( max(calvalue) for calid in ([13],[24],[35])) p

updated fiddle link:http://sqlfiddle.com/#!3/c5f52/4

I used rank() in inner query to get only last run's reading for each serial
Then I used PIVOT to transform the output.

please try the below query:

select serial, [13] as [TRth0], [24] as [TRth1],[35] as [TRth2]
from
(
  select 
     serial,Calvalue,CalID
  from
     (
      select 
         Serial, 
         Rank() OVER (Partition by Serial order by RunId desc) as rank, 
         calvalue,
         calid
      from Calibrationdata
      ) c
   where c.rank=1
  ) s
PIVOT
 ( 
   max(calvalue) for calid in ([13],[24],[35])
 ) p

sql fiddle link http://sqlfiddle.com/#!3/01c12/7

How to search a column in multiple tables in SQL Server 2005

You can do:

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%columnName%'

---

Related Topics