Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

View 11 Replies


ADVERTISEMENT

Common Table Expression

May 28, 2008

Hi
 
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
 
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan 
 

View 3 Replies View Related

Common Table Expression?

Jul 20, 2005

What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston

View 2 Replies View Related

Recursive Common Table Expression Data?

Jan 10, 2014

With my new data, need result for below scenario ( use CTE )

´`````````````
CREATE TABLE
´´´´´´´´´´´´´´´´
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,

[Code] ....

===============================
Insert DATA TABLE 1
===============================

INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')

===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,

2.HasNodes = Yes, Display Parent and 1st Node

3.HasNodes = No, Display Parent Only.

================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6

===============
My Query
=============

learning CTE To apply for HasNodes Rules.

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (

[code]....

View 4 Replies View Related

Multiple Operations On A Common Table Expression

Jan 28, 2008

Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.

What is wrong with my syntax?


;With OrderedTable

AS

(

select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee

)

delete from OrderedTable where RowId != 1

SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc

View 1 Replies View Related

Conditional Processing From A Common Table Expression (CTE)

Sep 5, 2007

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:


WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

View 3 Replies View Related

Is WITH / Common Table Expression A Syntactic Sugar?

Oct 4, 2006

I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.

However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.

Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?

View 5 Replies View Related

Unable To ALTER A Temporary Table In TSQL

Jul 20, 2005

Is it possible to ALTER a temporary table in TSQL (SQL2000)?The following TSQL reports a syntax error at the ALTER TABLE line:DECLARE @Test TABLE([Col1] [int] NOT NULL ,[Col2] [int] NOT NULL)ALTER TABLE @TestDROP COLUMN Col2I was hoping this would work the same way as a normal table. For example:CREATE TABLE [dbo].[Test] ([Col1] [int] NOT NULL ,[Col2] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE TestDROP COLUMN Col2EXEC sp_help TestDROP TABLE Test

View 1 Replies View Related

Encountered Problems With Multiple Common Table Expression

Feb 20, 2006

Hi guys,

I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.

The following are the errors

Code:


Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.



I'm using SQL Server Express

View 4 Replies View Related

SQL Server 2005 Common Table Expression(CTE) Implement Reg.

Aug 30, 2006

Hi,

We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.

Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.

In ASP Application, We call this query €œ081Stats€? from Record set.

This €˜081Stats€™ query call below sub query itself

Master Query: 081Stats

SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;

Sub Query 1: 08Accepted

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 2: 08Applicants

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 3: 08Dinged

SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 4: 08Interviewed

SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 5: 08Matriculating

SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.

How can I call that CTE from ASP.NET 2.0 using C#?

CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.

How can I implement the CTE, where can I write the CTE and where can I store the CTE.

And how can I call the CTE from ASP.NET 2.0 using C#.

It€™s Very Urgent Requirements.

We need your timely help.

With Thanks & Regards,
Sundar

View 1 Replies View Related

Common Table Expression Calling Stored Procedure

Nov 19, 2007

Hi,

I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.

So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.

This is what I want to change:

Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))

Insert into @TempTable
Exec Procedure @Paramter

select @Col = col2 from @TempTable

INTO

With CTE(Col2, Col3, Col4) AS
(

Exec Procedure @Paramter)

select @Col = col2 from @TempTable

Thanks
Punu

View 6 Replies View Related

Difference In Creating Temporary Table By #table And ##table

Nov 29, 2006

Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply
Banti"

View 1 Replies View Related

Grouping By Month In Common Table Expression Counts Wrong

Sep 26, 2012

I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date

[code]...

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.

View 3 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

SQL Server 2012 :: Filtering Common Table Expression Within View

Sep 1, 2015

I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...

ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId

[code]....

View 4 Replies View Related

Preferred Method To Incorporate Common Table Expression (CTE) Functionality

Feb 26, 2007

What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset?  The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and  then limiting the persisted results based on the CTE function. 

It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
 
 

 

View 1 Replies View Related

Speed/efficiency Of View Vs. Common/nested Table Expression In A Join

Mar 2, 2008



i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.

i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.

the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?

View 2 Replies View Related

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

Common Table Expression (CTE) T-SQL Errors:Invalid Object Name 'ProductItemPrices'.The Variablename '@TopEmp' Has Been Declared

Jan 4, 2008

Hi all,
I copied the following code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CET.sql--

USE AdventureWorks

GO

--Use column value from a table pointed at by a foreign key

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT p.Name, pp.AvgPrice

FROM ProductItemPrices pp

JOIN

Production.Product p

ON

pp.ProductID = p.ProductID

ORDER BY p.Name

SELECT * FROM ProductItemPrices

GO

--Display rows from SalesOrderDetail table with a LineTotal

--value greater than the average for all Linetotal values with

--the same ProductID value

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice

FROM Sales.SalesOrderDetail sd

JOIN

ProductItemPrices pp

ON pp.ProductID = sd.ProductID

WHERE sd.LineTotal > pp.AvgPrice

ORDER BY sd.SalesOrderID, sd.ProductID



--Return EmployeeID along with first and last name of employees

--not reporting to any other employee

SELECT e.EmployeeID, c.FirstName, c.LastName
JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID)

JOIN Empcte a

ON e.ManagerID = a.empid)

--Order and display result set from CTE

SELECT * Hi all,

I copied the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):

--CTE.sql--

USE AdventureWorks

GO

--Use column value from a table pointed at by a foreign key

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT p.Name, pp.AvgPrice

FROM ProductItemPrices pp

JOIN

Production.Product p

ON

pp.ProductID = p.ProductID

ORDER BY p.Name

SELECT * FROM ProductItemPrices

GO

--Display rows from SalesOrderDetail table with a LineTotal

--value greater than the average for all Linetotal values with

--the same ProductID value

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice

FROM Sales.SalesOrderDetail sd

JOIN

ProductItemPrices pp

ON pp.ProductID = sd.ProductID

WHERE sd.LineTotal > pp.AvgPrice

ORDER BY sd.SalesOrderID, sd.ProductID



--Return EmployeeID along with first and last name of employees

--not reporting to any other employee

SELECT e.EmployeeID, c.FirstName, c.LastName

FROM HumanResources.Employee e

JOIN Person.Contact c

ON e.ContactID = c.ContactID

where ManagerID IS NULL

--Specify top level EmployeeID for direct reports

DECLARE @TopEmp as int

SET @TopEmp = 109;

--Names and departments for direct reports to

--EmployeeID = @TopEmp; calculate employee name

WITH Empcte(empid, empname, mgrid, dName, lvl)

AS

(

-- Anchor row

SELECT e.EmployeeID,

REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +

' ' + c.LastName, ' ', ' ') 'Employee name',

e.ManagerID, dn.Name, 0

FROM Person.Contact c

JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID

WHERE e.EmployeeID = @TopEmp

UNION ALL

-- Recursive rows

SELECT e.EmployeeID,

REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +

' ' + c.LastName, ' ', ' ') 'Employee name',

e.ManagerID, dn.Name, a.lvl+1

FROM (Person.Contact c

JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID)

JOIN Empcte a

ON e.ManagerID = a.empid)

--Order and display result set from CTE

SELECT *

FROM Empcte

WHERE lvl <= 1

ORDER BY lvl, mgrid, empid

--Alternate statement using MAXRECURSION;

--must position immediately after Empcte to work

SELECT *

FROM Empcte

OPTION (MAXRECURSION 1)
====================================
This is Part 1 (due to the length of input > 50000 characters).
Scott Chang

View 5 Replies View Related

Common Table Expression (CTE):How To Delete A Wrong CTE That Is In SQL Server Management Studio Express (SSMSE)?

Feb 25, 2008

Hi all,

I ran the following CTE sql code:


Use ChemDatabase

GO

WITH PivotedTestResults AS

(

SELECT TR.AnalyteName, TR.Unit,

Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),

Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),

QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)

FROM TestResults TR

JOIN Samples S ON TR.SampleID = S.SampleID

GROUP BY TR.AnalyteName, TR.Unit

)

SELECT AnalyteName, UnitForConc,

avg1 = abs(Prim + Dupl) / 2,

avg2 = abs(Prim + QA) / 2,

avg3 = abs(Dupl + QA) / 2,

RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,

RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,

RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2

FROM PivotedTestResults

GO

//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:

Msg 207, Level 16, State 1, Line 9

Invalid column name 'Unit'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'Unit'.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?

Please help and advise.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Creating Temporary Table

Jul 20, 2005

Hi,How can I create a temporary table say "Tblabc" with column fieldsShmCoy char(2)ShmAcno char(10)ShmName1 varchar(60)ShmName2 varchar(60)and fill the table from the data extracted from the statement..."select logdetail from shractivitylog"The above query returns single value field the data seperated with a '·'Ex:BR··Light Blue Duck··in this case I should getShmCoy = 'BR'ShmAcno = ''ShmName1 = 'Light Blue Duck'ShmName2 = ''I want to do this job with single SQL query. Is it possible. Pls help.Herewith I am providing the sample dataBR··Light Blue Duck···0234578···BR··Aqua Duck···0234586···UB··Aqua Duck··Regards,Omav

View 1 Replies View Related

Need Help Creating A Temporary Table In MS SQL Server.

Oct 18, 2006

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end user
starts entering the contents for a file that is going to be generated once he
finishes entering the data, but the problem is that if more than one user is
doing the same process the data would get mixed up.  To avoid this I
thought in creating a temporary table (its name will consist of a string
and the current date time). 

 I would like to see any tutorial
about creating and working with temp tables.  Or if you have any
suggestions, I will appreciate them.  Thanks

View 1 Replies View Related

Creating Temporary Table With SELECT INTO

Jul 20, 2005

HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal

View 4 Replies View Related

Problem While Creating Index For Temporary Table...

Sep 27, 2007



Hi,

i have created index for a temporary table and this script should used by multiusers.So when second user connecting to it is giving index i mean object already exists.

So what i need is when the second user connected the script should create one more index on temporary table.Will sql server provide any random way of creating indexes if the index exists already with that name??

Thank You,

View 6 Replies View Related

SQL Server 2008 :: Creating Primary Key On Temporary Table?

Apr 30, 2015

Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0

I was doing a code review for another developer and came across this code:

CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)

[Code] ....

This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:

CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT

[code]...

==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.

==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.

(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:

SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Creating And Dropping Global Temporary Table?

Apr 3, 2015

I want to create and drop the global temporary table in same statement.

I am using below command but I am getting below error

Msg 2714, Level 16, State 6, Line 11

There is already an object named '##Staging_Temp' in the database.

if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,

[Code] ....

View 1 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Common Table Expressions With Table Creation

May 20, 2008

I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.

I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?

like:


create table #temp (row_no int identity (1,1),time datetime, action varchar(5))

insert into #temp select * from cte5

View 4 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Is It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?

Mar 10, 2008

Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789

View 2 Replies View Related

Temporary Table Vs. Table Valued Function

Jun 6, 2006

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.

View 4 Replies View Related

TSQL Statement Extracting Data From One Table Through Another Table

Nov 17, 2007

Hi,

I have 2 tables,
MembersTemp and Organisations

I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.

Initiallt I only have the ExecID for the MembersTemp table

MembersType table:
ExecID 3013
OrganisationID 4550

Organisation table:
ID 4550 (PK)
Name "Microboff"

Any ideas??

View 5 Replies View Related







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