The column prefix does not match with a table name

6 svar · 431 visningar · startad av colione 31 juli 2007

colioneMedlem sedan juni 20014 421 inlägg

Frågan31 juli 2007 12:49#1

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?

LarsGMedlem sedan dec. 200012 464 inlägg

31 juli 2007 12:58#2

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

colioneMedlem sedan juni 20014 421 inlägg

31 juli 2007 13:12#3

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

LarsGMedlem sedan dec. 200012 464 inlägg

31 juli 2007 13:42#4

Det finns ett kommando som heter print.

colioneMedlem sedan juni 20014 421 inlägg

6 aug. 2007 07:52#5

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.

colioneMedlem sedan juni 20014 421 inlägg

6 aug. 2007 08:08#6

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)

LarsGMedlem sedan dec. 200012 464 inlägg

6 aug. 2007 10:10#7

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.