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 using cast

Hi guys,

The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'

I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".

In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.

Please Help.

"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,

Aldo.

Hi,

What is the errormessage?

Is it possible that there are non-nummeric values at these positions?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

the data in the field is a string containing nummeric characters...

I solved the problem using the code below:

SLC = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)= 0 then 'Zero'" _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)>= 1 " _
& "And CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)<= 699 then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values', "

The ERROR in the code I uploaded earlier was using the word "Case" in both cases:

... Case When ...

...Case When...

instead of:

... Case

When

When

This code is working too:

sqlString = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)>= '001' And SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)<= '699' then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values' "

I'll be glad to learn some other good idea.

Thanks,

Aldo.|||Just an FYI, this is a transact-sql question, not an SSIS question. To tie this into SSIS, you could avoid doing the case statement in your SQL and do it in a derived column transformation.

CASE statement question

hi,

Suppose I want to do the following:

DECLARE @.num
SET @.num = -1 /* initial value */

SELECT col1, col2,
CASE @.num
WHEN -1 THEN ( [assign a value to @.num], [return that value] )
ELSE @.num
END AS 'num'
FROM T1

In English, I only want the @.num variable to be calculated once but appear in every column of the SELECT result, since table T1 has many rows.

Greatly appreciate any help... thanks in advance!!Hello,

it looks like SQL Server ?? Is it true ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Yes... it is SQL Server...sql

Case statement in a where clause

Hi All,
I have a bit of a delema here, that I know someone knows how to do it
properly.
If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
where DSP_IN_HOUSE_IN = F.CollectorDesc
the code I am using is:
left outer join tlkCollector F
on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
end
Is this possible without building the where clause dynamically?
TIA,
JoeCASE is an expression that returns a value; it is not a logic flow operator
that allows you to change the meaning and semantics of a query. Assuming
F.CollectorDesc, DSP_IN_HOUSE_IN and DSP_RE_OPEN_IN (curse the people who
named your columns!) are the same data type:
ON F.CollectorDesc = CASE DSP_IN_HOUSE
WHEN 1 THEN DSP_IN_HOUSE_IN
WHEN 0 THEN DSP_RE_OPEN_IN
END
By the way, you should really get in the habit of using aliases on all of
the tables in your join/where clauses. Leaving them out just because
they're the only table with such a column is confusing for other readers /
maintainers of the code, and could bite you if such a column is added to one
of the tables later.
A
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:B8391A53-D820-4EF7-8BA8-751A341E0EED@.microsoft.com...
> Hi All,
> I have a bit of a delema here, that I know someone knows how to do it
> properly.
> If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
> where DSP_IN_HOUSE_IN = F.CollectorDesc
> the code I am using is:
> left outer join tlkCollector F
> on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
> when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
> end
> Is this possible without building the where clause dynamically?
> TIA,
> Joe
>|||You cannot use case statments like that. A case statement returns a value.
Honestly, when you run into problems like this in your query you know it is
time to reconsider your design :-)
John
"jaylou" wrote:

> Hi All,
> I have a bit of a delema here, that I know someone knows how to do it
> properly.
> If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
> where DSP_IN_HOUSE_IN = F.CollectorDesc
> the code I am using is:
> left outer join tlkCollector F
> on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
> when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
> end
> Is this possible without building the where clause dynamically?
> TIA,
> Joe
>|||Thank you for the help! I did know this but my brain went on vacation for
some reason. I guess I needed an extra pair of eyes look at what I thought
looked right. :)
I normally use aliases when I create SPs this is a one time converstion from
an Access Application into a .net App with a SQL Backend.
And YES I do curse them out all the time. this is part of my very large
project of cleaning up all tables and procedure here.
Aren't I a lucky one' :)
"Aaron Bertrand [SQL Server MVP]" wrote:

> CASE is an expression that returns a value; it is not a logic flow operato
r
> that allows you to change the meaning and semantics of a query. Assuming
> F.CollectorDesc, DSP_IN_HOUSE_IN and DSP_RE_OPEN_IN (curse the people who
> named your columns!) are the same data type:
>
> ON F.CollectorDesc = CASE DSP_IN_HOUSE
> WHEN 1 THEN DSP_IN_HOUSE_IN
> WHEN 0 THEN DSP_RE_OPEN_IN
> END
>
> By the way, you should really get in the habit of using aliases on all of
> the tables in your join/where clauses. Leaving them out just because
> they're the only table with such a column is confusing for other readers /
> maintainers of the code, and could bite you if such a column is added to o
ne
> of the tables later.
> A
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:B8391A53-D820-4EF7-8BA8-751A341E0EED@.microsoft.com...
>
>sql

CASE Statement - I'm in a bit of a quandry

Hello everyone,

I'm having a problem with trying to check a value from one column and add a value to the next column based on what the first column is. Below are my to CASE statements. What I need is to make field IN_OUT either "IN" for when field UpDnGrd is "Upgrade" and "Out" for when UpDnGrd is "Downgrade"

Kinda like this:

IF UpDnGrd = "U" THEN make IN_OUT = IN

OR

IF UpDnGrd = "D" THEN make IN_OUT = OUT

ELSE keep the original value of IN_OUT which will always be either IN or OUT

see below example of my current CASE statement. Just want to know how to change the value of IN_OUT based on what the value of UpDnGrd is.

================================================== =
CASE sv7.InOut
WHEN 'I' THEN 'IN'
WHEN 'O' THEN 'OUT'

END AS IN_OUT,

CASE sv11.S11M14

WHEN 'D' THEN 'Downgrade'
WHEN 'U' THEN 'Upgrade'
ELSE ' '

END AS UpDnGrd
================================================== =

Thank YOU!How could I merge the 2 case statements?|||You question and code sample are a little confusing, but I think this is what you want:

SELECT
CASE

WHEN sv11.S11M14 = 'D' THEN 'OUT'
WHEN sv11.S11M14 = 'U' THEN 'IN'
WHEN sv7.InOut = 'I' THEN 'IN'
WHEN sv7.InOut = 'O' THEN 'OUT'
ELSE ' '

END AS IN_OUT

FROM ...

Only the first WHEN condition will be processed.

CASE statement

Trying to calculate a value using a CASE statement - any thoughts?
Thanks.
SELECT
CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
'VAL'
WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
as 'VAL'
FROM
T1
JOIN T2 ON T2.TICKNUM = T1.TICKNUM
JOIN C1 ON C1.CURR_CODE = T1.CURRENCYPlease post the complete table DDL and perhaps a little more detail about wh
at you are attempting to accomplish. Some sample data in the form of INSERT
statements and what your expected output looks like would be helpful.
I'm about valuex and value1.
General comments about using CASE. The form is usually like this:
SELECT
CASE
WHEN {expression} THEN {value}
WHEN {expression} THEN {value}
ELSE {Value}
END AS 'VAL'
FROM ...
You seem to have left out the [END]
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<RBurns6@.gmail.com> wrote in message news:1151419826.520556.136270@.i40g2000cwc.googlegroups
.com...
> Trying to calculate a value using a CASE statement - any thoughts?
> Thanks.
>
> SELECT
> CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
> 'VAL'
> WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
> as 'VAL'
> FROM
> T1
> JOIN T2 ON T2.TICKNUM = T1.TICKNUM
> JOIN C1 ON C1.CURR_CODE = T1.CURRENCY
>|||RBurns6@.gmail.com wrote:
> Trying to calculate a value using a CASE statement - any thoughts?
> Thanks.
> SELECT
> CASE WHEN valuex= '1' THEN (cost * spot) where currency = curr_code as
> 'VAL'
> WHEN value1 = '0' THEN THEN (cost/spot) where currency = curr_code
> as 'VAL'
> FROM
> T1
> JOIN T2 ON T2.TICKNUM = T1.TICKNUM
> JOIN C1 ON C1.CURR_CODE = T1.CURRENCY
>
Based on what you've given us, I'm guessing at column names for the
where clause, but this should be close:
SELECT
CASE
WHEN valuex = 1 THEN cost * spot
WHEN valuex = 0 THEN cost / spot
END AS Val
FROM T1
INNER JOIN T2
ON T1.ticknum = T2.ticknum
INNER JOIN C1
ON T1.curr_code = T2.curr_code
WHERE currency = curr_code
AND valuex IN (0, 1)

case statement

How do i update a column using case statement.

I have a column that has values from 1 to 5.

I wanna update it like: case 1 then change the value to xyz

case 2 then change the value to abc

can anyone help me with the script?

thanks

Not sure whether you are looking for sql command to do this or looking to do this in SSIS ? The sql command to do this would be something like this:

update Table

set column = case when column = 1 then xyz

when column = 2 then abc

end

In SSIS, you can use an execute sql task with a similar command as above.

|||Thanks. this is what i need. i was using the case statement incorrectly.sql

Case Sensitivity

I am wondering if T SQL in SQL Server 2005 is case sensitive.I running am running a query in a stored procedure whchi compares a passed in value with that in a field in the database, as such, is their a need to do this

SELECT * FROM table WHERE UPPER(column_name) = UPPER(@.var)

or will this return the same results

SELECT * FROM table WHERE column_name = @.var

1) A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters

2) T-SQL in also case insensitive.

|||

To see which type your database is, go into SQL Server Management Studio, right-click your database and choose Properties. Select 'General' on the left-hand side, and look at the Collation property (under the 'Maintenance' heading). Somewhere in the name of the property, you'll have either a CS or a CI, standing for Case Sensitive or Case Insensitive, respectively.

I.e., mine is "SQL_Latin1_General_CP1_CI_AS" (SQL Server 2000-compatible). The _CI_ denotes the database is case insensitive.

sql

CASE NOT equal

I have the following statement...
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
ELSE
'Equal'
END
The value for SRV.srv_package is NULL and the value for
PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
When they are clearly NOT equal. Am I failing to use CASE incorrectly
here? Can I NOT use <>?
Any help would be GREATLY appreciated...
wnfisbaNo, nulls do not come under anything. So, usually nulls are not predictable.
add this to the first line and try
SET ANSI_NULLS OFF
and try it..
if it still doesn't work
then do this... hope this helps.
CASE
WHEN isnull(SRV.srv_package,0) <> isnull(PkgtoSRV.Package_SRV,0)
THEN 'Not Equal'
ELSE
'Equal'
END
"wnfisba" wrote:

> I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
'
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba|||NULL is unknown. Repeating something I posted yesterday:
<snip>
Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
</snip>
So, in order to do this comparison, you either need to account for NULLs in
the ELSE, or use COALESCE to allow bogus values into the comparison.
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
WHEN SRV.srv_package = PkgtoSRV.Package_SRV
THEN 'Equal'
ELSE 'Unknown - one or both are NULL'
END
-- assuming -1 is not a possible value
-- though, I don't even have an idea what data type you are using
CASE WHEN COALESCE(SRV.srv_package, -1) = COALESCE(PkgtoSRV.Package_SRV, -1)
THEN 'Equal'
ELSE 'Not Equal'
END
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E25E0D4E-0113-401A-A07C-FB7B86C7DC96@.microsoft.com...
>I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query
> return...Equal?
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba

Case Expression within a Stored Proc

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
a 20 is passed in the clause would read IN(20,21....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
>a 20 is passed in the clause would read IN(20,21....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @.parameter AND @.parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>Thx, Bill

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @.Specific int,
@.RangeStart int
AS
IF (@.Specific IS NULL AND @.RangeStart IS NULL)
OR (@.Specific IS NOT NULL AND @.RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @.Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @.Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @.RangeStart AND @.RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE and DATE statement - Need Help :)

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

Quote:

Originally Posted by opusid

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

use the CASE...WHEN...END statement...

select AdvertPurchasePrice = case when AdvertCreationDate is before 04/10/2007 then 299
else 399
end
from mytable

this is a pseudocode, not a working code...but if you search the help on the CASE-WHEN-END syntax, you'll see what i mean

CASE / NULL Problem

Hello!
I want to set a value depending on a condition but I can't seem to get it
right when the original value is NULL.
Select bla, bla... FROM bla...
*****************************
DellyDate=CASE DelivDate
WHEN NULL THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
***********************************'
This code parses successfully, but even when DelivDate actually is NULL,
DellyDate never has the value '1999-12-31'. What am I doing wrong here?You'll need to use IS NULL.
DellyDate=CASE WHEN DelivDate IS NULL
THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
or alternatively
DellyDate=COALESCE(LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) +
'-' +
RIGHT(DelivDate,2) ,'1999-12-31' )|||DellyDate = CASE WHEN DelivDate IS NULL THEN '1999-12-13' ELSE .... END
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Clarkie" <clarkbones@.rock.sendmenot.etmail.com> wrote in message
news:eKVi$lQJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>|||you could also
coalesce(convert(varchar(10), convert(datetme, DelivDate), 120),
'1999-12-31')
"Clarkie" wrote:

> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>
>|||Read the definition of the CASE expression. You are using a shorthand
for
SELECT ..
FROM ..
WHERE delly_date
=CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
THEN '1999-12-31'
ELSE .. END ;|||Thanks for all the answers!
Problem solved!
//Clarkie
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138581718.965490.84740@.g14g2000cwa.googlegroups.com...
> Read the definition of the CASE expression. You are using a shorthand
> for
> SELECT ..
> FROM ..
> WHERE delly_date
> =CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
> THEN '1999-12-31'
> ELSE .. END ;
>

CASE

Is it possible to use a CASE statement without requiring equality? I don't
want to use a search case, but test a value for match or for range:
CASE @.Integer
WHEN 0 THEN do this
WHEN > 1 do this
WHEN <-1 do that
END
Thanks in advance.try
CASE
WHEN @.Integer = 0 THEN do this
WHEN @.Integer > 1 THEN do this
ELSE do that
END
"R Riness" wrote:

> Is it possible to use a CASE statement without requiring equality? I don't
> want to use a search case, but test a value for match or for range:
> CASE @.Integer
> WHEN 0 THEN do this
> WHEN > 1 do this
> WHEN <-1 do that
> END
> Thanks in advance.
>|||The alternatives for a CASE expression can't be
anything but expressions themselves. Since "do this"
doesn't sound like an expression, this might be more
what's needed (flow control).
Also watching out for the possibility that @.Integer can be
NULL, and avoiding an ELSE that happening more often
than it should:
if @.Integer = 0
begin [do this] end
else if @.Integer > 1
begin [do this] end
else if @.Integer < -1
begin [do this] end
Steve Kass
Drew University
Mike Gemmell wrote:
>try
>CASE
> WHEN @.Integer = 0 THEN do this
> WHEN @.Integer > 1 THEN do this
> ELSE do that
>END
>"R Riness" wrote:
>
>|||Now that I RTFD I know that a searched CASE is not what you want. I am
curious as what you have against a searched case.
If you are attempting something as simple as the example then you could try.
CASE SIGN(@.Integer)
WHEN 0 THEN
WHEN 1 THEN
WHEN -1 THEN
END
to test against a value you could
CASE SIGN(@.Integer - @.Intvalue)
WHEN 0 THEN match_result_expr
WHEN 1 THEN greater_than_result_expr
WHEN -1 THEN less_than_result_expr
END
"Mike Gemmell" wrote:
> try
> CASE
> WHEN @.Integer = 0 THEN do this
> WHEN @.Integer > 1 THEN do this
> ELSE do that
> END
> "R Riness" wrote:
>

Cascading Parameters

I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?

Thanks,

The Rook

I'd be very interested to see how you got one parameter's available values to be dependant on another's. Every way I've tried, I've had a forward dependency error.

Do you mean all available values, or the selected values?

|||I meant the "selected values." MSDN has a walkthrough that's very helpful and it's URL is: http://msdn2.microsoft.com/en-us/library/aa337426(d=printer).aspx - good luck!|||

Thanks for the link.

As for displaying the selected values on the report, that depends. If you are using just single-valued parameters, you can just drop a textbox for each parameter on the page, and set its value to the parameter. If you have a multi-valued parameter, you can try =Join(Parameters!Foo, ", ") for a comma-separated list of selected values. If you wanted something fancier, the only way I can think of is to use that same join syntax to pass the selected parameters to a query that returns the rowset that can populate, say, a table.

cascading parameters

In cascading parameters if we change the value of parent parameter then the
child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
automatically as default.
--
asharmaUse query and dont hard code then a post back happens everytime.
Amarnath,MCTS
"asharma2004" wrote:
> In cascading parameters if we change the value of parent parameter then the
> child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
> automatically as default.
> --
> asharma|||Hi! Actually it is a 3-level dependency. Here it is..
Param_head => Country = Select country from country [single valued param]
Param1=> Region =Select region from region where country in (@.country) [Multi]
Param2=> City= Select city from cities where region in (@.region)
Let's say I selected USA for Country. For this case, Region should display
all the states in USA and City should display all the Cities in USA in
default. However, when I select just one State (i.e California), default
values on City are correct and all selected, but when I try to add another
state, i.e Utah, City displays all the cities in Utah and California but only
California cities are selected.
I could not get around this. Is this a bug or something?
Thanks!
--jarm
"Wei Lu [MSFT]" wrote:
> Hello Jarm,
> Could you please let me know the query you use for those 2 parameters?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jarm,
Yes, I reproduce this issue.
This is because after you change the first parameter, the report will not
refresh so that the default value for the second parameter will not refresh.
The only time that dependent parameters are updated is when an upstream
parameter change causes dependent parameter selections to be invalid. This
satisfies the vast majority of cases where users do not an upstream
parameter change to blow away their other settings (consider a report with
many parameters, all of which might need to be reset if the user decides to
make a change to one of the upstream values).
We'll consider doing this in a future release.
Thank you for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

carrying values from a subreport to a main report

How can I carry a value over from a subreport to a main report?

If I have a total in a sub report, I want to use that total in expression on the main report.

Any ideas?

You can't. You can only pass parameters from the main report to the subreport.|||IS there any way around this? some other method perhaps?|||

Here is something you can try. Create a NET assembly referenced by both reports (master and subreport). In the assembly, define a static collection to store values on per user and report basis (so users can't overwrite each other values), e.g. collection key consists of the user name (User!UserID) and report name (Globals!ReportName(. Next, try adding/changing values from the subreport and see if the master can get them. SSRS 2005 changed the order in which expressions are evaluated so I don't know what will happen but you asked for another way...

If you decide to try this, please share the results here.

carrying values from a subreport to a main report

How can I carry a value over from a subreport to a main report?

If I have a total in a sub report, I want to use that total in expression on the main report.

Any ideas?

You can't. You can only pass parameters from the main report to the subreport.|||IS there any way around this? some other method perhaps?|||

Here is something you can try. Create a NET assembly referenced by both reports (master and subreport). In the assembly, define a static collection to store values on per user and report basis (so users can't overwrite each other values), e.g. collection key consists of the user name (User!UserID) and report name (Globals!ReportName(. Next, try adding/changing values from the subreport and see if the master can get them. SSRS 2005 changed the order in which expressions are evaluated so I don't know what will happen but you asked for another way...

If you decide to try this, please share the results here.

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 column values

Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>

carriage return and line feeds

I have a value of 7.5 when I ask for the length (after I have trimmed it)
the value 5 is returned. Visually this is a 3. Someone suggested there may
be a carriage return and line feed (Char(10) and Char(13)) in the column.
How can I remove these if they exist?
Thanks a bunch,What is the data type? Did you try DATALENGTH? Why aren't you using a
numeric data type to store numeric data?
To remove a CHAR(13), for example, you can use REPLACE():
SELECT REPLACE(column_name, CHAR(13), '')
FROM table_name;
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,|||"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,
What datatype is the column?
declare @.dec decimal(5,3)
set @.dec = 7.5
select len(ltrim(rtrim(@.dec)))
...also returns 5.|||I thoguht this was an interesting behavior, so I did a little more
testing...
declare @.dec decimal(5,3)
declare @.var varchar(20)
declare @.char char(10)
set @.dec = 7.5
set @.var = CAST(@.dec AS varchar(20))
set @.char = CAST(@.dec AS char(10))
select len(ltrim(rtrim(@.dec))) as dec
,len(ltrim(rtrim(@.char))) as char
,len(ltrim(rtrim(@.var))) as var
select @.dec as dec
, @.char as char
, @.var as var
RESULTS
dec char var
5 5 5
dec char var
7.500 7.500 7.500
If the number is defined with 3 decimal places, these 3 places are stored as
zeros. Some applications just happen to display the values without trailing
zeros.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23qk9naLOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
it)
column.
> What datatype is the column?
> declare @.dec decimal(5,3)
> set @.dec = 7.5
> select len(ltrim(rtrim(@.dec)))
> ...also returns 5.
>|||More fun...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.5
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))|||Thats cheating.
You reduced the precision.
But here is one more, which I find more puzzling, although I recall other
languages doing strange rounding like this as well.
I can't recall WHY the rounding works like this...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.555
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
SET @.dec = 7.55
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
Results:
3 7.6
3 7.5
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyuhSwLOGHA.2320@.TK2MSFTNGP11.phx.gbl...
> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>|||> Thats cheating.
> You reduced the precision.
Well, the extra characters are leading, not trailing. Compare LTRIM() and
RTRIM()...
A|||Aaron and Co.,
Thanks for your replies. Unfortunately, the datatype can't be stored as a
number because results that come in are from many different sources
(Laboratories, Radiology etc) and have different formats. The specific
result I am looking at will always return values like 10.3 or 7.5.
The problem is that for some reason, after I strip the extraneous characters
away(which I have done with ease), I try to cast these values as decimal and
it doesn't work.
Here is code and raw data...it's got me stumped...
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
This led me to look at the string lengths of these characters and I
discovered that a few values had what appeared to be spaces in them even
after they were trimmed.
Thanks to all who give their .02.
"Aaron Bertrand [SQL Server MVP]" wrote:

> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>
>|||In your sample output there is an extra space before and after the value in
question.
I know your code is removing spaces, but I'm not sure what accounts for the
values in the output.
Can you post your table DDL? What datatype is decodedvalue?
I suspect you have tabs rather than carriage returns, but try this code...
convert(varchar,replace(replace(replace(
replace(replace(@.dec,'
',''),'> ',''),'%',''),char(10),''),char(13),'')c
har(9),''))
Char(10) and Char(13) are a carriage return and line feed (I can never
recall which is which).
Char(9) is a tab character.
Rather than removing the characters you dont want, change the code to keep
only the characters you do want. I'll see if I can track down a sample of
this, but someone else will probably beat me to it.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:1125946A-8B58-4424-9F4D-C1FBE98CDA6B@.microsoft.com...
> Aaron and Co.,
> Thanks for your replies. Unfortunately, the datatype can't be stored as a
> number because results that come in are from many different sources
> (Laboratories, Radiology etc) and have different formats. The specific
> result I am looking at will always return values like 10.3 or 7.5.
> The problem is that for some reason, after I strip the extraneous
characters
> away(which I have done with ease), I try to cast these values as decimal
and
> it doesn't work.
> Here is code and raw data...it's got me stumped...
> There is something still contained in the string that is messing up the
> cast. (See raw data below code)
> select top 7
> patientid,
> decodedvalue,
> convert(varchar,replace(replace(replace(
decodedvalue,'
> ',''),'>',''),'%','')) value,
> -- cast(convert(varchar,replace(replace(rep
lace(decodedvalue,'
> ',''),'>',''),'%','')) as float) value,
> len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%',''))
str_length
> from
> #diabetes_results
> where
> decodedvalue like '%.%'
> order by
> patientid
> 58 6.8 % 6.8 3
> 58 7.6 % 7.6 3
> 58 6.7 % 6.7 3
> 58 7.1 % 7.1 3
> 58 6.2 % 6.2 3
> 168 7.5 % 7.5 3
> 168 7.5 7.5 5
> Note the length of '5' in the final record though it is obvious the length
> should be 3. I trimmed the column of spaces but they remain.
>
> This led me to look at the string lengths of these characters and I
> discovered that a few values had what appeared to be spaces in them even
> after they were trimmed.
> Thanks to all who give their .02.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Char(10) and Char(13) are a carriage return and line feed (I can never
> recall which is which).
I have two ways of remembering it:
(a) in VB, the constant is named vbCrLf (carriage return, line feed)
(b) they come in inverse order
So, CHAR(13) is carriage return, CHAR(10) is line feed. In some
environments, this is the order it has to be in; in some environments, the
opposite is true. And to make things even more fun, in some environments,
you can have them in either order.
At least that's how I loosely remember it.
A