Problem Referencing A Column In A Table Variable In A Query.

Apr 23, 2006

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.

declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable

Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table


Thanks in advance

smHaig









View 1 Replies


ADVERTISEMENT

Referencing A Table By Variable

Jul 18, 2000

I would like a stored proc to be fed the table name as a paramater.
I tried below

create procedure testit as

set nocount on
declare @tblnm varchar(50)
select * from @tblnm

but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.

Any idea

View 1 Replies View Related

Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)

Jun 15, 2006

For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID  . . . . . . . .  .  .  int
Name  .  .  .  .  .  .  .  .  .  .  .  nvarchar(50)
ManagerID  . . . . . . . .  .  .  .  int
 
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?

View 6 Replies View Related

SQL Server Table's Column Referencing Two Parent Tables.

Feb 15, 2008

Hi everybody, 
I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
 
 

View 2 Replies View Related

Stored Procedure && Referencing A Database With A Variable

Aug 16, 2006

Hi all,
I wonder if there is anybody that can help with this one.
As you know, you can pass variables into a stored procedure and then use these variables for table names, columns etc.
Now, you also know that you can access another database on the same server just by typing the name of it into your query.
The hard part:
Instead of hardcoding the database name into the stored procedure i want to use a variable and then pass this to reference the database name, in the same way you would reference anything else with a variable. I need to do this as i have to search for various results across multiple databases.
We currently use SQL server 2000 standard, though are looking into SQL 2005 - especially is this problem is easy to resolve in the latter.
Look forward to your help
Regards
Darren
 
 

View 6 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related

Using A Variable For A Column Name In A Query

Sep 22, 2006

Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE      CASE WHEN @Field = 'Fname' THEN Fname = @Value END,     CASE WHEN @Field  = 'Lname' THEN Lname = @Value END,     CASE WHEN @Field  = 'Phone' THEN Phone = @Value END;

View 1 Replies View Related

ADD COLUMN To Variable Table?

Sep 29, 2007

Code Block

ALTER TABLE @OrderPrice ADD(description varchar(50) );


What's wrong?

View 3 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 10 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 1 Replies View Related

Using A Variable To Specify Table Column In Sql Statement??

Feb 6, 2008

Hello everyone,
I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases).  I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement?  For example:
SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE
This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions.
Thanks
Scott

View 15 Replies View Related

CHECK Constraint - Referencing Another Column

Aug 31, 2000

I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

Thanks in advance!

Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.

Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)


.

View 1 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?
 
 

View 4 Replies View Related

Referencing Column List For Foreign Key No Match

Oct 26, 2013

I'm encountering this very weird problem, so I create a staff table:

CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);

and then when I create a professional therapist table

CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);

It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.

View 3 Replies View Related

How To Get Query Second Column Value/other Rows While Assigning To A Variable In A Stored Procedure?

Feb 6, 2008

Hi,
I'm try to get the query second column value when it is assinged to a varchar variable.
Ex:In SP below is statement I wrote
SET @Values  =  (SELECT COL1,COL2 FROM TABLE)
Question 1:  How to access the COL2 value from @Value2?
Question 2:  How to access the other row values if above returns more than a row?
Please send me the solution as soon as possible.
Thanks-Vikash/Bala

View 3 Replies View Related

SQL Server 2012 :: Table Variable Update Column

May 29, 2014

I am writing a query to update table variable. It is throwing me some error.

I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table

update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID

View 9 Replies View Related

Transact SQL :: Update Multiple Table Referencing New Table Data

Aug 4, 2015

I have a table called ADSCHL which contains the school_code as Primary key and other two table as

RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.

I tried using Merge Update function not sure if this is going to work.

Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name

View 10 Replies View Related

Integration Services :: How To Compare SSIS Variable And Column In Table

Apr 21, 2015

My Requirement IS : 1<sup>st</sup>run: if the record does not exist in the table insert the record (file_name, last_modified_file_date) and create a copy in the archive folder with file_name_currentdate.csv

Daily run: retrieve the last_modified_file_date from the input file and check if the retrieved date is greater than the last_modified_file_date in the table:

If true: create a copy of the input file in the archive folder and update the last_modified_file_date in the table with the retrieved date

If false don’t do nothing because the file has been archived in one of the previous runs.I have already retrieving the modified date and File Nae iserting into Filename Table: (That table has 2 columns which are FileName and FileDate) so In script task everytime the variable getting Modified date(retrieve the last_modified_file_date from the input file). How I can Compre the existing table record and variable. I have already imported the all Filenames and Modified into table like below.

View 3 Replies View Related

Correlated Subquery Column Referencing Outer Date Range

Aug 25, 2006







Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?

Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)

select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'


Thanks

View 9 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:


SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x


This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:


SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'



'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

T-SQL (SS2K8) :: Insert Subset Of Self-referencing Table Into That Table

Mar 19, 2015

IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO

CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,

[Code] ....

gives:

nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C

I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows

4 4 Two A
5 4 Two B
6 5 Tow C

View 5 Replies View Related

Random Selection From Table Variable In Subquery As A Column In Select Statement

Nov 7, 2007

Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?




SET NOCOUNT ON


Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end


Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'


select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase



SET NOCOUNT OFF

View 14 Replies View Related

Referencing Data From A Query

Sep 30, 2006

I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure:

Quote: CREATE PROCEDURE dbo.SP_Check_Login (
@arg_UserEmail VARCHAR(255),
@arg_UserPassword VARCHAR(255))
AS

BEGIN

SELECT a.userArchived,a.userPasswordDate
FROM app_users a
WHERE a.userEmail = @arg_UserEmail
AND a.userPassword = @arg_UserPassword

END;
GO

What I want to do is some conditional statements on the query results.

i.e:
IF (userArchived = 1)
RETURN "Archived"
ELSE IF (userPasswordDate < dateadd(month,-3,getdate())
RETURN "UpdatePassword"
ELSE
RETURN "OK"

So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code?

Thanks, Mike

View 2 Replies View Related

Self-referencing Table

Sep 1, 2007

Hi,

I'm using MS SQL 2005 Express.

CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO

if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.

The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

Anyone got any advice?

View 1 Replies View Related

Referencing A Calculated Field In A Query

Nov 24, 2005

I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005

SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2

This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.

The only way how to avoid the issue in the SQLExpress seems probably to be

- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)

- Utilize computed columns if possible (no idea on performance impact here)

Am I right or is there any other way how to reference a calculated field?

Any suggestion is greatly welcomed! Thanks in advance.

View 1 Replies View Related

Recursive Query Without Self-Referencing Structure

Sep 13, 2007

I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with:

WITH Folders (ID, ParentID, FolderName, FolderDepth)AS ( SELECT FOLDER_ID, NULL, FOLDER_NAME, 0 FROM ZZ_FOLDER WHERE FOLDER_ID = 1 UNION ALL SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1 FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID WHERE FP.FPROP_LINK_ID = Folders.ID )SELECT * FROM Folders

The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get:

Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.FolderDepth" could not be bound.

It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong? Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against.

-- Create Folder TablesCREATE TABLE ZZ_FOLDER ( FOLDER_ID INTEGER NOT NULL PRIMARY KEY, FOLDER_NAME VARCHAR(50))CREATE TABLE ZZ_FOLDER_PROP ( FPROP_ID INTEGER NOT NULL PRIMARY KEY, FPROP_FOLDER_ID INTEGER NOT NULL, FPROP_LINK_TYPE VARCHAR(50), FPROP_LINK_ID INTEGER NOT NULL)GO-- Populate Folder TablesINSERT INTO ZZ_FOLDER VALUES (1, 'Top Level')INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A')INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B')INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C')INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1')INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2')INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3')INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1')INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1')INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2')INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A')INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3)INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7)GO/*Top Level Second Level A Third Level A1 Third Level A2 Third Level A3 Fourth Level A3A Second Level B Third Level B1 Second Level C Third Level C1 Third Level C2*/

View 3 Replies View Related

Updating Table Referencing 2nd Table Using Case

Feb 9, 2008

Hi

Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...

Using T-sql!!!

update import set import.gone =
from import
inner join stat
ON stat.id = import.id
CASE
WHEN stat.A = import.field2 THEN import.gone = sec.A
WHEN stat.B = import.field2 THEN import.gone = sec.B
WHEN stat.C = import.field2 THEN import.gone = sec.C
WHEN stat.D = import.field2 THEN import.gone = sec.D
WHEN stat.E = import.field2 THEN import.gone = sec.E
WHEN stat.F = import.field2 THEN import.gone = sec.F
ELSE import.gone = null
END

Any help would be greatly appreciated

View 3 Replies View Related

Inserting Data Into A Table Referencing PK From Another Table

May 12, 2008

How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event

Schedules data is entered into the Schedules Table first

then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.

How do i insert data into Event table referencing the (PK ID) from Schedules Table ?


Fields inside each of the tables can be found below:




Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment

Schedule Table
(PK) ID


AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks

View 2 Replies View Related

Select Self Referencing Table

May 25, 2005

I have a table that holds a ParentID and the RecordID.  There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not.  0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if   '   1- 2   ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J

View 4 Replies View Related







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