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 |
|
steamngn
Constraint Violating Yak Guru
306 Posts |
 Posted - 2005-06-27 : 22:21:49
|
It just keeps getting better and better... consider the following query:selectcoalesce(ps_tkt_hdr.tkt_no,'')+' '+coalesce(ps_tkt_hdr.bill_nam,'')+' '+coalesce(ps_tkt_hdr.bill_city,'')from ps_tkt_hdr This will return something like:12345 Joe Blow Smalltown NOW,The request has been put in for the data to look like this:12345Joe BlowSmalltown At this time the data is being presented directly from a view. Before I go and build a whole presentation-layer setup I figured I'd ask:Can this query(or any query,for that matter) be set up to return the data with the carriage returns in it?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-06-27 : 22:31:03
|
It depends on what tool you are using to display the data (which is yet another reason you should do this in the presentation layer).  For example:DECLARE @test NVARCHAR(55)SELECT @test = 'a' + CHAR('010') + 'B'SELECT @testThat will show a new line if you use Query Analyzer and run in Text mode. It's ignored though if you run it in Grid mode.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-06-27 : 22:50:22
|
Hey Derrick,I know a presentation layer would be the way to go, but here is the current setup:The app used in accounting (it's always the accountants) has a handy little function that allows a button to be added to any screen. This button can be linked to a table or view. At present the query I posted is called into a 3 column view, each one filtered for 30,60, and 90 days past due, and one of these buttons is configured to call the view so the customer list can be seen. The trolls are having a problem reading the data in linear format(must be the pointy eyes), and have requested this stacked feature. If I build a presentation-layer setup, I can't use the button link feature. Not that this is the end of the world, but I'm sure the help desk pager is going off right now just beacause I'm thinking about adding another step! There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-06-27 : 22:57:14
|
why isn't it:DECLARE @test NVARCHAR(55)SELECT @test = 'a' + CHAR(10) + 'B'SELECT @testCorey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-06-27 : 23:05:17
|
Hey Corey,Well, it could even be:DECLARE @test NVARCHAR(55)SELECT @test = 'a' + CHAR(13) + 'B'SELECT @test But the problem will still remain. The data in text mode would be ok, but because we need a view to hold the data this way this doesn't work. I've been farting around with this for a while now, and can't seem to come up with anything that will do the job directly in SQL (damn!) ...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-06-27 : 23:09:37
|
| Well, technically, it couldn't be either on "any" system. And, if you wanted for the "results to text" option, you could use, 10, 13, 010, or 013....but, hey who cares, right? MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
'Stacking' column data