Transact SQL :: 2012 - Using 1 CTE Instead Of 5 CTEs?
Jul 29, 2015
I am currently using 5 separate CTEs and I am wondering if I can use only 1 cte. What I am listing below is what 2 ctes looks like. Thus can you tell me if I can use the same cte that is listed in the sql below? If so, how to change the sql listed below to use only 1 cte?
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo2
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as ( select 0 [iterations], 1e+0 [computed] union all select iterations+1,dbo.f(computed) from where rCTE where iterations < 30 ) select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
I am trying to use CTEs so as to be able to reuse the results for subsequent queries. But I am having trouble with the syntaxes for CTEs and could do with some help here
Basically, this is how my final query would look like.
Code SnippetWITH T1 AS (SELECT id, parent_id, end_desc_id FROM GO WHERE (parent_id = - 1)) WITH T2 AS (SELECT g.id, g.parent_id, g.end_desc_id FROM GO AS g, T1 AS t WHERE g.parent_id = t.id) SELECT G.* FROM GO G, T2 as T WHERE G.id>=T.id AND G.id<=T.end_desc_id ORDER BY G.id, T.id
GO is defined as (id, parent_id, end_desc_id, path, value)
I faced this issue regarding the SQL Server 2005 CTEs
Hope to get an explanation here is the code,
Code Snippetwith MyCTE(i) as ( select i = 1 union all select i = i + 1 from MyCTE where i < 10 ) select i from MyCTE order by i
this will display the numbers from 10 rows containing values from 1 to 10, although the where clause is so clear i<10 if we replace it with i<=10, it will display 11 rows from 1 to 11
So as we know the recursion process here is limited by default to 100 (we can change it using option (maxrecursion n) ),this can yield that it is not limited to 100 but 101 even u changed the limit to 1000 it will not be 1000 but 1001, but this may be correct as the recursion counting is began after union all so it should be 100 and display 101 in the result set but my question is clear above (the max value displayed and the where clause)
In a t-sql 2012, I want to declare variables with multiple values and I am having problems since sql server thinks I am working with numbers when I am really working with character and bit values. Here is a copy of the sql that I am trying to use:
DECLARE @Account varchar(100) DECLARE @Processed bit set @Account = '58100,98326,09897' set @Processed ='0,1'
Thus would you show me what I can do so that the sql server knows that I want the values in the set states above to be varchar or character value for @Account and bit value for @Processed?
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:
CREATE TABLE [dbo].[Locker]( [lockerID] [int] IDENTITY(1,1) NOT NULL, [schoolID] [int] NOT NULL, [number] [varchar](10) NOT NULL, [lockID] [int] NULL CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[Code] ....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999 insert into test.dbo.Locker ( [schoolID], [number]) select distinct LKR.schoolID, A.lockerNumber FROM [InputTable] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID) order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
I have question about the size of foreign key’s in sql-server 2012. If I in one table had a foreign key of the “INT” type. Do it still cost 4 bytes of storage?
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer Version 11.0.2100.60 Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1Rel Microsoft .NET Framework Version 4.5.51641 SP1Rel
I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment) 2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect. 3. I can create a new virgin user on the PC and that login experiences the same problem. 4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
We have SCCM 2012 primary site and Remote SQL 2012 server. Due to hardening and password reset we are facing reporting issue.
while we Open the SRS report in SQL server and try to edit the Report (Report Builder) we are getting following error due to which we are unable to configure Reporting Service point in the SCCM 2012 server. We created the New Reporting server database still we are getting the below error.
I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause. str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN" Thanks
I am trying to insert to our DB. Here is my insert statement. str = "INSERT INTO SURVEY (q3) VALUES (" & q3 & ") WHERE iID="&iID Here is my debug: DEBUG: INSERT INTO SURVEY (q3) VALUES (1) WHERE iID=23 And here is my error: Incorrect syntax near the keyword 'WHERE'. Can someone please tell me what I am doing wrong? Thanks in advance.
I am new to SQL and need some help. Would anyone be able to tell how to write a statmement, that would bring back several columns with specific amounts of spaces in it? I don't want to change the sizes of the columns forever, just for this one query.
I START A NEW JOB WITH A COMPANY PROVIDES IT SUPPORT TO CALL CENTERS. WE USE VB6 CONNECTING VIA ADO TO SQL SERVER 2000 STORED PROCEDURES. OUR ONLINE REPORTING TOOL USES CRYSTAL REPORTS & ASP (LITTLE VB6). CAN ANYONE RECOMMEND GOOD BOOKS AND WEBSITES TO PREPARE ME FOR MY NEW POSITION WITH THIS COMPANY?
ALSO, HOW DOES ONE GO ABOUT BUYING RELEVANT CODE IN VB6, TRANSACT SQL, ASP?
WITH CALL CENTERS (OUR CLIENTS) MOVING OVERSEAS-OUTSOURCING, WHAT ABOUT ENRICHING OUR POTENTIAL CLIENT BASE...FOR EXAMPLE, RECOMMENDING MARKETING EFFORTS TO 'NAILED DOWN' CLIENTS LIKE PHARMACEUTICAL COMPANIES WHERE THE GOVERNMENT DEMANDS SUCH WORK BE DONE IN AMERICA. OR EXPANDING OUR ROLE TO ALSO DO...?
ANY IDEAS TO ABOVE THREE ISSUES PLEASE-PLEASE EMAIL mikelynn@comcast.net
Hi: Can any one please tell me the difference b/w TSQL and simple SQL. Also there is a a TSQL use in my VB6 Program, how to i find that SQL in SQL Server? cnADOSQL.Execute "DBCC CHECKIDENT ('tbl_distlist_balrange', RESEED, 0)"
I am trying to use LIKE in a statement but I can't seem to pull all the rows for Age of Empire. I have at least 50 rows with the word Age of Empire but I only get two rows. What is the best way to use LIKE in the statement below so that it pulls all rows with the words Age of Empire?
Select * from dbo.proddesc where codeabbreviation Like '%Age of empire%'
Hi Im reading about active and temporal databases. I have a quesiton though. In which category does SQL server falls into? Also What is the difference between Transact SQL and TSQL2?
I am trying to tally up information from two tables, but I am runningin to trouble and I can't seem to figure this out. I have aCreditsTable and a DebitsTable and I am trying to create a querry thatsums up the info from each per UserID and returns TotalCredits,TotalDebits, and a Ballance.CreditsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 192 | 2 | True || 207 | 1 | True || 207 | 1 | True || 207 | 2 | True || 212 | 3 | True |DebitsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 207 | 1 | True || 207 | 1 | True |***This is the Function I have tried, but it doesn't return the correctresultsALTER FUNCTION [dbo].[BallanceTotals]()RETURNS TABLEASRETURN(SELECT DISTINCTdbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,SUM(dbo.DebitsTable.Ammount) AS TotalDebits,SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS BallanceFROMdbo.CreditsTable FULL OUTER JOINdbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserIDWHERE(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)GROUP BYdbo.CreditsTable.UserID)*** This is what it returns, it is not adding things up correctly (itlooks like it is adding NULL values as 1 instead of 0 or something)BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 2 | 1 || 207 | 4 | 3 | 1 || 212 | 3 | | |*** This is what I want it to return!BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 1 | 2 || 207 | 4 | 2 | 2 || 212 | 3 | 0 | 3 |I would really appreciate some help in getting this to work correctly!-Daniel
Why is my where skewing data? This 1st statement shows everything perfectly, but this go around Ineed to add one more stipulation and adding in the where returns 0 results. But if I run a simple select with the where added, I get the intended result set.
--works with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable) delete from cte where rn > 1
--no work with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable where timeadded > 0) delete from cte where rn > 1
Below is my SQL code and after that output i get. For result i would like to get SUM of column Expr1 (which is 60.668,66) so that means only 1 row not 5. I know it's not that hard but i've been searching around and trying but no luck...
SELECT INVENTTABLE_1.ITEMID AS MA, INVENTDIM_1.INVENTLOCATIONID, SUM(INVENTSUM_1.PHYSICALINVENT) AS Expr1 FROM dbo.INVENTDIM AS INVENTDIM_1 INNER JOIN dbo.INVENTSUM AS INVENTSUM_1 ON INVENTDIM_1.INVENTDIMID = INVENTSUM_1.INVENTDIMID INNER JOIN dbo.ECORESPRODUCTTRANSLATION AS ECORESPRODUCTTRANSLATION_1 INNER JOIN dbo.INVENTTABLE AS INVENTTABLE_1 ON ECORESPRODUCTTRANSLATION_1.PRODUCT = INVENTTABLE_1.PRODUCT ON
I have 3 tables in SQL 2005. I have dumbed them down significantly, to illustrate my question.
1) Item Table - Contains a list of 2 items, "Coca-Cola" and "Pepsi-Cola". 2) Market Share - Contains the 'market share' information for the items in "Item Table". 1 to 1 relationship. 3) Retail Price - Contains the 'average retail' price data for the items in "Item Table". 1 to 1 relationship.
All three tables are linked by "iItemID".
I am trying to build a simply query that generates the following results:
Where the first two values for Market Share are repeated again for row 3 and 4.
How do I syntax my SQL statement to return a single data set in the following fashion? ---------------------------------------------------------- Item Name Market Share Retail Price ----------------------------------------------------------- Coca-Cola mm.mm% $xx.xx Pepsi-Cola mm.mm% $xx.xx
I know this is simple for someone who write queries all the time. But this is driving me crazy. I have tried various JOINS (LEFT OUTER, RIGHT, etc.), but I am missing something.
I need to return a computed value and have no idea how to do it.
I have a table of dates: Column DataType RowID int Person nvarchar(20) Percent real Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult John D - 2001 - .58 John D - 2002 - .52 John D - 2003 - .50 Jane D - 2001 - .58 Jane D - 2002 - .52 Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Hi! I need help! We have 98% transact log full. After run DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log. How can we resolve problem.
