November 21, 2005 at 4:50 pm
How can I add a block of text to the output of a simple query that will be added at the end of the last row every time the query is executed?
November 22, 2005 at 3:47 pm
bump, is that not doable?
November 22, 2005 at 3:53 pm
One way that I can think of is to Use a UNION clause. Use whatever condition is needed in the first and then add UNION with required message and remaining blank fields.
However this could be an issue if there are NOT-NULL fields in the first query (particularly if there is a date field it would be displaed as "1900-01-01 00:00:00.000" is we use '')
Here is what I am saying
(1) create a table for testing and insert data
CREATE TABLE junk(empname VARCHAR(10), empstate CHAR(2))
INSERT INTO junk
SELECT 'Test1','CA' UNION
SELECT 'Test2','IL' UNION
SELECT 'Test3','MN' UNION
SELECT 'Test4','OH' UNION
SELECT 'Test5','CA'
(2) Now run a select with a UNION clause
SELECT * FROM junk
UNION
SELECT 'THIS IS THE LAST LINE OF THE RESULT',''
November 23, 2005 at 8:46 am
Using UNION was exactly what I was thinking for this as AKS suggests, just keep in mind that for a UNION to work, you must have the same number of columns of data and the same datatype.
November 23, 2005 at 11:48 am
As bellis points out, the UNION requires both queries to have the same number of columns with compatible datatypes.
Since you will have an extra column in every record of the resultset anyway, why not add the text to every record? This makes the query simpler. If it suits your needs, try the following query. This selects two fields from the Northwind database table Customers and adds the text as a third column.
USE Northwind
GO
SELECT CustomerID, CompanyName, 'This is the block of text'
FROM Customers
November 23, 2005 at 12:04 pm
Even though the UNION will work for this, I suspect this is for informational purposes only for the end-user and the presentation layer is really the place to add these modifications.
November 28, 2005 at 10:43 am
When you say presentation layer, is there any way to do that with the csv file created from the query or does that have to be done with SQL reporting services, or some third party app like Crystal, etc.
November 28, 2005 at 10:50 am
How are you creating the csv file and what is it being used for? The first thing that came to mind here is possibly add the last line you want using FSO, but this would screw up any import if you again don't have the same number of columns of data that the import process would expect.
I still think that you would be better off adding this last line to the report or whatever you're trying to create.
Can you provide further details of what you're trying to accomplish?
Thanks,
Brian
November 28, 2005 at 11:56 am
I have a non complex select query (select * from TABLE between date range) that pulls data from a table. I save that to a csv file thru Query analyzer in the default csv format. All I'd like to do is have that select query modified to place text at the end of the rows that show up in the analyzer window. The text is a "key" to tell my client how to interpret the data.
November 28, 2005 at 12:09 pm
If you mean you need an additional column, or field, that contains the key at the end of each row, use the following...
SELECT *, 'your text for the key here' AS Key FROM tablename WHERE daterange
I believe this was suggested by CJohnson above.
If you want one additional record that contains the text for the key at the end of the recordset generated by the SQL statement, use the UNION trick.
SELECT * FROM tablename WHERE daterange
UNION ALL
SELECT 'your text for the key here' AS Key, '',''...
Add placeholders for each field in the original select statement, otherwise the UNION will not work.
HTH
November 28, 2005 at 12:15 pm
Thanks to all...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply