Case Statement!

Hi all,
I am trying to return a true / false value via case statement.
The boolean value returned is determined whether a column contains a null
value.
Can someone help with the following query as it is causing an error...
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN IsNull THEN 0
WHEN Not IsNull Then 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Cheers,
Adam
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IS NULL THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||Try (untested)
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IsNull THEN 0
WHEN a.ColumnID Is not Null Then 1
END AS colAlias
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID
Is Null THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||Try this.
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID Is Null THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl:

> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
I suspect that you're misusing the ISNULL function. AFAICT, the syntax
for ISNULL is: ISNULL ( check_expression , replacement_value )
Try something like:
CASE ISNULL(a.ColumnID, 0)
WHEN 0 THEN 0
ELSE 1
END AS aID
or
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END AS aID
HTH,
Geoff Lane
Cornwall, UK|||Hi Adam,
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
HTH, Jens Suessmeyer.sql

Case Statement Question

Hi All,

Can anybody tell why the underlined when statement doesn't return any results? It should return something like this: Sun - Sat 10:30PM

select substring(sjt.name,1,charindex(':',sjt.name)-1),
case js.freq_type when '8' then
case js.freq_interval
when '1' then 'Sun'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
when '64' then 'Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) end
when '4' then
case js.freq_subday_type when '4' then 'Sun - Sat'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+'-'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_end_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+' '+'every'+' '+cast(js.freq_subday_interval as varchar(2))+' '+'min'
when '4' then 'Sun - Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) end end
from
(select name, job_id, active_start_time, freq_type, freq_interval, freq_subday_type, freq_subday_interval, active_end_time
from sysjobschedules
where name like 'ALGL%' or name like 'WC%') as js
join
(select name, job_id
from sysjobs
where name like 'ALGL%' or name like 'WC%') as sjt
on sjt.job_id = js.job_id
order by sjt.name

This is the output of this query:
---------- ------------
ALGL-VCS Data Process Sun 10:30PM
ALGL-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
ALGL-VCS Data Process NULL (underlined when stmt)
ALGL-VCS Data Process NULL (underlined when stmt)
ALGL-VCS Maintenance Process Sat 10:50PM
WC-VCS Data Process Sun 10:30PM
WC-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
WC-VCS Data Process NULL (underlined when stmt)
WC-VCS Data Process NULL (underlined when stmt)
WC-VCS Maintenance Process Sat 10:50PM
WC-VCS Maintenance Process NULL (underlined when stmt)

Thanks.Is it just me, or does it appear that you have two WHEN '4' entries in your case statement?

Perhaps you aren't seeing the results of the second one because you are seeing the results of the first one?

Not trying to be a smartass, but unless I missed an END somewhere, seems like you have duplicate WHEN's in there.

BTW...my head hurts from reading that code...yikes!|||Perhaps formatting the statement a bit will point out what I think is an issue to deal with:select substring(sjt.name,1,charindex(':',sjt.name)-1),
case js.freq_type
when '8' then case js.freq_interval
when '1' then 'Sun'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
when '64' then 'Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end
when '4' then case js.freq_subday_type
when '4' then 'Sun - Sat'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+'-'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_end_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+' '+'every'+' '+cast(js.freq_subday_interval as varchar(2))+' '+'min'
when '4' then 'Sun - Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end
end
from
(select name, job_id, active_start_time, freq_type, freq_interval, freq_subday_type, freq_subday_interval, active_end_time
from sysjobschedules
where name like 'ALGL%' or name like 'WC%') as js
join
(select name, job_id
from sysjobs
where name like 'ALGL%' or name like 'WC%') as sjt
on sjt.job_id = js.job_id
order by sjt.name|||Instant SQL Formatter:
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl|||Thanks. I fixxed it. It works.

CASE problem

Hello all.
I'm trying to use a CASE statement to return the Month name from a month
number (1=Jan, 2=Feb, ect.) I have a field in my db that stores the date as
epoch time.
Here's the entire SQL, I get an "syntax error near =" when I run this:
SELECT
CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6, 'Jan 1, 1970'),
100) AS Create_Date_Converted,
DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6,
'Jan 1, 1970'), 100)) AS Create_Date_Converted_Month,
DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6,
'Jan 1, 1970'), 100)) =
CASE
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
END,
Create_Date,
Priority
FROM ARAdmin.Help_Desk
I'm a total newbie. Trying the CASE part of this query in different ways
hasn't resulted in any solutions. Any/all help is greatly apprecitated!lol, this is easy,, use the DateName() Function
Forget the case
Select DateName(month, getdate())
If you just want the abbreviation, take the Leftmost 3 Characters
Select Left(DateName(month, getdate()), 3)
"Drew" wrote:

> Hello all.
> I'm trying to use a CASE statement to return the Month name from a month
> number (1=Jan, 2=Feb, ect.) I have a field in my db that stores the date a
s
> epoch time.
> Here's the entire SQL, I get an "syntax error near =" when I run this:
> SELECT
> CONVERT(char(20), DATEADD(second, Create_Date + 3600 * - 6, 'Jan 1, 1970')
,
> 100) AS Create_Date_Converted,
> DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * -
6,
> 'Jan 1, 1970'), 100)) AS Create_Date_Converted_Month,
> DATEPART (month, CONVERT(char(20), DATEADD(second, Create_Date + 3600 * -
6,
> 'Jan 1, 1970'), 100)) =
> CASE
> WHEN '1' THEN 'Jan'
> WHEN '2' THEN 'Feb'
> WHEN '3' THEN 'Mar'
> END,
> Create_Date,
> Priority
> FROM ARAdmin.Help_Desk
> I'm a total newbie. Trying the CASE part of this query in different ways
> hasn't resulted in any solutions. Any/all help is greatly apprecitated!|||Thanks CBretana.
Your suggestion is to use DateName instead of DATEPART?
"CBretana" wrote:
> lol, this is easy,, use the DateName() Function
> Forget the case
> Select DateName(month, getdate())
> If you just want the abbreviation, take the Leftmost 3 Characters
> Select Left(DateName(month, getdate()), 3)
>
> "Drew" wrote:
>|||On a quick glance, you have a invalid SQL expression in your SELECT
statement. IOW, the statement you post forms a construct similar to:
SELECT <some_expression>,
<some_expression>,
<some_expression> = CASE expression, -- wrong
<other_column>
FROM <your_table>
The third column in the SELECT statement is invalid since it does not return
a valid SQL datatype. CASE expressions evaluates a list of conditions and
returns one of multiple possible result expressions. It can be used in any
SQL clause where a regular expression can be used or in the LHS or RHS of a
valid comparison operator. See the topc CASE in SQL Server Books Online for
more details.
Anith|||Yes, this function does exactly what you said you wanted,, it returns the
monthname (or any date part name) from a date,
"Drew" wrote:
> Thanks CBretana.
> Your suggestion is to use DateName instead of DATEPART?
> "CBretana" wrote:
>|||Thanks CBretana, works like a charm.
"CBretana" wrote:
> lol, this is easy,, use the DateName() Function
> Forget the case
> Select DateName(month, getdate())
> If you just want the abbreviation, take the Leftmost 3 Characters
> Select Left(DateName(month, getdate()), 3)
>
> "Drew" wrote:
>

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.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|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!
klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.
|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol
|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.
|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.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
|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)
|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspf...r />
tive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked t
o
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked t
o
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'

will return 'Burger King' ?!!!klabu wrote:

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!
>
>

That depends on the collation being used...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...

http://sqlserver2000.databases.aspf...-sensitive.html
"klabu" <klabu@.klabucomwrote in message
news:12m6h19lhljpr65@.corp.supernews.com...

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!
>

|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:

Quote:

Originally Posted by

I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol

Glasshouse + stone:
'' IS NULL
'Hello' <'Hello '
Sequences, routines and tables share the same namespace

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||klabu wrote:

Quote:

Originally Posted by

I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol

Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!

Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.

Default when you install SQL Server is a case-insensitive collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:

Quote:

Originally Posted by

lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)

Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html

CASE function result with result expression values (for IN keyword)

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE
GROUP.GROUP_ID = 2
AND DEPT.DEPT_ID = 'D'
AND WORK_TYPE_ID IN
(
CASE DEPT_ID
WHEN 'D' THEN 'A','B','C' <- ERROR
WHEN 'F' THEN 'C','D
ELSE 'A','B','C','D'
END
)

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'WHERE'.

which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

AND

(

(CASE DEPT_ID = 'D' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID = 'F' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID != 'D' AND CASE DEPT_ID != 'F' AND

WORK_TYPE_ID IN ('A','B','C'))

)

Though this could lead to bad performance :-(

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks for the reply . It works, and I agree with you that it could lead to performance degradation.

However, if the number of records involved are filtered and limited to, say under 1000 rows, it would still be managable ? Just a feeling, I know it is hard to quantify the expense of a query by just the row count alone.

Kenny

Case - When - Then - Else?

Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
LeeLee,

Why not just attach 'else' to your case expression...

case when 1=1 then 1
else 0 end

--
-oj
http://www.rac4sql.net

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Thanks for not starting the reply with HI STUPID as I was unaware that it was as simple as that!
Again Thanks,
Lee|||Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Sorry for the repeat post; somehow I had not seen the responses already in the browser. Very confused...

Regards,

Hugh

Originally posted by hmscott
Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

carrier return + line feed in a varchar

Hi

how can I split a line into 2 lines in t-sql. etc
Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's line two'

I will be using it to send a mail from sql server (2000 sp4), so i can build a nice looking mail

> Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's

> line two'

Set @.text1 = 'here''s one line' + CHAR(13) + CHAR(10) + 'here''s line two';

|||thanks a lot

Carriage return within column alias

Is there a way to insert a carriage return or line feed in the middle of a column alias within a select statement? I tried using the CHAR function for the ascii value of the carriage return, but SQL Server wouldn't allow it inside the alias name for the column.
Any ideas?What are you trying to achieve with the end result? Is the result to be used in html or something? If so, you can use html tags in the header.|||The database query will be used in an ASP script run from a web site. The problem was that there were so many columns that I couldn't fit them on one page landscape for printing. If I can put some of the column headings (which are declared as aliases in my SQL query) on two lines as opposed to one long heading line, it will save page space.|||The simple solution, then is to put the HTML tag in the alias.. ie

SELECT col1 as 'COLUMN <BR> ONE'
bla bla bla

then when the column header is rendered by the asp, if it is set up correctly, it will put the break in. I believe, however, that there are ways to do this in HTML w/o the need of putting it in the column name.

Hope this helps.

carriage return problem..

While retrieving user input from an input control, eg: multi-line textbox, and inserting it into the database, the carriage return or the 'Enter' key is not getting inserting into the database.. instead it inserts a quad ( square ) in the database.. also the text typed after the 'Enter' key is not getting inserted into the database.. please help.The carriage returnis getting inserted. The square box confirms that. When rendering it to a web page, you need to replace the carriage returns with their html equivalent - "<br />"|||I'm having the same problem, how would this be accomplished using VB?|||

VB.Net
<%# Eval("MyValue").ToString().Replace(vbcrlf,"<br />") %>

C#
<%# Eval("MyValue").ToString().Replace("\r","<br />") %>

|||

Thanks!

It works great!

I was also able to get it going with the following a few minutes ago:

<%# Eval("MyValue").Replace(Environment.NewLine, "<br />") %>

Which method do you recommend or are they both good?

|||

With the .NET framework, there are approximately 63 ways to skin most particular cats. The difference between them is most often negligible, and you should use whatever you prefer so long as your page load doesn't appear to be adversely affected. Occasionally you will get a guru tell you to use one option rather than another, because it shaves nanoseconds off the operation, and they will have benchmark tests to prove it. Personally, I think life is too short. I usually use the option that requires less typing, unless I am informed of a convincing reason to use another.

Environment.NewLine has the benefit that it can be used regardless of page language, so I shall use it in future when I answer this question without knowing the language the poster is using. Quite simply, it meets my desire to do less typing.

carriage return inside a field of text data type?

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thankstry char(10) instead
or the combination of the two characters|||ive actually tried them both :(

edit: just tried using char(13) + char(10) and it works! thanks!|||you welcome ;)

Carriage Return In View

Is it possible to return one field in a view that has something similar to
vbCrLf in Visual Basic? I want to return it as a formatted envelope
address. Thanks.
Daviduse char(13) as your column value
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"David C" wrote:

> Is it possible to return one field in a view that has something similar to
> vbCrLf in Visual Basic? I want to return it as a formatted envelope
> address. Thanks.
> David
>
>|||Thanks. Actually, I had to use ... + Char(13) + Char(10)
David
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2CEEA448-B43B-457C-BE12-D0547A4F3AD4@.microsoft.com...
> use char(13) as your column value
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Database change management for SQL Server
>
> "David C" wrote:
>

carriage return in label

Hi, any way to code a linefeed/carriage return in a label? I need it for
labels in a pie graph (description on line one, value on line 2 of pie graph
series description).
Any suggestions welcome!
Thanks,
JohnJust use an expression similar to this for the datapoint label expression:
=Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> Hi, any way to code a linefeed/carriage return in a label? I need it for
> labels in a pie graph (description on line one, value on line 2 of pie
> graph
> series description).
> Any suggestions welcome!
> Thanks,
> John
>|||You can also try using System.Environment.NewLine as vbcrlf may not
always be interpreted correctly depending on the report format (PDF,
Excel, HTML,...) you use.
Q
Robert Bruckner [MSFT] wrote:
> Just use an expression similar to this for the datapoint label expression:
> =Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "John" <John@.discussions.microsoft.com> wrote in message
> news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> > Hi, any way to code a linefeed/carriage return in a label? I need it for
> > labels in a pie graph (description on line one, value on line 2 of pie
> > graph
> > series description).
> >
> > Any suggestions welcome!
> >
> > Thanks,
> > John
> >|||Works! Thx!
"baseLogiK" wrote:
> You can also try using System.Environment.NewLine as vbcrlf may not
> always be interpreted correctly depending on the report format (PDF,
> Excel, HTML,...) you use.
> Q
> Robert Bruckner [MSFT] wrote:
> > Just use an expression similar to this for the datapoint label expression:
> > =Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "John" <John@.discussions.microsoft.com> wrote in message
> > news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> > > Hi, any way to code a linefeed/carriage return in a label? I need it for
> > > labels in a pie graph (description on line one, value on line 2 of pie
> > > graph
> > > series description).
> > >
> > > Any suggestions welcome!
> > >
> > > Thanks,
> > > John
> > >
>

Carriage return in header of Flat File Destination

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

I was able to do this using a property expression on the 'header' property (accessed via expressions of the dataflow task).
When I reversed the order of \r\n to \n\r I do get some messages about inconsistent line delimeters in some editors.

"/INST=-1\r\n"
+"/DELIMITER=\",\"\r\n"
+"/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4\r\n"
+"/LOCATION=100\r\n"

Hope this helps

|||How do you get to the "Expression" of the DataFlow task? Right-click does not list "expressions" as a menu item. The Advanced Editor does not provide any apparent access to "Expressions"...?|||In the control flow, right-click on the data flow task and select properties. Scroll down in that list and you'll see "Expressions."|||I'm trying to do something similar in setting up a header for a fixed width flat-file output. When I try to use \r\n after my text, the characters "\r\n" just show up in the header. How do I get a CR+LF? I've tried using ="mytext\r\n" and I just see that entire literal string, including the quotes, appear in the output.|||As Phil pointed out, on the Control Flow tab, you have a DataFlow component (which, when you edit it, leads to the DataFlow tab and displays components there). If you look at the properties for the object on the control tab, one of them is "Expressions" and you can open it to get at the properties of the components on the Dataflow tab (like header for a flat file destination.)

Setting the Expression to a quoted string allows you to include \r\n and they will be translated properly.

Carriage return in header of Flat File Destination

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

I was able to do this using a property expression on the 'header' property (accessed via expressions of the dataflow task).
When I reversed the order of \r\n to \n\r I do get some messages about inconsistent line delimeters in some editors.

"/INST=-1\r\n"
+"/DELIMITER=\",\"\r\n"
+"/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4\r\n"
+"/LOCATION=100\r\n"

Hope this helps

|||How do you get to the "Expression" of the DataFlow task? Right-click does not list "expressions" as a menu item. The Advanced Editor does not provide any apparent access to "Expressions"...?|||In the control flow, right-click on the data flow task and select properties. Scroll down in that list and you'll see "Expressions."|||I'm trying to do something similar in setting up a header for a fixed width flat-file output. When I try to use \r\n after my text, the characters "\r\n" just show up in the header. How do I get a CR+LF? I've tried using ="mytext\r\n" and I just see that entire literal string, including the quotes, appear in the output.|||As Phil pointed out, on the Control Flow tab, you have a DataFlow component (which, when you edit it, leads to the DataFlow tab and displays components there). If you look at the properties for the object on the control tab, one of them is "Expressions" and you can open it to get at the properties of the components on the Dataflow tab (like header for a flat file destination.)

Setting the Expression to a quoted string allows you to include \r\n and they will be translated properly.

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...[vbco
l=seagreen]
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing
it
> all appears on the same line.
> Thanks,
> Mike
>[/vbcol]|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
Mike
You can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>
|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike
|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
---Line 1
Line 2
Line 3
(1 row(s) affected)
|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>
|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike