multiple results in one result, application limitations

I am working with a legal software called Case Aware. You can do limited sql searches and I have had luck in getting 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 tem开发者_如何转开发plate. (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.

IntegrationGirly

Addtl FYI:

I currently have this kludge: Pulling each value individually from the database even if it is null and putting each value into a table in the document. (30 separate searches) It "works" but takes much longer for the document to generate and it also leaves a great deal of empty space. Some case have 3 values, most have 5-10 but we have up to 30 areas for rate because once in a blue moon we need them. This makes the template look horribly junky but that doesn't affect the lawyers who generate the docs since they don't see it, but every time they generate the table they have to take out all the empty columns. With the number of docs we do each day, 1) this becomes time consuming and 2) This assumes attorneys and paralegals know how to take rows out of tables in word.

---

First, my condolences for having to work with such terrible software.

Second, here's a possible solution (this is assuming SQL Server):

1) Execute a SELECT COUNT(*) FROM case_fin_info WHERE fin_info_id = 7 AND rate <> 0. Store the result (number of rows) in your client application.

2) In your client app, do a for (i = 0; i < count; i++) loop. During each iteration, perform the query

WITH OrderedRates AS
(
   SELECT Rate, ROW_NUMBER() OVER (ORDER BY <table primary key> ASC) AS 'RowNum'
   FROM case_fin_info WHERE fin_info_id = 7 AND rate <> 0
)
SELECT Rate FROM OrderedRates WHERE RowNum = <count>

Replacing the stuff in <> as appropriate. Essentially you get the row count in your client app, then get one row at a time. It's inefficient as hell, but if you only have 15 rows it shouldn't be too bad.

---

I had a similar query to implement in my application. This should work.

DECLARE @Rate VARCHAR(8000) 
SELECT @Rate = COALESCE(@Rate + ', ', '') + rate 
From case_fin_info where fin_info_id = 7    and rate!=0;

---

Here's a single query that will return the one result in a single column. It assumes your manual return is CR + LF. And, you would need to expand it to handle all 15 rates.

SELECT max(Rate1) + CHAR(13) + CHAR(10)
    + max(Rate2) + CHAR(13) + CHAR(10)
    + max(Rate3) + CHAR(13) + CHAR(10)
    + max(Rate4) + CHAR(13) + CHAR(10)
    + max(Rate5) + CHAR(13) + CHAR(10)
FROM (
    SELECT CASE RateID WHEN 1 THEN CAST(rate as varchar) END AS Rate1,
        CASE RateID WHEN 2 THEN CAST(rate as varchar) END AS Rate2,
        CASE RateID WHEN 3 THEN CAST(rate as varchar) END AS Rate3,
        CASE RateID WHEN 4 THEN CAST(rate as varchar) END AS Rate4,
        CASE RateID WHEN 5 THEN CAST(rate as varchar) END AS Rate5
    FROM
        (
        select RateID, rate From case_fin_info where fin_info_id = 7 and rate!=0
        ) as r
    ) as Rates