Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.
This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!
Hi, In the SQL92 Specifications i read the foloowingf statement... "All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are permitted for updatable tables, subject to constraining Access Rules. The operations of insert, update, and delete are not allowed for read-only tables." But i am concentrating on the below line from the above written lines, "Derived tables are either updatable or read-only." I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...
Hi!I want to return a derived table along with 4 simple tables in a storedprocedure as follows:Input parameter: @FtNum (==Order Number, selects one Order and allassociated data)Table 1: OrdersTable 2: ItemsTable 3: InstancesTable 4: StockDetailsDerived Table: for each Item that requires stock items, 1st columnshould receive the ItemNo (from Items), subsequent columns should receive thedetails from StockDetails via the common key field 'StockCode'.I have so far used a 'Fetch' cursor to find all occurrences of a StockCodewithin the Items table, but have been unable to figure out how to first addthe ItemNo into the temporary table.Code is as follows:... build #tmp_StockDECLARE stock_cursor CURSOR FORSELECT StockCode, ItemNoFROM ItemsWHERE FtNum = @FtNumORDER BY ItemNoOPEN stock_cursorFETCH NEXT FROM stock_cursorINTO @StockCode, @ItemNoWHILE @@FETCH_STATUS = 0BEGININSERT INTO #tmp_Stock-- wish to insert ItemNo = @ItemNo here --SELECT *FROM ControlledStockWHERE StockCode = @StockCodeFETCH NEXT FROM stock_cursorINTO @Stockcode, @ItemNoENDOf course there may be a much simpler way to do this!Your help would be greatly appreciated either way.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
Hello.So the scenario is a little complicated.I am joining two tables.Table 1 is derived; it has one row; it has a column based from newid()Table 2 joins to table 1 and reuses the newid() value from table 1 in table 2's rowsBecause there is only one row in Table 1, the value of newid() REPEATS in Table 2The bug is that the NewId() value from Table1 is REGENERATED with every Table 2 record.I created a blog about this because it takes a code sample to demonstrate:http://jerrytech.blogspot.com/2008/04/sql-2005-tsql-bug-with-newid-in-derived.html
they all seem same to me. Is there any big difference among them.
Also, if I do have the tables avaiable, so now I don't think I have to create any of the temerory tables, since I can use the
WITh mystatement ( ) Select * from mystatmment
I think if I have to use any of the temperory tables , only time when I have to just create some tables to test some values on in it rather then using the real table in the database.
2) And not only that I use the CTE for that but for any sub query whereever apply, i would like to forget about the IN, Exit or = in the correlated queries but would use whereever I can use the "Joins"
create multiple INNER JOIN on derived tables as I have written below or use a #temp table for all derived tables and use them into JOIN. This below query is also very hard to understand what is going on .
Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan
I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error. Output: 1234567890000043321092513 00050020
Select DATA from( select '12345678'+ left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) + cast (CONVERT(int,(INV_AMT*100)) as varchar) + left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+ CAST (MONTH(DATE) as varchar(2)) + left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) + CAST (day(DATE) as varchar(2))+right(cast (year(DATE)
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
Hi, wondering if anyone can help me. I currently have a field that has a date and time in it in the format dd/mm/yyyy hh:mm:ss. Ideally I would like to get rid of the time part of it altogether but for it to still be recognised as a date as opposed to a string. However, I've been told that this is not possible in 2005, is this true? If this is the case, what would be the best way to set the time to 00:00:00 after the date for all records on that field?
Guys, this is what I want to do. How can I do this ?Select employid,Gross=( Select sum (uprtrxam) from Fleet..upr30300 where pyrlrtyp=1),DedofWages=( Select sum (uprtrxam) from Fleet..upr30300 wherepyrltyp=2),Gross-DedofWagesfrom FLEET..UPR00100The problem is the third field (Gross-DedofWages). It says Invalidcolumn. Any ideas ?Thanks, Girish
I am trying to transfer data from SQL Server 2005 table to another SQL Table. In the source table there is a field called Region [nvarchar(max)]. The values for these fields will be like APAC-China, NA-Racine, etc., i.e Region followed by the country name seperated by - symbol. I want the destination tablre with 2 fields Region[nvarchar(max)] and Country [nvarchar(max)]. I am using a Derived Column task to achieve the same.
Can anyone please help me out in doing this data conversion?
Is there a way to group by a derived value? For example, in the query below, is there a way to group the results by the AgeBracket field? I'd like to see adults (0) in one grouping and children (1) in the other.
SELECT AgeBracket = CASE WHEN dbo.GetAge(DOB, GETDATE()) > 18 THEN 0 ELSE 1 END, COUNT(*) FROM tableX
I am doing a task which will get all data based on several base and crosswork tables. I used stored procedure to get the data, but the next step will be using derived column to massaging data, and to load to the destination database. Is there a way to do that since I must generate data on the fly?
Is there a way to group by a derived value? For example, in the query below, is there a way to group the results by the AgeBracket field? I'd like to see adults (0) in one grouping and children (1) in the other.
SELECT AgeBracket = CASE WHEN dbo.GetAge(DOB, GETDATE()) > 18 THEN 0 ELSE 1 END, COUNT(*) FROM tableX GROUP BY ?
I am trying to create a derived column from two separate fields. One is a date field in format of 2008-03-01 and one is a time field. In DTS I use to have something like this:
I am trying to figure out how to do this in SSIS. I know I have to use a dervived column but can't seem to come up with the correct syntax. Any help would be apprecaited. thanks.
i have a column in my table that will store the sum of 3 other column, e.g the data in total_book column will be equal to the sum of the number in the romance, fiction and thriller column. how do i do this?
Hi, I have built a database for a university project, but am having trouble with the SQL syntax for inserting derived data from a calculation into a table. At present my SQL is; INSERT INTO MemberPayment (TotalCharge) VALUES ( [Total] ) SELECT ((MileageHistory.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) + ((MileageHistory.MileageCountry * Vehicle.EmissionsPerGramUrban) * 0.05) AS 'Total' FROM [NeuCar].[dbo].[MileageHistory] JOIN [NeuCar].[dbo].[Vehicle] ON MileageHistory.Registration = Vehicle.Registration JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName WHERE Member.UserName = 'wenger1' AND (MileageHistory.[Date] >= CURRENT_TIMESTAMP - 30); Would anybody be able to point out where I am going wrong? I would very much appreciate any advice, Kind regards, Chima
I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -
SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq FROM questionsstats GROUP BY username ORDER BY score DESC
This works just fine.
However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".
I got an error as follows: Derived table 'A' is not updatable because a column of the derived table is derived or constant. when I tried to run this query: update A set MonthsUnbilled =99999888 FROM (select MonthsUnbilled from dbo.vw_MasterView WHERE (RecordID =8377396)) A This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.
Below is my sql, i am getting error msg "Incorrect sytax near 'Votes'." Can anyone tell me what im doin wrong?
UPDATE #TblExposure SET E.Unity = ( SELECT -- DealId ,DocketId, CASE Participant2 WHEN nullTHEN 'NA' ELSE CASE WHEN Participant2-Vote2 =0 THEN 'Yes' ELSE 'No' END END AS Unanimous1 FROM #tblVoting ) Votes FROM #tblExposure E INNER JOIN Votes ONVotes.Dealid = E.Dealid AND Votes.DocketId =E.DocketId
1. In a if else expression if condition is false how do you keep the value of the source column eg: Name == "" ? "Unknown" : Name Above will change all the non blank values to Name and not the actual value in the Name Column eg. John
2. I have a column (unicode string)that stores date and time (The source is flat file) Is it possible to write expression to select the 1st day of month based on that date, and use this derived column as input to a table with a datetime field.
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus". is there some whay to modify this code so that it will work? commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')