Creating a new View - Combining columns

Question

Post reply

Creating a new View - Combining columns

matt_scott1984

SSC-Addicted

Points: 469
More actions
May 9, 2011 at 3:18 am

#235842

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

good job explaining what you wanted;

try this SQL below adn see if this gets the data you were looking for:

SELECT

dbo.nominal_narratives.narr,

dbo.nominal_narratives.nt_seqnum,

dbo.nominal_narratives.nt_accode,

axxia01.dbo.nmnomtrn.nt_addesc,

axxia01.dbo.nmnomtrn.nt_trdesc,

CASE

WHEN axxia01.dbo.nmnomtrn.nt_addesc = 'N'

THEN axxia01.dbo.nmnomtrn.nt_trdesc

WHEN axxia01.dbo.nmnomtrn.nt_addesc = 'Y'

THEN RTRIM(axxia01.dbo.nmnomtrn.nt_trdesc) + CHAR(13) + CHAR(10) + dbo.nominal_narratives.narr

END AS Description

FROM dbo.nominal_narratives

LEFT OUTER JOIN axxia01.dbo.nmnomtrn

ON dbo.nominal_narratives.nt_seqnum = axxia01.dbo.nmnomtrn.nt_seqnum

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matt_scott1984 SSC-Addicted Points: 469 More actions · Answer 2

Thanks Lowell.

I'm currently getting an error message with this statement and can't place my finger on why:

"Error in SELECT clause: expression near ','.

Missing FROM clause.

Unable to parse query text."

Followed by:

"SQL Execution Error.

Executed SQL statement: SELECT

dbo.nominal_narratives.narr,

dbo.nominal_narratives.nt_seqnum,

dbo.nominal_narratives.nt_accode,

axxia01.dbo.nmnomtrn.nt_addesc,

axxia01.dbo.nmnomtrn.nt_trdesc,

CASE

WHEN axxia01.dbo.nmnomtrn.nt_addesc = 'N'

TH...

Error Source: .NetSqlClient Data Provider

Error Message: Incorrect syntax near ','."

Do you have any ideas?

Thanks again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3