Prev: find if local variable exists
Next: impersonate SQL Server login
From: kalyan on 9 Aug 2010 05:56
Hi All

can any body help me for the following logic.


Input
-------
a,b

output
---------
case when isnull(a,0)=0 then b else a end

Input
-------
a,b,c

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then c else b
end) else a end

Input
-------
a,b,c,d

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
isnull(c,0)=0 then d else c end) else b end) else a end

and so on..i can give input a comma separated string

earlier help is highly appreciated.

Regards
Kalyan

From: Erland Sommarskog on 9 Aug 2010 07:53
kalyan (kalikoi(a)gmail.com) writes:
> output
> ---------
> case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> isnull(c,0)=0 then d else c end) else b end) else a end
>
> and so on..i can give input a comma separated string

I have an article on my web site that describes several ways to unpack
a comma-separates list into a table,
http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the
problem, you need a method where you get the list position.

Once you have the list in tabular format, you can use this SELECT to
get the value:

WITH numbered AS (
SELECT n, rowno = row_number() OVER(ORDER BY listpos)
FROM iter_intlist_to_tbl(@str)
WHERE n > 0
)
SELECT str FROM numbered WHERE rowno = 1




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: kalyan on 9 Aug 2010 09:00
On Aug 9, 4:53 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> kalyan (kali...(a)gmail.com) writes:
> > output
> > ---------
> > case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> > isnull(c,0)=0 then d else c end) else b end) else a end
>
> > and so on..i can give input a comma separated string
>
> I have an article on my web site that describes several ways to unpack
> a comma-separates list into a table,http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the
> problem, you need a method where you get the list position.
>
> Once you have the list in tabular format, you can use this SELECT to
> get the value:
>
> WITH numbered AS (
> SELECT n, rowno = row_number() OVER(ORDER BY listpos)
> FROM iter_intlist_to_tbl(@str)
> WHERE n > 0
> )
> SELECT str FROM numbered WHERE rowno = 1
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Well i got the solution i used coalesce
From: --CELKO-- on 9 Aug 2010 12:41
This looks like the recursive definition of COALESCE() given in the
standards:

COALESCE (a) => a

COALESCE (a, b) => (CASE WHEN a IS NULL THEN b ELSE a END)

COALESCE (a, b, c, ..) => (CASE WHEN a IS NULL
THEN COALESCE(b,c)
ELSE a END)

Just remember that COALESCE() promotes its results to highest data
type in the list and ISNULL() uses the data type of the first
argument.


 | 
Pages: 1
Prev: find if local variable exists
Next: impersonate SQL Server login