Derived Columns In One To Many Relationships
Jul 20, 2005
I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.
What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:
Author Title
Joe Writer Book 1, Book 2, Book 3,
Rather than having it appear as 3 records:
Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3
Could someone help me with the SQL involved in this?
Thanks for the help.
Cheers,
Mike
View 4 Replies
ADVERTISEMENT
Oct 13, 2006
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
View 5 Replies
View Related
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
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
Sep 19, 2006
Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
View 15 Replies
View Related
Jan 26, 2007
Hello,
Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?
Any help is welcome ;-)
Cheers,
Bertrand
View 5 Replies
View Related
Oct 26, 2007
All,
I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)
So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.
Anyway, I'm curious if anyone else has seen this?
Thanks.
View 10 Replies
View Related
Aug 7, 2007
I'm sending a lot of columns through my derived column transform, checking for empty strings from a flat file - I was wondering is there a way that I could "script out" all the transforms instead of enduring this click hell that I'm stuck in inside the derived column transformation editor. I've got probably 100+ columns to configure with the following sort of transform....
Replace Col1
TRIM(Col1) == "" ? NULL(DT_WSTR,2) : Col1
Basically - if the string is empty, then throw Null in the data stream. I'm about a third the way through but it would really be nice if there was a quicker way. Even with the most efficient copying & pasting & keyboard shortcuts, it's still painful.
View 1 Replies
View Related
Feb 8, 2008
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
View 9 Replies
View Related
Apr 27, 2015
I have a lot of different data flows that need "Derived Column". There are maybe only 5 different such "Derived Column" but they appear many times. Is there a way to eliminate all that double work? It should be something that does not take me more time to do than just duplicating all the "Derived Columns".
View 2 Replies
View Related
Feb 15, 2008
Hi,
In my working existing package I am adding a derived column as date and datatype as DT_DBTIMESTAMP and my destination data type is datetime. when I try to union these records through an "Union ALL" component its not allowing me to map this column and throws error as "outputcolumnlineageid " and defines the error as metadata for source and destination are not matching.
I checked the metadata and they are DT_DBTIMESTAMP, Am I missing something? please advice.
View 4 Replies
View Related
Jan 24, 2008
Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.
Example:
On an ETL of Products I want a new derived column that uses two other columns.
I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.
So I make that expression = @[User::Variable]
and stuff into @variable ( a string param) : ProductID + " - " + ProductName
My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names
I've tried with/without brackets, quotes and braces but no change.
Any way I can get the pieces in that variable expression to evaluate as column names?
View 6 Replies
View Related
Nov 25, 2015
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
View 3 Replies
View Related
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
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
May 29, 2007
Ok, so I open up a book on beginning sql and it discusses relationships.There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.Can someone explain this further please?Thanks in advance.
View 3 Replies
View Related
Aug 8, 2007
How do you show many to many relationships in ASP.NET with datasources?
I can retrieve the information from the database easily but I can't manage to save that information into a variable so that i can then call for only information in the M2M table related to the ID Key I want. For example..1 <asp:Label ID="lblTestLabel" Text='<%# DataBinder.Eval(Container.DataItem, "title_id") %>' Runat="server" />
2
3 <script language="VB" runat="server">
4 sub Page_Load(sender as Object, e as EventArgs)
5 Dim lblTitleID As Label = Repeater1.FindControl("lblTestLabel")
6 Response.Write(lblTitleID.text)
7 End Sub
8 </script>
When I try to run this script it dosn't work and I get the error:
System.NullReferenceException: Object reference not set to an instance of an object.
For line 6 of the code above. Is there a way to just retrieve information from a datasource without storing information into a label first?
PS: I use sqldatasource tags to retrieve information like:1 <asp:SqlDataSource ID="DataSet1" runat="server"
2 ConnectionString="Driver={MySQL ODBC 3.51 Driver};server=localhost;database=;user=;pwd=;option=3;"
3 ProviderName="System.Data.Odbc"
4 SelectCommand="Select * from titles order by title_id">
5 </asp:SqlDataSource>
View 2 Replies
View Related
Aug 10, 2007
Can someone help me sort something out. Suppose we have two tables in a database.One is named Person, and one is named Birthday. Is this a many to many relationshipor a 1 to many relationship?
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life. birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.
View 6 Replies
View Related
Feb 2, 2004
Is there any easy way of getting all the relationships for the tables in aSQL server db? I've looked through Google and the Books online but I'm barely scratching the surface on the systables. I know I can go to each individual table and get the dependencies or even print out the diagram through Enterprise Manager but I really don't want to have to type out all the primary/foreign keys for 100+ tables. Any help on getting this data either through a 3rd party software or querying the systables would be great.
THT
DMW
View 5 Replies
View Related
Mar 21, 2001
We have a table with approximately 150 colums and 2/3 of them are bit datatypes. We do not expect much activity on this table. Does it make sense to separate it into a few tables instead of 1 with one-to-one relationships? Will the join(s) significantly slow down our web application? Thank you
View 2 Replies
View Related
Aug 6, 2004
My hosting servers have "myLittleAdmin" installed. I am new to ms sql I have some experience with Access but I have never set up Relationships inside the database server. I have always just relied on my application logic to call the relations. What is the benefit to setting up relationships in sql, if any?
View 4 Replies
View Related
Nov 11, 2004
Is there a tool in Enterprise manager to list out the one to many & many relationships with a database?????:?:
View 4 Replies
View Related
Feb 1, 2007
Hey, I have SQL Sever Enterpise Manager. I am new at this company and I need to know the relationships between ALL the tables. Is there anywhere I can go to view all the primary keys and the relationships?
View 3 Replies
View Related
Jun 1, 2007
I am viewing tables in the ‘object explorer’- I can see the tables. If I open it up I can see columns, keys, constraints etc…
So I can see they key… can I tell which table/field the foreign key is connected to?
thanks
View 10 Replies
View Related
Oct 17, 2007
Hello,
I have 3 tables. Posts, PostsTags and Tags.
This is a many to many relationship.
So PostsTags has the following columns:
PostId (PK) and TagId (PK)
I always did that way in SQL 2005.
In some examples I see PostsTags have 3 columns:
PostTagId(PK), PostId (FK), TagId (FK)
Should I use it this way, instead?
Thanks,
Miguel
View 8 Replies
View Related
Jul 13, 2006
Is it possible to have 2 different databases and create relationships inbetween the two? I am thinking about way down the road and the size of the databases and I believe that I need to split my data into multiple databases to keep from running into the size limitations.
Davids Learning
View 1 Replies
View Related
May 29, 2007
Ok, so I open up a book on beginning sql and it discusses relationships.
There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.
http://foo.gearsector.com/foo/instr_scrns/oneToMany.gif
Can someone explain this further please?
Thanks in advance.
View 5 Replies
View Related
Apr 1, 2008
How can I use with relationShips in SQl?
Tamir BLoch .
View 1 Replies
View Related
Apr 3, 2007
Hello!
I have a really small database in terms of number of objects, but very big in terms of data (possibly millions of records). I always try to design my databases to be in the 3rd normal form (at least I hope they are!), but sometimes (for databases consisting of just a few tables) maintaining M:N relationships looks like an over-bloated approach to me... Let's see an actual example. I have a table like this:
Code SnippetCreate table [Airfares]
(
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
[Price] Decimal(9,2) Default 0 NOT NULL,
[PriceChange] Decimal(9,2) Default 0 NOT NULL,
[Wrong] Bit Default 0 NOT NULL,
Primary Key ([OriginCity],[DestCity],[FareDate])
)
go
Now, at first there also was an Airline nvarchar(50) field, which stored the airline name. But now the client requested to store fares for a few airlines if it's the same price and city pair. This way we can have 3 airlines offering the same price on the same itinerary for example. So I added a dictionary table called Airlines:
Code SnippetCreate table [Airlines]
(
[AirlineCode] Char(2) NOT NULL,
[Name] Nvarchar(50) NOT NULL,
Primary Key ([AirlineCode])
)
go
Now, there are two things I can do. We are obviously dealing with the M:N relationship type here. I can either add another nvarchar column in the first table, like Airlines nvarchar(255) and store something like 'AA,AB,AC' there - those would be airline codes. OR I could do it the proper way and create a cross-reference table which will connect both Airfares and Airlines tables (this will store as many records as there are airlines for that particular itinerary, offerring the same price):
Code SnippetCreate table [AirfareAirlines]
(
[AirlineCode] Char(2) NOT NULL,
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
Primary Key ([AirlineCode],[OriginCity],[DestCity],[FareDate])
)
go
But this is starting to look overly complicated, isn't it? The pros of this are I'm going to get real relationships, referential integrity, etc., but cons are more difficult code to query all this. What would you do? Do we really need referential integrity everywhere? Are there times when we're better not using relationships? I have seen many programmers skip referential integrity before, which always made me think they are missing something important in a Relational Database Management System.
View 13 Replies
View Related
Feb 13, 2008
Hi
How I can get to see the relationships, like in access, in sql 2005?
Thank,
K
View 1 Replies
View Related
Mar 16, 2008
Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies
View Related
Feb 15, 2004
I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:
*******************************************************
I've got a structure which looks vaguely like this:
[ANCESTORS]
Grandparent
Parent
Child
If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:
SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Grandparent.Name = 'Cybill'
This would return all of the children of 'Cybill' and their children. Now, if I use the following query:
SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Child.Name = 'Jean'
This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).
Likewise, if I enter:
SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Parent.Name = 'Ron'
Then I would get Ron's parents and also his children.
*******************************************************
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
Can anybody see my dilemma here?
Mark-up ASP.net posts here
MarkItUp.com
View 10 Replies
View Related
Nov 27, 2005
Hi
I am using the Web Matrix Project to write a .NET
application. I have set up a database using MSDE which I
accessed through the Web Matrix Project to start with. I have now
found the Web Data Administrator which I use to backup my database and
help to transfer it to the Server where I hope to run this application
(if I ever get it finished!).
I need to set up some relationships between database tables but cannot
see a way to do this. SQL Server Books on Line referes to
Enterprise Manager which I believe ships with the full MS SQL Server
package (and costs a package). I am doing this on a
shoe-string. Any ideas on how to do it?
Thanks in advance.
Mike
View 2 Replies
View Related
