CASE returning different data types

Hi, folks...
I was trying to write a sql query which returns different data types, but it
returns every time, a smalldatetime format...
SELECT CASE WHEN Tipo = 'N' THEN Numerico
WHEN Tipo = 'L' THEN Logico
WHEN Tipo = 'P' THEN Percentual
WHEN Tipo = 'D' THEN Data
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
'Numerico' is an integer field
'L' is a bit field
'P' is a float field
'D' is a smalldatetime field.
Whatever is the datatype returning, the result is a smalldatetime field...
Any help would be apreciated.
Daniela.CASE is an expression -- and by definition every path of an expression must
return the same datatype (think of a function in a procedural language --
any function you define can only have a single return datatype). You might
consider, in this case, casting all return values to a string datatype.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Daniela Binatti" <Daniela Binatti@.discussions.microsoft.com> wrote in
message news:39E5A440-51BB-4DAF-A73B-8BDFD9E136F7@.microsoft.com...
> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||No, can't do that.. ALL Possible data values that could come out of a case
must be the same datatype. That's because the datatype is not associated
with the data item itself, but with the COLUMN of the resulting Resultset.
SQL has t oassigna a datatypes to the Column...
But not all s lost... You simply have t oCAST The data in each primitive
Table colum,n to the same datatype in the case statement
SELECT CASE WHEN Tipo = 'N' THEN Cast(Numerico As VarChar(20))
WHEN Tipo = 'L' And Logico = 1 THEN 'True'
WHEN Tipo = 'L' And Logico = 0 THEN 'False'
WHEN Tipo = 'P' THEN Cast(Percentual As VarChar(20))
WHEN Tipo = 'D' THEN Convert(VarChar(20), Data, 112)
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||If they are different types then how do you want to display them in a
single column? Maybe we could help you better if you explain more about
what you are trying to do.
David Portas
SQL Server MVP
--|||Thank you very much. I've sorted out the problem by converting every result
in a sql_variant field.
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>

Can't use TAB key in SQL Pane

Hello folks,
When working in Visual Studio 2005 Reporting Services, I have been
cursed with a small problem that I have run out of remedy ideas for.
On the data tab of a report I am no longer able to use the TAB key on
the keyboard to format my SQL. I say no longer because i was using
Visual Studio 2003 until recently and this problem did not exist. If
I hit TAB when writing code in the SQL pane the cursor just moves to
the next action object (button) as if on a form. The only band-aid
solution has been to write all my code in a SQL Management Studio
query window and than copy/paste it into the report SQL pane but this
is a nuisance, especially for short,easy report queries and when
returning to existing reports for upgrades or bug fixes. Another
workaround has been to copy a single TAB from Notepad or any other app
and paste the tab in the SQL pane when needed, but than i have to re-
copy it each time if i copy something else. Any ideas or
sympathizers, I would love to hear from you.
Thanks, and here is an example of what I mean.
/* this is what I am stuck with */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.foo
/* this is what I want */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.fooNo way t o use tab, you need to use spacebar with space, I understand if it
is a small query you can do it, but if it is a big query will be very
tedious.
what otherway you can do is, just click "generic query builder" and again
click what it does is, it indends automatically.
Amarnath
"Skilliam" wrote:
> Hello folks,
> When working in Visual Studio 2005 Reporting Services, I have been
> cursed with a small problem that I have run out of remedy ideas for.
> On the data tab of a report I am no longer able to use the TAB key on
> the keyboard to format my SQL. I say no longer because i was using
> Visual Studio 2003 until recently and this problem did not exist. If
> I hit TAB when writing code in the SQL pane the cursor just moves to
> the next action object (button) as if on a form. The only band-aid
> solution has been to write all my code in a SQL Management Studio
> query window and than copy/paste it into the report SQL pane but this
> is a nuisance, especially for short,easy report queries and when
> returning to existing reports for upgrades or bug fixes. Another
> workaround has been to copy a single TAB from Notepad or any other app
> and paste the tab in the SQL pane when needed, but than i have to re-
> copy it each time if i copy something else. Any ideas or
> sympathizers, I would love to hear from you.
> Thanks, and here is an example of what I mean.
> /* this is what I am stuck with */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
> /* this is what I want */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
>