OK....I know how to write a query to return for example :
All the people that ordered X and Y
but how do I write one for:
All the people that ordered X but not Y?
Thanks,
Trey
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.
I presently have the following code
SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,
ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,
ProSolution.dbo.Offering.Code
FROM ProSolution.dbo.StudentDetail INNER JOIN
ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN
ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID
WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND
(ProSolution.dbo.Enrolment.CompletionStatusID = '1')
The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.
567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS
Can anyone give us any guidance please?
Thanks
Chip
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!
View 1 Replies View RelatedHi,
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...
Code Block
Update (select * from test1) AS de SET id=0
????
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
View 2 Replies View RelatedHello.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
Have a nice day;
Jerry
Two point to discuss
1) Temp table, variable table and derived tables,
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"
I would like other point of view on that.
Thanks
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 .
CREATE TABLE #Temp
(
NumPlayers INT,
ModuleID INT,
ClientId INT,
ASF_Version VARCHAR(10),
ASF_VersionHead INT
[code]....
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
View 6 Replies View RelatedI 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...
View 1 Replies View RelatedI 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...
View 8 Replies View RelatedThe 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)
[code]....
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.
View 4 Replies View RelatedI have 3 tables TableA , TableB and TableC
I have to update tableA with the value of TableB by checking A.Field=B.Field
and TABLEB as condition that B.Field should exists in tableC
Update A Set A.Field=B.Field From tableA A
(select B.Field From TableB B where B.Field=A.Field and B.Field in (select C.Field
From tableC C))
How to do it correctly.
Thanks in advance
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?
View 1 Replies View RelatedGuys, 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
View 3 Replies View RelatedI have two columns made up of 4 digits numbers eg col1 1234, col2 9876
I want to create a derived column so i get a eight digit column, eg col3 12349876
I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression
Thanks
Robbie
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?
Thanks
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
Thanks!
Hi,
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?
Thanks,
Megan
Hi Guys,
How can i put in a derived column the value of 3 columns? I've tried these:
[MyId]+[Paper1]
"[MyId]+[Paper1]"
but no luck. How can i put it right?
Thanks
Gemma
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 ?
Thanks!
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:
DTSDestination("TransactionDate") = cdate(DTSSource("DSRHDATI")) & " " & timevalue(DTSSource("DSRHTIME"))
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.
Stacy
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?
I would like to creat a new column called Load_Dt and hard-coded as 06/30/2006 ( date data type) in derived transformation... how can 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
Hi Folks,
Is there any way to add a derived column into the where clause.
Example:
Select Name, Date, Procedure#,
(Case When Procedure# in ('1','2','3') then 'Y' else 'N') AS Class
From Procs
Where Class = 'Y'
Thanks,
Ray
Hi everyone,
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 -
score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)
The SQL query that i use to get this info is -
Code:
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'.".
Surely there must be a simple way to do this?
Thanks.
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.
Thanks in advance!
Hi,
Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:
---------------------------------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END
FROM
ResidentData B
WHERE
(AgeCalc >= 18)
---------------------------------
How do I do conditionals on the "AgeCalc" derived column?
Thanks.
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
Two questions regarding Derived Columns in SSIS
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')