Can you use formatting in output?
-
Another query!
Im using a really awful and basic reporting tool which uses sql (only t-sql, no procedural stuff). I want it to display some text dependant on what kind of report it is. So i was thinking of doing something like this..
SELECT CASE type
WHEN 'cinema' then 'Text here'
WHEN 'Hall' THEN 'text here' ELSE END
FROM VENUES
which woudl be fine, but i need to include basica formatting such as bold, carriage returns, etc. is this at all possible?
Thanks!!
-
You should really consider doing this kind of presentation logic in the client. But anyway, you can of course include formatting in the output. But how to do it depends on what you are using the output for. Is it html for a web page, RTF for a richtextbox or what? For instance, if it is html, here is an example:
SELECT CASE type
WHEN 'cinema' then '<b>Text here</b><br>' -- bold with break
WHEN 'Hall' THEN '<i>text here</i>' ELSE END -- in italics
FROM VENUES
-
No, it's for a RTF file?
-
Here's a link to another post where something similar was discussed:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=185007#bm185291
**ASCII stupid question, get a stupid ANSI !!!** -
Good luck then.
Seriously though, constructing RTF by hand is a nightmare. I would definately suggest implementing a client that pulls the text from the database and uses some API to construct the RTF code for the file.
-
You should see this piece of p00 report writing tool that im lumbered with. seriously.
cheers sushila for the suggestion althuogh it's not quite what im after.
as an aside.. can you use IN and LIKE with the WHEN clause of a CASE statement? chuckign up errors with:
SELECT CASE (SELECT Type_Number
FROM Arrangements
WHERE Events.Number_SQL = EventNo)
WHEN IN (46, 11 ,12 ) THEN 'Day Delgates Rate' ELSE '' END AS CinemaOption1
FROM Events
WHERE Events.Number_SQL = 2
chucks up errors.. as does changing it around slightly to work on a LIKE statement
SELECT CASE (SELECT Type
FROM Arrangements
WHERE Events.Number_SQL = 1)
WHEN LIKE '%Cinema%' THEN 'Day Delgates Rate' ELSE '' END AS CinemaOption1
FROM Events
WHERE Events.Number_SQL = 2
hmm!
thanks,
Alex!
-
>You should see this piece of p00 report writing tool that im lumbered with.
Well then you have got some work to do ahead of you. In the same way as I included html formatting in the example above you can include rtf formatting instead. But getting that to work well is like I said a very boring job. In any case the reference I linked to should tell you anything you need.
Regarding IN and LIKE you can do this:
select case when 1 in (1, 2) then 'a' else 'b' end
select case when 'abcd' like 'abc%' then 'a' else 'b' end
But not this:
select case 1 when in (1, 2) then 'a' else 'b' end
select case 'abcd' when like 'abc%' then 'a' else 'b' end
-
may have to be the only way Chris... great.
cheers for the above, didnt know that.
Can you alias the results so..
select case 1 when in (1, 2) then 'a' else 'b' end AS myAlias ?
Thanks
-
Can you alias the results so..
select case 1 when in (1, 2) then 'a' else 'b' end AS myAlias ?
Yes, the result is a column in a resultset and works just as any other column.
-
Thanks Chris... just one more thing (like Columbo)
if i use
SELECT CASE WHEN x LIKE 'A%' THEN 'blah'
How do i add WHEN y LIKE 'B%' THEN 'blah2'?
cheers!
-
Just keep adding them. As soon as one WHEN-test evaluates to true that tests THEN part will be assigned to the receiving variable or as in this case a column in the output. This example should explain:
SELECT CASE
WHEN x LIKE 'A%' THEN 'blah'
WHEN y LIKE 'B%' THEN 'blah2'
ELSE 'blah3'
END
FROM (
SELECT 'ABC' AS x
, 'BCD' AS y
) foo
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply