Combing multiple results into one result, T-SQL

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combing multiple results into one result, T-SQL

Author

Topic

KristiW3511
Starting Member

3 Posts

Posted - 2011-03-17 : 14:05:01

Hi. I am working with a legal software called Case Aware. You can do limited sql searches and it is easy to get Case Aware to pull a specific value from the database. My problem is that I need to create a sql search that returns multiple values but the Case Aware software will only accept one result as an answer. If my query produces a list, it will only recognize the top value. This is a limitation of the software I cannot get around.

My very basic search is:

select rate
From case_fin_info
where fin_info_id = 7 and rate!=0

This should produce a list of 3-15 rates, which does when the search is run straight from the database. However, when run through Case Aware, only the first rate in the table will pull. I need to pull the values through Case Aware because with Case Aware I can automatically insert the results into a template. (Where I work generates hundreds if not thousands a day so doing it manually is a B$@#%!)

I need to find a way to pull all the values from the search into one value. I cannot use XML (Case Aware will give an error) and I cannot create a temporary table. (Again, a Case Aware limitation) If possible, I also need to insert a manual return between each value so they are separated in the document I am pulling this information into.

Case Aware does not have any user manual and you pay for support (We do have it) but I have my doubts on their abilities. I have been able to easily create queries that they have told me in the past are impossible. I am hoping this is one of those times.

Thanks a million!

IntegrationGirly

Thanks a million!

Kristi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 14:32:24

See if this works:

select replace((select rate
From case_fin_info
where fin_info_id = 7 and rate!=0 FOR XML PATH('')), '#x0D;'+char(10), '
')

KristiW3511
Starting Member

3 Posts

Posted - 2011-03-17 : 15:01:52

I can't use XML. Case Aware will give an error.

Thanks a million!

Kristi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 15:05:55

Did you try it? What kind of error does it give? That query won't return XML results, so that might work around their limits.

The only reason that wouldn't work is if the database is running on SQL Server 2000 or earlier, or the database is in 8.0 compatibility.

KristiW3511
Starting Member

3 Posts

Posted - 2011-03-17 : 16:31:59

Yes, I tried it. I don't get error messages. :-( But when I generate a document, it won't pull the information. *Grumble Grumble* I'm beginning to think that the only things I can do are select, from, where, when, then, case and joins.
I currently have a very ugly kludge which works-ish. It uses their tags vs. a query. It will give me a manual return between each value (because I tell it to- I have to) because I can't determine if there is a value of if it is null in this method.

This is a bit problematic because it gives a bunch of empty lines in a legal document. It can be edited manually after generation, but when you do the volume that we do, it is a **big** time waster and I hear about it from the paralegals/attorneys.

select CASE WHEN `__FININFO_RATE_Adjustable Rate_Rate Change 30__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 29__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 28__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 27__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 26__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 25__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 24__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 23__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 22__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 21__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 20__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 19__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 18__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 17__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 16__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 15__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 14__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 13__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 12__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 11__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 10__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 9__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 8__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 7__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 6__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 5__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 4__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 3__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 2__`!=`0` `__FININFO_RATE_Adjustable Rate_Rate Change 1__`!=`0` `__FININFO_Rate_Adjustable Rate_Current Rate__`!=`0` THEN ("__FININFO_RATE_Adjustable Rate_Rate Change 1__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 2__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 3__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 4__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 5__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 6__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 7__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 8__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 9__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 10__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 11__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 12__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 13__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 14__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 15__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 16__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 17__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 18__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 19__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 20__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 21__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 22__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 23__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 24__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 25__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 26__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 27__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 28__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 29__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Rate Change 30__"`_CRLF_` "__FININFO_RATE_Adjustable Rate_Current Rate__") END

Thanks a million!

Kristi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 16:44:50

Does this use SQL Server to store the data? If not, you'll have to try another site, SQLTeam is dedicated to Microsoft SQL Server.

You can try http://dbforums.com/ for other database platform questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources