Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    2

    Unanswered: Creating a new View combing columns

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE VIEW NewView AS
    SELECT narr, 
    	nt_addesc,
    	CASE WHEN NM.nt_addesc = 'N' THEN nt_trdesc
    		WHEN NM.nt_addesc = 'Y' THEN RTRIM(NM.nt_trdesc) + CHAR(13) + CHAR(10) + V.narr
    		ELSE NULL
    	END AS Description,
    	RTRIM(nt_trdesc) + COALESCE(CHAR(13) + CHAR(10) + V.narr, '') as Description2,
    	nt_seqnum
    FROM axxia01.dbo.nmnomtrn as NM
    	LEFT OUTER JOIN nominal_narratives as V ON
    		NM.nt_seqnum = V.nt_seqnum
    I didn't know if nt_addesc can have a value other than 'Y' or 'N'.
    I have provided two ways to generate Description.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2011
    Posts
    2
    Thanks ever so much Wim.

    This worked very well except for one minor error.

    This statement appears to be inputing two new lines so when i wrap the text in Excel I get nt_trdesc on a separate line as I requested but there is a small box after narr indicating two new lines.

    Any ideas?

    Thanks again.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Play around with the query by including only CHAR(10) or CHAR(13) (and not both) and see how Excel reacts on it.

    *nix expects linebreaks in the 'LF' (CHAR(13)) format, while Windows expects them in the 'CRLF' format. So it surprises me a bit that Excel has problems with it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    *nix does expect LF, but that is actually Char(10).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •