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.
>

CASE does not give correct result

How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)

Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.

The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').

What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0

The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.

When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.

So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.

Does anyone know how I can make this work?Read this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923𺇃|||You said you wanted iron clad?
-- prepare test data
declare @.test table (t varchar(50))

insert @.test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'

-- show data before inserting new values
select * from @.test

-- do the work
declare @.prefix varchar(50)

select @.prefix = 'ov' -- Throw anything in!

insert @.test
select @.prefix + convert(varchar, t.n + 1)
from (
select max(convert(int, substring(t, 1 + len(@.prefix), 50))) n
from @.test
where t like @.prefix + '%'
and not substring(t, 1 + len(@.prefix), 50) like '%[^0-9]%'
) t
where not @.prefix like '%[0-9]%'
and not t.n like '%[^0-9]%'

-- show data after inserting new values
select * from @.test|||I honestly tried to fit/convert your solution to my problem but I simply do not understand what you mean with things like "@.test" and "@.prefix".

This is mainly because I am merely a functional consultant in SAP Software and not a technical specialist like you. Is it therefore possible to explain your solution more in my context? :-)|||@.test is the table used for this test.
@.prefix is the first couple of characters used for searching a client number. you set @.prefix to the client number you want, in your case "c" or "s".

Cascading parameters problem : First parameter is textbox

Hi,
I am using cascading parameters in my report. The first paramter is a
servername which user types in a TextBox. The second parameter is
populated based on the first parameter.
The problem is secord paramter drop down list doesn't get populated
unless I press "View Report" button in the preview mode.
Is there any way to populate second parameter (drop downlist) as user
finished typing the first parameter (TextBox)
please help,
regards,
SAchinUser needs to press "Tab" key to populate and need not press "view report"
Amarnath
"sachin laddha" wrote:
> Hi,
> I am using cascading parameters in my report. The first paramter is a
> servername which user types in a TextBox. The second parameter is
> populated based on the first parameter.
> The problem is secord paramter drop down list doesn't get populated
> unless I press "View Report" button in the preview mode.
> Is there any way to populate second parameter (drop downlist) as user
> finished typing the first parameter (TextBox)
> please help,
> regards,
> SAchin
>

Cant use the NTEXT datatype in SQLCLR scalar-valued functions

From the SQL Server documentation :

"The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, exceptrowversion,text,ntext,image,timestamp,table, orcursor"

This is a problem for me. Here's what I'm trying to do :

I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.

Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.

The sql server 2005 nvarchar(max) is the same as sql server 2000 nText

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||Cool. Thanks for the tip. I'll give this a shot and report back.