Subquery With Invalid Column Name Runs

Oct 24, 2007

Hello!

Here is the statement in question:
--STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM
dbo.sourceTable)

The problem with Statement A is that 'colX' does not exist in
'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'.
Breaking the statement down, we have:

--STATEMENT B
SELECT colX FROM dbo.sourceTable
...which returns the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.

--STATEMENT C
SELECT colX FROM dbo.myTable
....which returns results,


If we modify Statement A to use a join:
--STATEMENT D
SELECT myTable.*
FROM dbo.myTable
JOIN dbo.sourceTable ON sourceTable.colX=myTable.colX
....we get the error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.


Any idea what SQL Server is doing in Statement A?!?!

Thanks!
Anne

View 5 Replies


ADVERTISEMENT

Error Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name

Jan 15, 2008

ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int)
As
Begin
Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1'
print @Sel
Exec Sp_executesql @Sel
End
 
--Execute Mypro 1,4        --->>Here I Executed
 Error
Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between 1 and (1+4)-1
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM
Procedure successfully created but giving error while Excuting'.
Please anybody give reply
Thanks
 

View 2 Replies View Related

A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window

Apr 23, 2008

Performance issue.


I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.

So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.

Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.

Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.

- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.

View 15 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Multiple Column SubQuery

May 14, 2008

I need to query out multuple rows of data from the following table and retrieve it as a single row.   Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL.
Here's an example of the data:UserDefinedFieldId     UserDefinedRowID     FieldValue1                              1                              date (stored as text)2                              1                              text3                              1                              text1                              2                              date (stored as text)2                              2                              text3                              2                              text...27 UserDefinedFieldIds for each row in the actual table
I wrote a working query, but I need something more efficient for this particular application.  (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.)  Already tried shorter table names, which only saved me about a 1000 characters.
SELECT     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 1) AS Column1,     ,,,     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND     Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId
Any suggestions would be appreciated.
Thanks,
Brad

View 3 Replies View Related

Multi Column Subquery? W/ Alias

Jun 12, 2008

What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:

when pricelist = 1

when pricelist = 2

when pricelist = 3


pricelist
--------
1
2
3


Price
--------
912 -- (linked with 1)
234 -- (linked with 3)
56 -- (linked with 2)
3245 -- (linked with 3)
234 -- (linked with 1)
65 -- (linked with 2)

these 2 columns are in the same table^^

so what i want my query to generate is:

Price1
--------
912
234

Price2
--------
56
65

Price3
--------
234
3245

Any help is apprecieated, thanks

if the above does not make sense to you maybe this will:
"can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"

View 10 Replies View Related

Using IS NOT NULL On Column Result From Subquery

Sep 17, 2005

I have a query where one or more of the columns returned is a resultfrom a subquery. These columns get their own alias. I want to filterout the rows containing NULL from the subqueries but it just won'twork. When running columnAlias IS NOT NULL i just get the error"Invalid column name 'columnAlias'.This is the query:SELECT k.UserId, k.Lastname, k.Firstname,(SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =k.UserID) AS myColumnAliasFROM Users kWHERE myColumnAlias IS NOT NULL)When running without the WHERE clause, I get the following results:UserId Lastname Firstname myColumnAlias113 Norman Ola jepps820 Karlsen Kjell830 Pens Jens juubidooWhat I want is to get rid of UserId=820. What am I doing wrong?

View 3 Replies View Related

Transact SQL :: Adding Subquery As A Column

May 19, 2015

I have a subquery i wanted to add a as a fourth column to my  Original Query(is the one below the subquery). How to combine the two queries to one statement?? I tried but was getting an error "Subquery returned more than 1 value "

select  
COUNT(*)FreeReduced
 from students s join Buildings b on
 s.Building_ID = b.Building_ID
 where s.Activeness =1 and (Eligibility = 3 or Eligibility =2) 
 group by building_number,Building_Name

[Code] ....

View 3 Replies View Related

Stored Procedure Just Runs And Runs

Oct 9, 2001

I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?

I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?

Any advice appreciated.

Derek


************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO

CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt

AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */

--SET NOCOUNT ON

DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt

SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID

SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)

CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)

-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'

SELECT * FROM #IGLErrors

-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'

SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors

-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear

SELECT * FROM #IGLErrors

-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo

SELECT * FROM #IGLErrors

END

-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors

-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID

DROP TABLE #IGLErrors

SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID

--SET NOCOUNT OFF

GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO

View 2 Replies View Related

Return Subquery Rows As One Delimited Column

Oct 26, 2006

I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?

View 8 Replies View Related

Sql Query To Update A Column When The Subquery Returns More Than One Row

Oct 6, 2006

Hi People,

I am having a table which has some 10 cols, only one column had all Nulls. DB-SQL2K5

I am now writing a query like

Update Test1

set Id =

(Select t2.Id from

Test2 t2, Test1 t1

where

t2.Name = t1.Name)

as likely this query is faling as the sub query is retuning more than a row. What is the best method to achive my requirement?

Thanks

View 7 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

May 14, 2008

Hi,

I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any ideas as to why??

SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')

Thanks

Jacquues

View 4 Replies View Related

Invalid Column Name

Mar 19, 2004

I get a Invalid Column Name ' '. with this procedure. Can anyone see what migh be wrong?

Thanks,

SELECT A.CompanyName,C.FirstName,C.LastName,C.Client_ID,
CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName] ELSE A.[CompanyName] END AS DRName, C.Client_ID
FROM tblClients C INNER JOIN tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE (C.Client_ID = 15057) AND (A.MailTo=1) AND Convert(varchar(5), GETDATE(), 10) BETWEEN Convert(varchar(5), A.Startdate, 10) AND Convert(varchar(5), A.Enddate, 10) OR (A.Startdate Is Null) AND (A.EndDate Is Null)
GO

View 3 Replies View Related

Invalid Column Name

Aug 19, 2004

Hi the following SP that causes an error.



CREATE PROCEDURE GetInfo
(
@MinPriceint=0,
@MaxPriceint=9999999999,
@TypeHomenvarchar(50)=NULL,
@Locationnvarchar(100)=NULL

)
AS

Declare @strSql nvarchar(255)
Set @strSql="Select * from table WHERE "
Set @strSql=@strSql + 'Price BETWEEN ' + CONVERT(nvarchar(20),@MinPrice) + ' and ' + CONVERT(nvarchar(20),@MaxPrice )

If @TypeHome != "No Preference"
Set @strSql=@strSql + ' and Type = ''' + @TypeHome+ ''''

If @Location != "No Preference"
Set @strSql=@strSql + ' and City = ''' + @Location+ ''''

Set @strSql=@strSql + ' and IDX = ''Y'' ORDER BY Price'
Exec(@strSql)
GO



The Error I get is:
"Error 207: Invalide Column Name 'Select * from table WHERE'
Invalid Column Name 'No Preference'
Invalid Column Name 'No Preference'

I have checked the table and the columns do exist, spelled correctly and caps are all the same. Also, this same SP in another table works just fine.

What is causing this error?

Thanks in advance!

View 1 Replies View Related

Invalid Column Name &#39;x&#39;

Feb 16, 2001

Can anyone tell me why I get the above message using the following stored procedure and passing in a value of 120:
CREATE Procedure qryAnalysisCountMain
(@WizardGroup1Question int)
As
EXEC("SELECT QuestionDescription FROM Questions WHERE QuestionCode = " + @WizardGroup1Question)

120 just happens to be the asci value of 'x' and whatever number I pass in gets converted into it's character equivalent and the sp tells me it can't find that column name. QuestionCode is an int field so there is no problem there

The procedure works OK with:
SELECT QuestionDescription FROM Questions WHERE QuestionCode = @WizardGroup1Question

However I need the SQL in an EXEC as the sp will eventually be dynamic so that i can pass in the name of the table to select from.

Thanks
Martin

View 1 Replies View Related

Invalid Column Name

Mar 30, 2004

Hi
I have a dynamic select statement which is showed below.
declare @query varchar(100)
set @query = 'select * from undergraduate where Gender =' + @Gender
exec (@query)

//
When I execute the @query, I get an error message like "Invalid Column Name Male".
I think I need to put a single quotation around the dynamic variable, so that I have
select * from undergraduate where Gender ='Male'. But I am not sure how to do that.

Thank you for your help!!

View 3 Replies View Related

Invalid Column

May 6, 2008

Hello All,

I m facing problem in one query. What I did is

SELECT
PRODUCT_ID,
PRODUCT_END_DATE,
CASE
WHEN PRODUCT_ID = 1 THEN DATEADD(YY,-5,PRODUCT_END_DATE)
WHEN PRODUCT_ID = 2 THEN DATEADD(YY,-10,PRODUCT_END_DATE)
WHEN PRODUCT_ID = 3 THEN DATEADD(YY,-15,PRODUCT_END_DATE)
END AS MODIFIED_END_DATE
FROM PRODUCTS
WHERE MODIFIED_END_DATE BETWEEN '2008-04-01' AND '2008-04-30'

when I execute this query returns an error as
Invalid column name MODIFIED_END_DATE

So how can I write this query? any idea.

Thanks in advance.



--kneel

View 3 Replies View Related

Invalid Column Name But Right

Jun 16, 2008

I have my column names right but its telling me they are invalid. It must be something to do with how I have my subquery formatted but I don't see it. I was wondering if anyone else can see it? It tells me payer_id is not right and I know its coming from the bolded section. I just added that line to do some additional grouping. I know that the query above aliased as D was working before I put the bolded line in. Am I setting this up wrong?


select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name
from person_payer
where person_id = tmp.person_id
order by payer_id) d
join payer_mstr e on d.payer_id = e.payer_id
join mstr_lists f on e.financial_class = f.mstr_list_item_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

Thanks in Advance!
Sherri

View 2 Replies View Related

Invalid Column Name

Mar 26, 2006

I am building a query and thought I had completed it but I get 'Invalid Column Name "A1" when I run it?
SELECT
Groups.GroupID,
Sum(Stages_On_Route.Distance) AS Miles_Covered,
Groups.Group_Name
FROM Groups
INNER JOIN ((Route INNER JOIN Departure ON (Route.GroupID=Departure.GroupID)
AND (Route.RouteID=Departure.RouteID))
INNER JOIN Stages_On_Route ON Route.RouteID=Stages_On_Route.RouteID)
ON Groups.GroupID=Departure.GroupID
GROUP BY Groups.GroupID,
Groups.Group_Name
HAVING (((Groups.GroupID)="A1"));

View 6 Replies View Related

Invalid Column Name

Dec 4, 2007

Hi I'm new to this forum and I seem to be having a basic problem. I have moved from using MS Access to SQL Server and I seem to be having problem with the SQL below.

SELECT Table_A.*, (Statement_A) AS Value_A ,
(Value_A + 4) AS Value_B

FROM Table_A

I keep on getting an error stating invalid column name, I know that statement A is fine because when I move the calculation into Value_B it works.

Can anyone tell me what I am doing wrong?

View 4 Replies View Related

Why, Invalid Column Name?

Jul 20, 2005

When I try to add the line . . .CPM * MOU AS COST,after all the CASE lines I get the response in SQL Query Analyser,Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'CPM'.Is it because I can not do this on this particular query since CPM isyet to be defined or do I just need to rephrase the request anotherway?The query now looks like this. . .SELECT DISTINCTDATA.dbo.[2004_JANUARY_SUM].RATEKEY,DATA.dbo.[2004_JANUARY_SUM].[DATE],DATA.dbo.[2004_JANUARY_SUM].CXRKEY,DATA.dbo.[2004_JANUARY_SUM].Area,DATA.dbo.[2004_JANUARY_SUM].Region,DATA.dbo.[2004_JANUARY_SUM].Market,DATA.dbo.[2004_JANUARY_SUM].AKA,DATA.dbo.[2004_JANUARY_SUM].MARS_NAME,DATA.dbo.[2004_JANUARY_SUM].O_MTA,DATA.dbo.[2004_JANUARY_SUM].O_MTA_NAME,DATA.dbo.[2004_JANUARY_SUM].O_STATE,DATA.dbo.[2004_JANUARY_SUM].O_LATA,DATA.dbo.[2004_JANUARY_SUM].O_LATA_NAME,DATA.dbo.[2004_JANUARY_SUM].MSC_CLLI,DATA.dbo.[2004_JANUARY_SUM].Trunk,DATA.dbo.[2004_JANUARY_SUM].Carrier,DATA.dbo.[2004_JANUARY_SUM].NPA_NXX,DATA.dbo.[2004_JANUARY_SUM].CALLS,DATA.dbo.[2004_JANUARY_SUM].MOU,DATA.dbo.[2004_JANUARY_SUM].TANDEM,DATA.dbo.[2004_JANUARY_SUM].T_MTA,DATA.dbo.[2004_JANUARY_SUM].T_MTA_NAME,DATA.dbo.[2004_JANUARY_SUM].T_STATE,DATA.dbo.[2004_JANUARY_SUM].T_LATA,DATA.dbo.[2004_JANUARY_SUM].[RC ABBRE],DATA.dbo.[2004_JANUARY_SUM].RC_ID,DATA.dbo.[2004_JANUARY_SUM].SWITCH,DATA.dbo.[2004_JANUARY_SUM].[OCN],DATA.dbo.[2004_JANUARY_SUM].[OCN_NAME],DATA.dbo.[2004_JANUARY_SUM].[CATEGORY],CASE WHEN SUBSTRING(DATA.dbo.[2004_JANUARY_SUM].[MSC_CLLI], 5, 2) =DATA.dbo.[2004_JANUARY_SUM].[T_STATE] THEN(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_GXWHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR3WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR2WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THENTELECOM.DBO.DOMESTIC_LD_RATES2.[INTRA_VENDOR1]ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_TANDEMEND)ELSE(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_GXWHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR3WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR2WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THENTELECOM.DBO.DOMESTIC_LD_RATES2.[INTER_VENDOR1]ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_TANDEMEND)END AS CPM,CPM * MOU as [COST]INTO TEST.dbo.[2004_JANUARY_RATES]FROM DATA.dbo.[2004_JANUARY_SUM] LEFT OUTER JOINTELECOM.dbo.DOMESTIC_LD_RATES2 ONDATA.dbo.[2004_JANUARY_SUM].RATEKEY =TELECOM.dbo.DOMESTIC_LD_RATES2.RATEKEYORDER BY DATA.dbo.[2004_JANUARY_SUM].[T_LATA] DESCOPTION (MAXDOP 2)

View 1 Replies View Related

Invalid Column Name?

May 9, 2008

Is there not a way to perform a calculation on a column comprised from a Case Function?

This is my query:
SELECT

'ComparativeValue' =

CASE

WHEN Id = 1 THEN Index

WHEN Id = 2 THEN Peers

WHEN Id = 3 THEN PrevYear

END,

Variance = (Base - ComparativeValue)/ComparativeValue
FROM Table

I get this error when trying to execute the query:


Invalid column name 'ComparativeValue'.

View 7 Replies View Related

Invalid Column Name

Aug 25, 2006

Below is the T-SQL

SELECT ci.name,
ci.address,
CASE ISNULL(cci.geography,'')
WHEN 'P' THEN 'port'
WHEN 'A' THEN 'appt'
WHEN 'X' THEN 'xatt'
ELSE ''
END AS Link,
ci.InsuranceType
FROM dbo.Insurance ci
INNER JOIN dbo.Contract cci
ON ci.InsuranceId = cci.InsuranceId
AND cci.ContractId = 1266
ORDER BY
ci.Link,
ci.InsuranceType

===========================

My work environment is all in SQL SERVER 2005.

I have a problem with above T-SQL. There is an error while I run above code in Develoment Server.The error disappears when I put 'ci.Link' as 'Link' in OrderBy clause. My work environment has TEST, PRE-PRODUCTION and PRODUCTION. In all other environments the code runs fine with ci.Link but only in DEVELOPMENT the error persists. It has an error as 'Link is invalid column name'.

View 6 Replies View Related

Invalid Column Name

Nov 7, 2007



I need to identify where this column could be, I have checked in the syscolumns but did not find it as a result I am now having to manually check each view and sp that could use the column so I can transfer the database to my new SQL 2005 machine.

Is there an easier way to identify this coloumn by some sort of search??

View 6 Replies View Related

Invalid Column Name

May 16, 2008



Hi,

Can I write parameter name like shown below?





Code Snippet
SELECT 'N' AS LiquidatingFlag, Report, RptSection, Portfolio, RepExcRsn, Units,
FROM Exc_SummaryData_Custom
WHERE (Report IN (@Report)) AND (Portfolio IN (@Portfolio)) AND ( LiquidatingFlag IN(@LiquidatingFlag))

UNION ALL
SELECT 'R' AS LiquidatingFlag, Report, RptSection, Portfolio, RepExcRsn, Units,
FROM Exc_SummaryData_Custom_LIQ
WHERE ( Report IN (@Report)) AND (Portfolio IN (@Portfolio)) AND ( LiquidatingFlag IN (@LiquidatingFlag))







I get a error message saying invalid column name LiquidatingFalg.
(Actually there is no clumn in the original table)Can any one help to work this correctly?

View 5 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

When I execute the below queries it works perfectly where as my expectation is, it should break.

Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)

Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.

create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,

[Code] ....

View 3 Replies View Related

Calcualte Percent Based On The Data In A Column Using Subquery

Apr 9, 2008




ID





TestID





IsCorrect

294817
1
1

294817
1
0

294817
1
1

294817
1
0

301391
2
1

301391
2
1

301391
2
1

301391
2
0

304409
1
1

304409
1
0

304409
1
0

304409
1
0



I am trying to get the percent correct for each ID and each testID from the table above. Also, IsCorrect column valuse (1= correct; 0= not correct)

select ID, count(Iscorrect) [total], (select count(Iscorrect) from dbo.StudentTestAnswers

where TestID=1 and Iscorrect=1) as totalCorrect,((select count(Iscorrect) from dbo.StudentTestAnswers

where TestID=1 and Iscorrect=1)/count(Iscorrect)*100) as PctCorrect

from StudentTestAnswers

where testID=1

Group by ID, TestID

The output should look like this after running the T-SQL code above. But I am getting some weird number on totalCorrect and PctCorrect columns. Not sure if my SQL code is even correct.









ID
total
totalCorrect
PctCorrect

294817
4
2
50%

301391
4
3
75%

304409
4
1
25%

View 7 Replies View Related

Invalid Column Name From C# Sql Query

Nov 15, 2007

 Hi I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:e.g.        SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn);            SqlDataAdapter dataAdapter5 = new SqlDataAdapter();            dataAdapter5.SelectCommand = menubar;            DataSet dataSet5 = new DataSet();            dataAdapter5.Fill(dataSet5);            DataTable selcartest4 = dataSet5.Tables["table"];            if (selcartest4.Rows.Count != 0)The session is called tbname and in that session is a users name however insetad of doing the nornal thing and retrieving the data in the sql database table matching that name it comes up with the following error message:System.Data.SqlClient.SqlException: Invalid column name 'jamie'this is weird as the 'jamie' is the name in the session from the previous page and in fact not a column name at all the column name is pernme  I am totally stuck any help would eb great thanksJ 

View 2 Replies View Related

Invalid Column Name 'Ploeg'.

Dec 19, 2007

When i try to execute i receive following error: 
Msg 207, Level 16, State 1, Procedure WedstrijdDeelnemersSelectAllMPNietGoedgekeurd, Line 85
Invalid column name 'Ploeg'.
 I dont really see whats wrong with the select... it works fine in the 2 first parts of the querryALTER PROCEDURE [dbo].[WedstrijdDeelnemersSelectAllMPNietGoedgekeurd]
-- Add the parameters for the stored procedure here
@WedstrijdID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

...
UNION

SELECT
dbo.fncGetPersoonNaam(L.PersoonID,0) as Persoon,
'Ploeg: ' + WDPI.Ploeg as TypeInschrijving,
WDPIL.LidClubID,
WT.Omschrijving as WedstrijdType,
C.Omschrijving as Categorie,
WDC.WedstrijdDetailID,
WDC.ID as WedstrijdDetailCategorieID,
WDPI.ID as TypeInschrijvingID
FROM
Wedstrijd W
INNER JOIN WedstrijdDetail WD ON W.ID = WD.WedstrijdID
INNER JOIN WedstrijdType WT ON WD.WedstrijdTypeID = WT.ID
INNER JOIN WedstrijdDetailCategorie WDC ON WD.ID = WDC.WedstrijdDetailID
INNER JOIN Categorie C ON WDC.CategorieID = C.ID
INNER JOIN WedstrijdDetailPloegInschrijving WDPI ON WDC.ID = WDPI.WedstrijdDetailCategorieID
INNER JOIN WedstrijdDetailPloegInschrijvingLid WDPIL ON WDPIL.WedstrijdDetailPloegInschrijvingID = WDPI.ID
...END 

View 1 Replies View Related

Invalid Column Error

Jul 11, 2004

Hello all,
Does anyone see anything wrong with the sql query below


DECLARE @BUILDINGLIST nvarchar(100)
SET @BUILDINGLIST = 'ALABAMA'

DECLARE @SQL nvarchar(1024)

SET @SQL = 'SELECT id, CLOSED, building AS BUILDING FROM
requests WHERE building = (' + @BUILDINGLIST + ')'


EXEC sp_executesql @SQL


I keep on getting the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ALABAMA'.


Thanks in advance.
Richard M.

View 2 Replies View Related

SqlException: Invalid Column Name

Jul 30, 2004

Hello,
I have a problem with my Update sql server command. The error message is strange :
System.Data.SqlClient.SqlException: Invalid column name 'CMD_DATE_ORDER'. Invalid column name 'CMD_REF_CLIENT'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.addorder_aspx.MasterGrid_Update(Object Sender, DataGridCommandEventArgs E)
in C:Inetpubwwwrootproject1addorder.aspx:line 157
On line 157 I have :
UpdateCommand.ExecuteNonQuery()

The Update code :
Sub MasterGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
' update the database with the new values
' get the edit text boxes
Dim dateCom As String = CType(e.Item.FindControl("dateCom"), TextBox).Text
Dim NumBon As String = CType(e.Item.FindControl("NumBon"), TextBox).Text
Dim NomDest As String = CType(e.Item.FindControl("NomDest"), TextBox).Text
Dim NovoieDest As String = CType(e.Item.FindControl("NovoieDest"), TextBox).Text
Dim nomvoieDest As String = CType(e.Item.FindControl("nomvoieDest"), TextBox).Text
Dim cpDest As String = CType(e.Item.FindControl("cpDest"), TextBox).Text
Dim villeDest As String = CType(e.Item.FindControl("villeDest"), TextBox).Text
Dim paysDest As String = CType(e.Item.FindControl("paysDest"), TextBox).Text
Dim telDest As String = CType(e.Item.FindControl("telDest"), TextBox).Text
Dim dateExp As String = CType(e.Item.FindControl("dateExp"), TextBox).Text

Dim myConnection As New SqlConnection(strConnect)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection
' Add to CLIID_LGN the selected value in the DropDownList
Dim cliid As String
cliid = Trim(DDL.SelectedItem.Value)

If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO Commandes (CMD_DATE_ORDER, CMD_NUM_BON_ORDER, CMD_NOM_DEST, CMD_NOVOIE_DEST, CMD_NOMVOIE_DEST, CMD_CP_DEST, CMD_VILLE_DEST, CMD_PAYS_DEST, CMD_TEL_DEST,
CMD_DATE_EXPED, CMD_REF_CLIENT) VALUES ('" & dateCom & "','" & NumBon & "','" & NomDest & "','" & NovoieDest & "','" & nomvoieDest & "','" & cpDest & "','" & villeDest & "','" & paysDest & "','" & telDest & "',
'" & dateExp & "', '" & cliid & "')"
Else
UpdateCommand.CommandText = "UPDATE Client SET CMD_DATE_ORDER=@dateCom, CMD_NUM_BON_ORDER = @NumBon, CMD_NOM_DEST = @NomDest, CMD_NOVOIE_DEST = @NovoieDest, CMD_NOMVOIE_DEST = @NomvoieDest,
CMD_CP_DEST = @cpDest, CMD_VILLE_DEST = @villeDest, CMD_PAYS_DEST = @paysDest, CMD_TEL_DEST = @telDest, CMD_DATE_EXPED = @dateExp WHERE CMD_REF_CLIENT = '" & cliid & "'"
End If

UpdateCommand.Parameters.Add("@dateCom", SqldbType.SmallDateTime, 4).Value = Trim(dateCom)
UpdateCommand.Parameters.Add("@NumBon", SqldbType.NVarChar, 10).Value = Trim(NumBon)
UpdateCommand.Parameters.Add("@NomDest", SqldbType.NVarChar, 50).Value = Trim(NomDest)
UpdateCommand.Parameters.Add("@NovoieDest", SqldbType.NVarChar, 5).Value = Trim(NovoieDest)
UpdateCommand.Parameters.Add("@nomvoieDest", SqldbType.NVarChar, 80).Value = Trim(nomvoieDest)
UpdateCommand.Parameters.Add("@cpDest", SqldbType.NVarChar, 5).Value = Trim(cpDest)
UpdateCommand.Parameters.Add("@villeDest", SqldbType.NVarChar, 35).Value = Trim(villeDest)
UpdateCommand.Parameters.Add("@paysDest", SqldbType.NVarChar, 35).Value = Trim(paysDest)
UpdateCommand.Parameters.Add("@telDest", SqldbType.NVarChar, 14).Value = Trim(telDest)
UpdateCommand.Parameters.Add("@dateExp", SqldbType.SmallDateTime, 4).Value = Trim(dateExp)

' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()

Catch ex as Exception
Message.Text = ex.ToString()

Finally
myConnection.Close()

End Try

' Resort the grid for new records
If AddingNew = True Then
MasterGrid.CurrentPageIndex = 0
AddingNew = false
End If

' rebind the grid
MasterGrid.EditItemIndex = -1
BindMasterGrid()
End Sub
I have send a response.write instruction : the values are all good.
In the sql server database, the syntax of this 2 fields CMD_DATE_ORDER and CMD_REF_CLIENT are good.

Can you help me for this problem ?
Thanks.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved