Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scottydogg84
Starting Member
5 Posts |
 Posted - 2011-05-09 : 05:11:02
|
| Dear all,I'm fairly new to SQL. I have created a new view (nominal_narratives), which contains three columns (dbo.nominal_narratives.narr, dbo.nominal_narratives.nt_seqnum and dbo.nominal_narratives.nt_accode).Within my database I also have a table (axxia01.dbo.nmnomtrn) which contains the following columns (axxia01.dbo.nmnomtrn.nt_addesc, axxia01.dbo.nmnomtrn.nt_trdesc and axxia01.dbo.nmnomtrn.nt_seqnum).What I have been struggling to do is create a new View which needs to combine the 'narr' column in the nominal_narratives table with the 'nt_trdesc' column in the axxia01.dbo.nmnomtrn table. Both these columns are text and are linked by the 'nt_seqnum' columns in both tables.The problem I have faced is that some transactions have a seqnum in the axxia01.dbo.nmnomtrn table but not in the nominal_narratives View. This is highlighted by the axxia01.dbo.nmnomtrn.nt_addesc column which contains Y (for yes) and N (for no) and this indicates whether or not there is additional text in dbo.nominal_narratives.narr. All transactions will have a seqnum in axxia01.dbo.nmnomtrn.nt_seqnum and text in axxia01.dbo.nmnomtrn.nt_trdesc but many will also have a matching seqnum in dbo.nominal_narratives.nt_seqnum along with text in dbo.nominal_narratives.narr where axxia01.dbo.nmnomtrn.nt_addesc column = 'Y'.When combining the two tables myself I have found that if the axxia01.dbo.nmnomtrn.nt_addesc column = N then the data is excluded all together when joining the axxia01.dbo.nmnomtrn.nt_trdesc and dbo.nominal_narratives.narr columns.In very basic terms (I told you I was new to SQL!) I think I need a statement which does the following:IF axxia01.dbo.nmnomtrn.nt_addesc = 'N' THEN just show axxia01.dbo.nmnomtrn.nt_trdesc, BUT IF axxia01.dbo.nmnomtrn.nt_addesc = 'Y' THEN show RTRIM(axxia01.dbo.nmnomtrn.nt_trdesc) + NEW LINE + dbo.nominal_narratives.narr WHERE axxia01.dbo.nmnomtrn.nt_seqnum = dbo.nominal_narratives.nt_seqnum, AS Description. This new column can be called 'Description'. Also, this data will be placed into Excel (and wrapped) when created. Is it possible to add a 'new line' function between axxia01.dbo.nmnomtrn.nt_trdesc and dbo.nominal_narratives.narr IF axxia01.dbo.nmnomtrn.nt_addesc = 'Y'? When created I am hoping to have a new View which will contain nt_seqnum from axxia01.dbo.nmnomtrn and Description. Hope this makes sense as I have started to confuse myself. Thanking you all in advanced and please feel free to ask any questions. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-05-09 : 13:38:42
|
| Sounds like what you're searching for is a LEFT OUTER JOIN. That way all rows from one table will be included as well as any matching rows from your outer table.Be One with the OptimizerTG |
 |
|
|
|
|
|
Creating a new View and combining Columns