Please Help
-
I have this query and it's returning the following error - any suggestions?
SELECT PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType,
RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL,
(Select count(*) from PartnerLeadTypes e2 where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID) as e.rownumber
FROM dbo.PartnerLeadTypes e
wHERE e.rownumber BETWEEN 0 and 10
Line 5: Incorrect syntax near '.'.
-
right here:
Select count(*) from PartnerLeadTypes e2 where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID) as e.rownumber
Take the e. off the alias.
-
Then I get an 'invalid column name ' error message..
Any other ideas?
-
(select count(*) .. ) AS [RowNumber]
N 56°04'39.16"
E 12°55'05.25" -
Still get the 'invalid column name' message.
Should I be changing the order in which I'm doing things?
-
SG, can you post exactly what you are running when you get that message, as well as a script dump of the tables in question? Either there is a typo somewhere, or the column names provided are incorrect.
-
Try this
SELECT A.PartnerLeadTypeID,
A.PartnerID,
A.LeadTypeID,
A.isActive,
A.AllowedStates,
A.RestrictedStates,
A.CreditHistory,
A.LoanAmount,
A.PropertyValue,
A.LoanToValue,
A.PropertyType,
A.RestrictedCityState,
A.SoftCap,
A.HardCap,
A.HasLender,
A.HasAgent,
A.FoundHome,
A.Bankruptcy,
A.ProdPostURL,
B.RowNumber
FROM dbo.PartnerLeadTypes AS E
LEFT JOIN (SELECT PartnerLeadTypeID,COUNT(1) AS RowNumber FROM dbo.PartnerLeadTypes GROUP BY PartnerLeadTypeID) AS B
ON E.PartnerLeadTypeID <= B.PartnerLeadTypeID
WHERE B.RowNumber BETWEEN 0 AND 10
-Ram
-
Here is the query
SELECT PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType,
RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL,
(Select count(*) from PartnerLeadTypes e2 where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID) as e.rownumber
FROM dbo.PartnerLeadTypes e
wHERE e.rownumber BETWEEN 0 and 10
Here is the schema
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PartnerLeadTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PartnerLeadTypes]
GO
CREATE TABLE [dbo].[PartnerLeadTypes] (
[PartnerLeadTypeID] [bigint] IDENTITY (1, 1) NOT NULL ,
[PartnerID] [bigint] NOT NULL ,
[LeadTypeID] [tinyint] NOT NULL ,
[isActive] [bit] NULL ,
[Notes] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LeadFee] [money] NULL ,
[DateCreated] [datetime] NULL ,
[AllowedStates] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RestrictedStates] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditHistory] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LoanAmount] [money] NOT NULL ,
[PropertyValue] [money] NOT NULL ,
[LoanToValue] [float] NOT NULL ,
[PropertyType] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RestrictedCityState] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FixedCap] [int] NOT NULL ,
[SoftCap] [int] NOT NULL ,
[HardCap] [int] NOT NULL ,
[HasLender] [smallint] NOT NULL ,
[HasAgent] [smallint] NOT NULL ,
[FoundHome] [smallint] NOT NULL ,
[Bankruptcy] [smallint] NOT NULL ,
[ProdPostURL] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdatedId] [int] NOT NULL ,
[LastUpdatedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PartnerLeadTypes] WITH NOCHECK ADD
CONSTRAINT [PK_PartnerLeadTypes] PRIMARY KEY CLUSTERED
(
[PartnerLeadTypeID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
ALTER TABLE [dbo].[PartnerLeadTypes] ADD
CONSTRAINT [DF_PartnerLeadTypes_isActive] DEFAULT (0) FOR [isActive],
CONSTRAINT [DF_PartnerLeadTypes_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [DF_PartnerLeadTypes_LastUpdatedDate] DEFAULT (getdate()) FOR [LastUpdatedDate]
GO
CREATE INDEX [ix_PartnerLeadTypeActive] ON [dbo].[PartnerLeadTypes]([PartnerID], [LeadTypeID], [isActive]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-
I thought that might be it. Try this as a quick fix and see if it works, then it can be expanded upon.
SELECT
d.*
FROM
(
SELECT PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType,
RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL,
(Select count(*) from PartnerLeadTypes e2 where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID) as e.rownumber
FROM dbo.PartnerLeadTypes e
) AS d
wHERE
d.rownumber BETWEEN 0 and 10
What are you trying to accomplish, by the way? Just from looking at your code, I'd guess that a SELECT TOP 10 ... ORDER BY PartnerLeadTypeID would do the trick, but maybe I missed something in your code.
-
I'm thinking you still need to remove the "e." from the column alias... everything else in David's post should be just about spot on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oops, you're right. He definitely needs to lose that "e." from rownumber in the derived table.
-
First, change "e.rownumber" to rownumber".
Now, the remaining problem is with the WHERE clause. You cannot use the column alias (rownumber) - the original expression must be used.
Replace:
wHERE e.rownumber BETWEEN 0 and 10
With:
WHERE (Select count(*)
from PartnerLeadTypes e2
where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID
) BETWEEN 0 and 10
Here is the modified query:
SELECT PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates
, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType
, RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL
, (Select count(*)
from PartnerLeadTypes e2
where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID
) AS rownumber
FROM dbo.PartnerLeadTypes e
WHERE (Select count(*)
from PartnerLeadTypes e2
where e2.PartnerLeadTypeID <= e.PartnerLeadTypeID
) BETWEEN 0 and 10
-
That's why we turned it into a derived table. The column alias will work like a charm in the WHERE clause using that method.
-
Yes, I see that now. The code you posted had a bug in it (still used the e.rownumber alias in the derived table query), and I didn't look closely at it.
-
You could also do this using a group by statement:
SELECT PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType,
RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL,
count(*) as rownumber
FROM dbo.PartnerLeadTypes
Group By PartnerLeadTypeID, PartnerID, LeadTypeID, isActive, AllowedStates, RestrictedStates, CreditHistory, LoanAmount, PropertyValue, LoanToValue, PropertyType, RestrictedCityState, SoftCap, HardCap, HasLender, HasAgent, FoundHome, Bankruptcy, ProdPostURL
Having Count(*) BETWEEN 0 and 10
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply