Derived Tables

Dec 12, 2006

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

View 16 Replies


ADVERTISEMENT

Derived Tables

Feb 2, 2004

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

View 1 Replies View Related

Scope In Derived Tables

Jun 29, 2007

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 Related

Derived Tables Are UPDATABLE Or NOT ????

Sep 28, 2007

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...





Code Block

Update (select * from test1) AS de SET id=0
????

View 10 Replies View Related

Derived Tables From Multiple Resultsets

Oct 19, 2005

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 Related

I Think I Found A BUG With Either Newid() Or Derived Tables

Apr 3, 2008

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

Have a nice day;
Jerry

View 2 Replies View Related

Temp, Variable, Derived Tables

Feb 26, 2008

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

View 1 Replies View Related

Create Multiple INNER JOIN On Derived Tables

Mar 10, 2014

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]....

View 1 Replies View Related

Searching A List Of Tables, Derived From Another Table

Sep 21, 2005

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 Related

Paging Records On SQL Server Using Derived Tables

Jan 24, 2007

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 1 Replies View Related

Paging Records On SQL Server Using Derived Tables : More Question

Jan 25, 2007

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...

View 8 Replies View Related

ORDER BY Clause Is Invalid In Views / Inline Functions / Derived Tables / Subqueries

Sep 25, 2013

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)

[code]....

View 6 Replies View Related

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

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 Related

Help With Derived Table

Mar 10, 2006

I 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

View 2 Replies View Related

Derived Column

May 6, 2008

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 Related

Derived Fields

Jul 23, 2005

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

View 3 Replies View Related

Derived Column

Jan 30, 2007

I 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

View 20 Replies View Related

Derived Column

Dec 3, 2007

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


View 4 Replies View Related

Group By Derived Value?

Dec 11, 2007

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!

View 1 Replies View Related

Derived Column

Nov 2, 2007

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

View 13 Replies View Related

Derived Column Help

Jul 16, 2007

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

View 13 Replies View Related

Group By A Derived Value?

Dec 11, 2007


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!

View 3 Replies View Related

Derived Column

Mar 31, 2008



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

View 4 Replies View Related

Derived Attribute

Oct 11, 2007



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?

View 1 Replies View Related

Derived Transformation

Oct 11, 2007

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?

View 1 Replies View Related

Inserting A Derived Value Into A Table

Feb 28, 2008

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

View 2 Replies View Related

Derived Columns In The WHERE Clause

Jul 31, 2001

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

View 2 Replies View Related

Conditional On A Derived Column

Jan 27, 2006

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.

View 2 Replies View Related

Derived Table Not Updatable

Oct 6, 2004

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!

View 4 Replies View Related

Conditionals On Derived Columns

May 16, 2007

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.

View 4 Replies View Related

Updating Using Derived Table Err:

May 12, 2008

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

View 1 Replies View Related

SSIS Derived Column

Mar 6, 2013

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.

View 1 Replies View Related

Problem With Greater Than Derived Value

Jun 1, 2008

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')

View 6 Replies View Related







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