---
title: "The column prefix does not match with a table name"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/163065-the-column-prefix-does-not-match-with-a-table-name"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "colione"
published: "2007-07-31T12:49:55.000Z"
updated: "2007-08-06T10:10:01.000Z"
replies: 6
views: 446
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/163065-the-column-prefix-does-not-match-with-a-table-name"
---

# The column prefix does not match with a table name

## #1 — colione, 2007-07-31T12:49Z

Hej, jag har fått en hyffsat stor SP på halsen. Den är tydligen nyligen översatt från "vanlig" sql till SP-format. DB är sql server 2000.
Dock får jag ett fel när den körs.

\[Microsoft\]\[ODBC SQL Server Driver\]\[SQL Server\]The column prefix 'P'  
does not match with a table name or alias name used in the query.

/xxx.asp, line 49

rad 49 ser ut såhär: oRS.NextRecordset
SPn ser ut såhär:

```
CREATE PROCEDURE [dbo].[spReportFlags]
	@CenterID int,
	@Datefrom Varchar(50) = Null,
	@Dateto Varchar(50) = Null,
	@OrderBy varchar(50) = 'Flag',
	@OrderDir varchar(4) = 'ASC'
AS
DECLARE @QueryAll varchar(2000)
SET @QueryAll = ''
Set @QueryAll = @QueryAll + 'SELECT DISTINCT COUNT(P.ProjectID) AS countAll '
Set @QueryAll = @QueryAll + 'From Project P '
Set @QueryAll = @QueryAll + 'Inner Join Projectowner Po On Po.Projectid = P.Projectid '
Set @QueryAll = @QueryAll + 'Left Join Projectnote Pn On Po.Projectid = Pn.Projectid '
Set @QueryAll = @QueryAll + 'WHERE P.CenterID = ' + CAST(@CenterID AS varchar(6)) + ' '
If @Datefrom IS NOT NULL And @Dateto IS NOT NULL
	Begin
		Set @QueryAll = @QueryAll + 'and (Pn.Projectnotedate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''' Or P.Firstvisitdate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''') '
	End
Else
	Begin
		If @Datefrom IS NOT NULL 
			Begin
				Set @QueryAll = @QueryAll + 'and (Pn.Projectnotedate >= ''' + @Datefrom + ''' Or P.Firstvisitdate >= ''' + @Datefrom + ''') '
			End
		If @Dateto IS NOT NULL 
			Begin
				Set @QueryAll = @QueryAll + 'and (Pn.Projectnotedate <= '''  + @Dateto + ''' Or P.Firstvisitdate <= '''  + @Dateto + ''') '
			End
	End
EXEC(@QueryAll)
DECLARE @Query varchar(2000)
SET @Query = ''
Set @Query = @Query + 'SELECT FlagID, CenterID, Flag, '
Set @Query = @Query + '('
Set @Query = @Query + 'SELECT DISTINCT COUNT(lPF.ProjectID) '
Set @Query = @Query + 'From tblLinkProjectFlag lPF '
Set @Query = @Query + 'Inner Join Projectowner Po On Po.Projectid = lPF.Projectid '
Set @Query = @Query + 'Left Join Projectnote Pn On Po.Projectid = Pn.Projectid '
Set @Query = @Query + 'WHERE FlagID = tblFlag.FlagID '
If @Datefrom IS NOT NULL And @Dateto IS NOT NULL
	Begin
		Set @Query = @Query + 'and (Pn.Projectnotedate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''' Or P.Firstvisitdate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''') '
	End
Else
	Begin
		If @Datefrom IS NOT NULL 
			Begin
				Set @Query = @Query + 'and (Pn.Projectnotedate >= ''' + @Datefrom + ''' Or P.Firstvisitdate >= ''' + @Datefrom + ''') '
			End
		If @Dateto IS NOT NULL 
			Begin
				Set @Query = @Query + 'and (Pn.Projectnotedate <= '''  + @Dateto + ''' Or P.Firstvisitdate <= '''  + @Dateto + ''') '
			End
	End
Set @Query = @Query + ') AS countProject '
Set @Query = @Query + 'FROM tblFlag '
Set @Query = @Query + 'WHERE CenterID = ' + CAST(@CenterID AS varchar(6)) + ' '
Set @Query = @Query + 'ORDER BY ' + @OrderBy + ' ' + @OrderDir
EXEC(@Query)
DECLARE @QueryNone varchar(2000)
SET @QueryNone = ''
Set @QueryNone = @QueryNone + 'SELECT DISTINCT COUNT(P.ProjectID) AS countNone '
Set @QueryNone = @QueryNone + 'From Project P '
Set @QueryNone = @QueryNone + 'Inner Join Projectowner Po On Po.Projectid = P.Projectid '
Set @QueryNone = @QueryNone + 'Left Join Projectnote Pn On Po.Projectid = Pn.Projectid '
Set @QueryNone = @QueryNone + 'WHERE P.CenterID = ' + CAST(@CenterID AS varchar(6)) + ' '
Set @QueryNone = @QueryNone + 'AND P.ProjectID NOT IN(SELECT ProjectID FROM tblLinkProjectFlag WHERE CenterID = ' + CAST(@CenterID AS varchar(6)) + ') '
If @Datefrom IS NOT NULL And @Dateto IS NOT NULL
	Begin
		Set @QueryNone = @QueryNone + 'and (Pn.Projectnotedate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''' Or P.Firstvisitdate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''') '
	End
Else
	Begin
		If @Datefrom IS NOT NULL 
			Begin
				Set @QueryNone = @QueryNone + 'and (Pn.Projectnotedate >= ''' + @Datefrom + ''' Or P.Firstvisitdate >= ''' + @Datefrom + ''') '
			End
		If @Dateto IS NOT NULL 
			Begin
				Set @QueryNone = @QueryNone + 'and (Pn.Projectnotedate <= '''  + @Dateto + ''' Or P.Firstvisitdate <= '''  + @Dateto + ''') '
			End
	End
EXEC(@QueryNone)
GO
```

Mina egna efterforskningar tyder på att det är för att P använda i sub-queries och att det inte är legalt i SQL server 2000. Stämmer detta och hur skriver jag isf om den?

Permalänk: https://www.webforum.nu/p/163065

## #2 — LarsG, 2007-07-31T12:58Z

Skriv ut den felande sqlsatsen som den ser ut när den är färdigbyggd.

Permalänk: https://www.webforum.nu/p/2010617

## #3 — colione, 2007-07-31T13:12Z

Då det är första gången jag jobbar med SPs så undrar jag hur jag gör det. :)

Permalänk: https://www.webforum.nu/p/2010625

## #4 — LarsG, 2007-07-31T13:42Z

Det finns ett kommando som heter print.

Permalänk: https://www.webforum.nu/p/2010634

## #5 — colione, 2007-08-06T07:52Z

Okej, nu har jag laborerat lite med print och jag får ut följande som jag tror är felaktigt.

Så här ställs själva frågan:

```
SQL = "spReportFlags @CenterID = " & Session("NFCID") & ", @DateFrom = " & DateFrom & ", @DateTo = " & DateTo & ", @OrderBy = 'Flag'"
oRS.Open(SQL), oConn

countAll = oRS("countAll")

Set oRS = oRS.NextRecordset <-- rad 49
Do While Not oRS.EOF
```

Så här ser frågan ut på en response.write

spReportFlags @CenterID = 94, @DateFrom = '2003-05-06', @DateTo = '2007-03-03', @OrderBy = 'Flag' 

Print ser ut såhär:

```
Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT DISTINCT COUNT(P.ProjectID) AS countAll From Project P Inner Join Projectowner Po On Po.Projectid = P.Projectid Left Join Projectnote Pn On Po.Projectid = Pn.Projectid WHERE P.CenterID = 94 and (Pn.Projectnotedate Between '2003-05-06' And '2007-03-03' Or P.Firstvisitdate Between '2003-05-06' And '2007-03-03')
```

Om vi printar nästa ser det ut så här:

```
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT FlagID, CenterID, Flag, (SELECT DISTINCT COUNT(lPF.ProjectID) From tblLinkProjectFlag lPF Inner Join Projectowner Po On Po.Projectid = lPF.Projectid Left Join Projectnote Pn On Po.Projectid = Pn.Projectid WHERE FlagID = tblFlag.FlagID and (Pn.Projectnotedate Between '2003-05-06' And '2007-03-03' Or P.Firstvisitdate Between '2003-05-06' And '2007-03-03') ) AS countProject FROM tblFlag WHERE CenterID = 94 ORDER BY Flag ASC
```

Och tar jag bort den printen får jag samma felmeddelande igen:

```
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'P' does not match with a table name or alias name used in the query.
```

Permalänk: https://www.webforum.nu/p/2012082

## #6 — colione, 2007-08-06T08:08Z

Felet ligger ju i denna.

```
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT FlagID, CenterID, Flag, (SELECT DISTINCT COUNT(lPF.ProjectID) From tblLinkProjectFlag lPF Inner Join Projectowner Po On Po.Projectid = lPF.Projectid Left Join Projectnote Pn On Po.Projectid = Pn.Projectid WHERE FlagID = tblFlag.FlagID and (Pn.Projectnotedate Between '2003-05-06' And '2007-03-03' Or P.Firstvisitdate Between '2003-05-06' And '2007-03-03') ) AS countProject FROM tblFlag WHERE CenterID = 94 ORDER BY Flag ASC
```

Jag försåt varför felet blir som det blir, det finns ingen referens till Project kolumnen i from av ett P. Men hur ska jag skriva om frågan? :)

Det är alltså denna del i SPn som strular:

```
DECLARE @Query varchar(2000)
SET @Query = ''
Set @Query = @Query + 'SELECT FlagID, CenterID, Flag, '
Set @Query = @Query + '('
Set @Query = @Query + 'SELECT DISTINCT COUNT(lPF.ProjectID) '
Set @Query = @Query + 'From tblLinkProjectFlag lPF '
Set @Query = @Query + 'Inner Join Projectowner Po On Po.Projectid = lPF.Projectid '
Set @Query = @Query + 'Left Join Projectnote Pn On Po.Projectid = Pn.Projectid '
Set @Query = @Query + 'WHERE FlagID = tblFlag.FlagID '
If @Datefrom IS NOT NULL And @Dateto IS NOT NULL
	Begin
		Set @Query = @Query + 'and (Pn.Projectnotedate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''' Or P.Firstvisitdate Between ''' + @Datefrom + ''' And '''  + @Dateto + ''') '
	End
Else
	Begin
		If @Datefrom IS NOT NULL 
			Begin
				Set @Query = @Query + 'and (Pn.Projectnotedate >= ''' + @Datefrom + ''' Or P.Firstvisitdate >= ''' + @Datefrom + ''') '
			End
		If @Dateto IS NOT NULL 
			Begin
				Set @Query = @Query + 'and (Pn.Projectnotedate <= '''  + @Dateto + ''' Or P.Firstvisitdate <= '''  + @Dateto + ''') '
			End
	End
Set @Query = @Query + ') AS countProject '
Set @Query = @Query + 'FROM tblFlag '
Set @Query = @Query + 'WHERE CenterID = ' + CAST(@CenterID AS varchar(6)) + ' '
Set @Query = @Query + 'ORDER BY ' + @OrderBy + ' ' + @OrderDir
EXEC(@Query)
```

Permalänk: https://www.webforum.nu/p/2012083

## #7 — LarsG, 2007-08-06T10:10Z

Den färdiag frågan skulle kunna se ut som

```
SELECT FlagID, 
       CenterID, 
       Flag, 
       (SELECT COUNT(distinct lPF.ProjectID) 
          From tblLinkProjectFlag lPF Inner Join Projectowner Po 
            On Po.Projectid = lPF.Projectid Left Join Projectnote Pn 
            On Po.Projectid = Pn.Projectid 
         WHERE FlagID = tblFlag.FlagID 
          and (Pn.Projectnotedate Between '2003-05-06' And '2007-03-03' 
           Or lpf.projectid in 
              (select projectid 
                 from project p
                where P.Firstvisitdate Between '2003-05-06' And '2007-03-03') )) AS countProject 
  FROM tblFlag 
 WHERE CenterID = 94 
 ORDER BY Flag ASC
```

jag tog även bort distinct eftersom du bara får en post i din subselect.

Permalänk: https://www.webforum.nu/p/2012100

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/163065-the-column-prefix-does-not-match-with-a-table-name