Case Statements

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

Quote:

Originally Posted by anishap

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

i think this would be better if you build the WHERE from your form/gui. you have might have to adjust the returned value of your checkbox. depending on your front-end tool.

something like

queryvar = queryvar + ' WHERE STATUS IN ('
for counter = numbercheckbox
if checkbox is checked
queryvar = queryvar + returnedvalueofcheckbox

queryvar = queryvar + ')'

this is a psedocode. am suggesting the technique, not the syntax. depending on your gui, you're going to have to adjust it accordingly

CASE Statement, works but.....

My CASE Statement works fine like this:

================================================== =======
CASE

WHEN so.PhoneNum LIKE '999%' THEN 'Chester'

END AS Company,(Works!)
================================================== =====

But how can I get it to work checking more than 1 instance of PhoneNum?

This doesn't work:

================================================== ====
CASE

WHEN so.PhoneNum LIKE '999%' THEN 'City1'
WHEN so.PhoneNum LIKE '997%' THEN 'City2 '
WHEN so.PhoneNum LIKE '998%' THEN 'City3'

END AS Company,(Fails!)
================================================== ====

This fails with an out put of "NULL"

ThanksFound the solution - this works!

[Company] =
CASE
WHEN so.PhoneNum LIKE '999%' THEN 'Chester'
WHEN so.PhoneNum LIKE '998%' THEN 'Camden'
WHEN so.PhoneNum LIKE '997%' THEN 'Great Falls'
WHEN so.PhoneNum LIKE '803%' THEN 'Winnsboro'
END,

Case Statement(Help Please)

I am trying to use a case statement to pull bad phone numbers out of my
company table into a readable format. I am using SQL Server 2000 SP4.
The phone numbers, in my inherited database, are in any number of formats,
not complete, etc, and I need to pull a report showing only the good phone
numbers.
My Case statement returns the error below the statement.
-- SELECT SUBSTRING((RTRIM(phone) + ' '+
RTRIM(phone) + ' '), 1, 25) AS Name, phone,
Phone =
CASE
WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
END
FROM company
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '+(' to a column of data type int.
RayRay wrote on Fri, 11 Nov 2005 07:05:07 -0800:

> I am trying to use a case statement to pull bad phone numbers out of my
> company table into a readable format. I am using SQL Server 2000 SP4.
> The phone numbers, in my inherited database, are in any number of formats,
> not complete, etc, and I need to pull a report showing only the good phone
> numbers.
> My Case statement returns the error below the statement.
> -- SELECT SUBSTRING((RTRIM(phone) + ' '+
> RTRIM(phone) + ' '), 1, 25) AS Name, phone,
> Phone =
> CASE
> WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
> WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
> END
> FROM company
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value '+(' to a column of data type
> int.
SUBSTRING(phone, 1, 2) > len(10) is the problem
This takes the first 2 characters of the phone column, and compares it to
the length of the string '10' (implicit conversion of the LEN expression to
a string). The first 2 characters are '(+', and due to implicit conversion
will try to convert that to an integer to compare to the value 2.. What
exactly are you trying to achieve here? It makes no sense.
Dan|||I am trying to pull good phone numbers from a list of bad numbers.
For Example:+ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+ () 011-2711-320-50
+ () 011-2711-377-38
+ () 011-322-545-252
+ () 011-331-4323-20
+ () 011-358-013360
+ () 011-418-643-307
+ () 011-44-002-7551
+ () 011-45-4468-446
+ () 011-468-719-500
+ () 011-495-254-991
+ () 011-603-707-449
+ () 011-612-4655-24
I inhereted this database from someone who just put stuff in anyway they
wanted. Now I need to pull out the ones formatted corrected so they can be
inputted into a new system.
Ray
"Daniel Crichton" wrote:

> Ray wrote on Fri, 11 Nov 2005 07:05:07 -0800:
>
> SUBSTRING(phone, 1, 2) > len(10) is the problem
> This takes the first 2 characters of the phone column, and compares it to
> the length of the string '10' (implicit conversion of the LEN expression t
o
> a string). The first 2 characters are '(+', and due to implicit conversion
> will try to convert that to an integer to compare to the value 2.. What
> exactly are you trying to achieve here? It makes no sense.
> Dan
>
>|||Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:

> I am trying to pull good phone numbers from a list of bad numbers.
Sorry, I guess I wasn't being clear enough. From the SELECT statement you
provided I can't figure out what you're trying to do. I can only guess that
you're looking for strings of at least 10 characters after the +( at the
start, but as to what you want returned it makes no sense - why would you
display the first 25 characters of the phone number concatentated to itself
as the name column, and then show the phone number again as phone, and then
appear to just put the string 'Phone' into another column called Phone? A
long list of numbers doesn't explain anything. What is a well formatted
phone number? None of those I would consider well formatted, but then again
I'm a UK resident and we have a different number format here.
Dan|||Daniel Crichton wrote:
> Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:
>
> Sorry, I guess I wasn't being clear enough. From the SELECT statement
> you provided I can't figure out what you're trying to do. I can only
> guess that you're looking for strings of at least 10 characters after
> the +( at the start, but as to what you want returned it makes no
> sense - why would you display the first 25 characters of the phone
> number concatentated to itself as the name column, and then show the
> phone number again as phone, and then appear to just put the string
> 'Phone' into another column called Phone? A long list of numbers
> doesn't explain anything. What is a well formatted phone number? None
> of those I would consider well formatted, but then again I'm a UK
> resident and we have a different number format here.
Another idea thrown into the mix: write a user defined function that
either maps a given phone number to a valid one or maps phone numbers to
something like "ok", "maybe", "crap". Then you can easily select
your_function(phone) = 'ok'.
Kind regards
robert

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 Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:
> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
> >I have the case statement below and when the processing runs (set
> > transname=(case stmt...) or insert into table (col1,col2...) select
> > col1,case
> > stmt from anothertable, the processing from within the part of the case
> > statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> > not
> > process the statements inside the case within the case. I suspect I have
> > committed an error but I can't pin it down. Help appreciated.
> >
> > CASE ISNULL(trans_id,0)
> > WHEN 5 THEN
> > CASE Upper(tloc_id)
> > WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> > WHEN 'FIN-INITG' THEN 'To Burlington'
> > WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> > Location' ELSE 'Move To Location' END -- move it in
> > WHEN 'REWORK' THEN 'Rework'
> > ELSE 'Move To Location'
> > END
> > WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> > 'Undefined' END
> > WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> > 'Undefined' END
> > WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> > WHEN 101 THEN 'Shipped'
> > WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> > 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> > Upper(tloc_id)='OF-OUT'
> > THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> > ELSE 'Undefined'
> > END
> >
> > Regards,
> > Jamie
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id)IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
Jamie
Well let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie
|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,cas
e
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Locatio
n'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- mov
e
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expressio
n?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Lo
ok
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>

CASE statement within join

Is it possible to have a join with case statement in it?
i.e.
select * from a inner join b on
case [x] then a.xid = b.xid
case [y] then a.yid = b.yid?What condition are you checking for?

That's a pretty malformed CASE statement you've got there...|||Wanted to do a join based on case statements. Instead I will just use the case statements in a function and join based on function.

This is probably not clear, but basically to join based on case statements (i.e. join two tables based on CASE of a field) use:

1. left outer join for each CASE, thus not using CASE in syntax, but using left outer join in place of CASE

2. create a function that checks the field in CASE and returns value. Then join based on function i.e. dbo.wholeword(a.searchword) = b.searchword|||BUT WHAT CONDITION DO YOU WANT TO CHECK FOR?

Is this not a clear request?

My best guess is that you want 2 queries and a union

Good luck

Case Statement Within A Select Where 2 or More Instances Of The Record Exist.

Ok,
I have a data warehouse that I am pulling records from using Oracle
SQL. I have a select statement that looks like the one below. Now what
I need to do is where the astrics are **** create a case statement or
whatever it is in Oracle to say that for this record if a 1/19/2005
record exists then End_Date needs to be=1/19/2005 else get
End_Date=12/31/9999. Keep in mind that a record could have both a
1/19/2005 and 12/31/9999 instance of that account record. If 1/19
exists that takes presedent if it doesnt then 12/31/9999. The problem
is that the fields I pull from the table where the end_date is in
question change based on which date I pull(12/31/9999 being the most
recient which in some cases as you see I dont want.) so they are not
identical. This is tricky.
Please let me know if you can help.

SELECT
COLLECTOR_RESULTS.USER_ID,
COLLECTOR_RESULTS.LETTER_CODE,
COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,
COLLECTOR_RESULTS.ACTIVITY_DATE,
COLLECTOR_RESULTS.BEGIN_DATE,
COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,
COLLECTOR_RESULTS.PLACE_CALLED,
COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,
COLLECTOR_RESULTS.ORIG_FUNC_AREA,
COLLECTOR_RESULTS.ORIG_STATE_NUMBER,
COLLECTOR_RESULTS.CACS_FUNCTION_CODE,
COLLECTOR_RESULTS.CACS_STATE_NUMBER,
COLLECTOR_RESULTS.STATE_POSITION,
COLLECTOR_RESULTS.TIME_OBTAINED,
COLLECTOR_RESULTS.TIME_RELEASED,
COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,
sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,
FROM
COLLECTOR_RESULTS,
COLLECT_ACCT_SYS_DATA,
COLLECT_ACCOUNT
WHERE
COLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)
AND
COLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)
AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)
AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)
AND COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''01/19/2005'',''mm/dd/yyyy'')
AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************On 20 Jan 2005 11:13:31 -0800, philipdm@.msn.com wrote:

>Ok,
>I have a data warehouse that I am pulling records from using Oracle
>SQL.

Hi philipdm,

You posted this question in a newsgroup for MS SQL Server. I doubt you'll
get any specific Oracle help here.

But if I understand your requirements correctly, I think you can solve it
using only ANSI-standard SQL: correlated subquery, group by and aggregate
functions. I assume Oracle will have no trouble running those! I'm not
sure if the NULL handling requires sppecial attention (see notes down
below).

First, let me check if I correctly understand your requirements:

> Keep in mind that a record could have both a
>1/19/2005 and 12/31/9999 instance of that account record. If 1/19
>exists that takes presedent if it doesnt then 12/31/9999.

The way I read this is: check collect_acct_sys_data for a particular
acct_num / location_code combinations. If there's a row for 1/19/2005, use
that. If there's a row for 12/31/9999 but no row for 1/19/2005, use the
12/31/9999 row. If there's no row for 1/19/2005 and no row for 12/31/9999,
use no row at all - the join will fail and the rows for the other tables
that use this acct_num / location_code combination won't be included in
the result set either.

I'd use something like the following. Note: I've used table aliasses and
converted the table and column names to lower case to improve readability;
I kept the (+) symbols you included and didn't change the format of the
to_date function calls - neither of these are known in MS SQL Server, so I
have no idea if they're right or wrong.

SELECT CR.User_ID,
... (lots of other columns)
FROM Collector_Results AS CR,
Collector_Acct_Sys_Date AS CASD,
Collect_Account AS CA
WHERE CA.Acct_Num = CASD.Acct_Num(+)
AND CA.Location_Code = CASD.Location_Code(+)
AND CA.Acct_Num = CR.Acct_Num(+)
AND CA.Location_Code = CR.Location_Code(+)
AND CR.Activity_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
AND CA.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND CASD.End_Date =
(SELECT MIN(CASD2.End_Date)
FROM Collector_Acct_Sys_Date AS CASD2
WHERE CASD2.Acct_Num = CASD.Acct_Num(+)-- Do you need the
(+) here?
AND CASD2.Location_Code = CASD.Location_Code(+)-- Do you
need the (+) here?
AND ( CASD2.End_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
OR CASD2.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')))

Final note: if the possibility exists that no row in CASD for a given
acct_num / location_code with either of the two dates, the subquery should
return NULL; the clause "AND CASD.End_Date = (subquery)" will evaluate to
"AND CASD.End_Date = NULL", which should not be true for any value of
CASD.End_Date (not even if CASD.End_Date is NULL!!). This is how NULLS
should be treated according to ANSI standard. If Oracle treats the result
of an empty subquery or comparison to NULL differently, then you should
tweak the query to get the correct results.

Best, Hugo
--

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

Case statement with multiple conditions

Obviously the below Case expression does not work. I have conditions on two
fields. If it is true then proceed.
CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
GETDATE()
ELSE
tbl.end_dt
END
Please Help,
Culammy bad,
Solution:
CASE WHEN su.deleted_flag = 1
THEN
CASE WHEN su.end_dt IS NULL THEN
CONVERT(VARCHAR(11), GETDATE() - 1, 101)
ELSE su.end_dt
END
ELSE
su.end_dt
END
"culam" wrote:

> Obviously the below Case expression does not work. I have conditions on t
wo
> fields. If it is true then proceed.
>
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam|||> Obviously the below Case expression does not work. I have conditions
> on two fields. If it is true then proceed.
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam
Apart from = NULL, this should work like you expect it to.
Lasse Vgsther Karlsen
http://usinglvkblog.blogspot.com/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x2A42A1C2

CASE statement with IN/OR

Hello,

I'm trying to write a query with case statement.

the condition is

when project_ref =393 then select qtn_ref in (7070000,7060000))
and when project_ref =391 and select q.qtn_ref=8700000

I need this condition in 'WHERE' statement.

I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query.

Tring to do something like this but it doesn't work.

SELECT *

FROM table

WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000
when project_ref =391 then 8700000

Anyone can help, please?

Hi,

I just modified your Query. i dont have an SQL Installation so im not able to test the Query.

Plz Try this i hope i would solve your problem.

SELECT * FROM table
WHERE (qtn_ref = case when project_ref =393 then 7070000
when project_ref =391 then 8700000) OR
(qtn_ref = case when project_ref =393 then 7060000
when project_ref =391 then 8700000)

|||

SELECT*FROM yourTable

WHERE qtn_ref=(casewhen project_ref=393 then 7070000

when project_ref=391 then 8700000 end)OR

qtn_ref=(casewhen project_ref=393 then 7060000

when project_ref=391 then 8700000 end)

|||thanks guys. it works.|||Plese check one of them as answers!

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

case statement with an sql query statement

a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??Originally posted by ONIL
a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??

can you show the statement you are trying to execute? Or a better understanding of what you are after?|||ok here's the issue
i have the following: -
1. a access table: telephone_directory (fields are first name, last name, extension_no, building_name)
2. a form with menu find and sub menus "by extension number", "by first name", "by last name". ALSO another form named PF with a text box and a listbox

3. a sql parameter query as: -
cq.SQL = "PARAMETERS something1 INTEGER; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(EXTENSION_NO,1) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,2) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,3) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,4) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,5) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,6) LIKE [something1] " & _
" OR EXTENSION_NO LIKE [something1] " & _
" ORDER BY EXTENSION_No; "
4. a bas file with the parameter query wherein case 1 is for extension number case 2 is for last names wherein the sql query is : -

cq.SQL = "PARAMETERS something1 text; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(LAST_NAME,1)LIKE [something1] " & _
" OR LEFT(LAST_NAME,2)LIKE [something1] " & _
" OR LEFT(LAST_NAME,3)LIKE [something1] " & _
" OR LEFT(LAST_NAME,4)LIKE [something1] " & _
" OR LEFT(LAST_NAME,5)LIKE [something1] " & _
" OR LEFT(LAST_NAME,6)LIKE [something1] " & _
" OR LAST_NAME LIKE [something1] " & _
" ORDER BY LAST_NAME; "
.... and so on

5. now how do i call for the "case" in the PF form so that the text box takes the input and listbox displays result for all types of find. I am succesful with different forms for each FIND but i want to use ONLY ONE form.|||Since it's an Access database I don't know if IIf() or Switch() would help at all.sql

CASE statement with an IN

hi there,
I am trying to do this, it would simply my sql so much, but the syntax won't
parse.
DECLARE @.Tmp varchar
--@.Tmp will either be cow or pig after something done here, lets say pig for
simplification..
SET @.Tmp = 'pig'
SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 THEN
(1,2) END)
To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I want
to use one select statement but the WHERE clause changes depending on the
value of @.Tmp.
If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it
is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is thi
s
simply not possible or have I got the syntax wrong? it doesnt seem an
unlikely thing to want to do...
I even tried ditching the idea and doing an if and rewriting the whole
select statement depending on @.Tmp. Only problem is it is the select
statement for a cursor FOR statement, so fiddling around with that is tricky
too !!!Hi Louise
Try something like
SELECT * From Blah
WHERE ( SomeField = 1 AND @.tmp = 'cow')
OR ( SomeField = 2 AND @.tmp = 'pig')
John
"louise raisbeck" wrote:

> hi there,
> I am trying to do this, it would simply my sql so much, but the syntax won
't
> parse.
> DECLARE @.Tmp varchar
> --@.Tmp will either be cow or pig after something done here, lets say pig f
or
> simplification..
> SET @.Tmp = 'pig'
> SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 TH
EN
> (1,2) END)
> To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I wa
nt
> to use one select statement but the WHERE clause changes depending on the
> value of @.Tmp.
> If it is cow, then I just want the clause to be WHERE SomeField IN(1), if
it
> is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
> lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is t
his
> simply not possible or have I got the syntax wrong? it doesnt seem an
> unlikely thing to want to do...
> I even tried ditching the idea and doing an if and rewriting the whole
> select statement depending on @.Tmp. Only problem is it is the select
> statement for a cursor FOR statement, so fiddling around with that is tric
ky
> too !!!|||ok, that works [blush]!! thanks.
"John Bell" wrote:
> Hi Louise
> Try something like
> SELECT * From Blah
> WHERE ( SomeField = 1 AND @.tmp = 'cow')
> OR ( SomeField = 2 AND @.tmp = 'pig')
> John
> "louise raisbeck" wrote:
>|||It looks like you are going to have to construct your SQL statement in a
string variable and then use EXEC() to submit the string to the query parser
.
SET @.strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @.Tmp + ')'
EXEC(@.strSQL)
HTH,
Mike
"louise raisbeck" wrote:

> hi there,
> I am trying to do this, it would simply my sql so much, but the syntax won
't
> parse.
> DECLARE @.Tmp varchar
> --@.Tmp will either be cow or pig after something done here, lets say pig f
or
> simplification..
> SET @.Tmp = 'pig'
> SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 TH
EN
> (1,2) END)
> To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I wa
nt
> to use one select statement but the WHERE clause changes depending on the
> value of @.Tmp.
> If it is cow, then I just want the clause to be WHERE SomeField IN(1), if
it
> is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
> lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is t
his
> simply not possible or have I got the syntax wrong? it doesnt seem an
> unlikely thing to want to do...
> I even tried ditching the idea and doing an if and rewriting the whole
> select statement depending on @.Tmp. Only problem is it is the select
> statement for a cursor FOR statement, so fiddling around with that is tric
ky
> too !!!|||yes that is another solution. thanks. does exec (string) have speed
implications?
"Mike Austin" wrote:
> It looks like you are going to have to construct your SQL statement in a
> string variable and then use EXEC() to submit the string to the query pars
er.
> SET @.strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @.Tmp + ')'
> EXEC(@.strSQL)
> HTH,
> Mike
> "louise raisbeck" wrote:
>|||Certainly, this solution is slower than if you're able to come up with a way
to implement dynamic SQL in a fixed statement.
Another approach may be:
IF @.Tmp = 'Pig'
BEGIN
SELECT...
END
ELSE
BEGIN
SELECT...
END
"louise raisbeck" wrote:
> yes that is another solution. thanks. does exec (string) have speed
> implications?
> "Mike Austin" wrote:
>

CASE statement using wildcard

im trying to use wildcard in CASE statement with no luck.

im use this:

SELECT CASE tb001
WHEN '%price%' then 'Price'
ELSE 'No price'

the colum is varchar

The expected result would be 'Price' in every row that contains price (with wildcard around) but every line shows 'No price'
Everything works fine if i don't use wildcard and put in the whole string.

thx in advance //MrTry (not tested)
SELECT
CASE
WHEN tb001 like '%price%' then 'Price'
ELSE 'No price'
END
FROM ...|||works like charm... thx alot

Case Statement Using Table Alias, Not Possible ...?

Hello, how come I can't qualify a column with its table alias in a
case clause
this works:
select tasks.taskid,tasks.status,machinename=
case
when machinename is null ( select machine from queue where
taskid = tasks.taskid)
else machinename
end
from tasks
but when I change all machinename references to tasks.machinename
i get syntax errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'else'
For this simple query, ambiguous columns are not an issue, but when
they become one, how is one to use a case statement?"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>
You missed out the keyword "THEN" in your CASE expression.
Couldn't you do this with a JOIN? Try the following:
SELECT T.taskid, T.status,
COALESCE(T.machinename, Q.machine) AS machinename
FROM tasks AS T
LEFT JOIN queue AS Q
ON T.taskid = Q.taskid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Oh, yeah the then... at any rate, then 'then' wont fix the error.
I need a case because the query in question is part of a somewhat big
view that is now required to provide some additional "functionality".
Getting the case to work would be easier than rewriting it... well, if
it can work...
Any ideas as to why the qualifier causes it to fail?
Thanks,
Skye|||As David Portas pointed out, you need a THEN. But to avoid your error,
don't add the tasks. prefix to the machinename in the first line just before
the = sign. That is the name of the column in your result set, not the name
of the column in the tasks table. So you want:
select tasks.taskid,tasks.status,machinename=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
If for some reason, you wanted the name of the colum in the result set to be
tasks.machinename (and you almost certainly don't want that), you would have
to enclose it in single quotes, e.g.,
select tasks.taskid,tasks.status,'tasks.machinename'=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
Tom
"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>|||Tom Cooper wrote:
> As David Portas pointed out, you need a THEN. But to avoid your error,
> don't add the tasks. prefix to the machinename in the first line just befo
re
> the = sign. That is the name of the column in your result set, not the na
me
> of the column in the tasks table. So you want:
> select tasks.taskid,tasks.status,machinename=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
ahh, ok....

> If for some reason, you wanted the name of the colum in the result set to
be
> tasks.machinename (and you almost certainly don't want that), you would ha
ve
> to enclose it in single quotes, e.g.,
> select tasks.taskid,tasks.status,'tasks.machinename'=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
Thanks allot for your clarification, I actually just ended up aliasing
the case statement.
> Tom
> "MaggotChild" <hsomob1999@.yahoo.com> wrote in message
> news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...

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 usage?

I am trying to do this inside a stored procedure: Select list of ids which will use conditions, When a id is in another list of ids which retrieved from a table and limited by an dynamically chosen WHERE condition using CASE statement.

I do realize I can not use CASE statement because after keyword THEN, it must be a value, can not be a condition statement.

My code having syntax error are:

SELECT ...

FROM ...

WHERE ...

AND lav.ListingAttributeId IN (
SELECT listingAttributeId
FROM @.TempListingAttributeValuesTable
WHERE
CASE comparision
WHEN 'Between' THEN
lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT)
WHEN '=' THEN
lav.Value = CAST(attributeValue1 AS FLOAT)
WHEN '>' THEN
lav.Value > CAST(attributeValue1 AS FLOAT)
WHEN '<' THEN
lav.Value < CAST(attributeValue1 AS FLOAT)
WHEN '>=' THEN
lav.Value >= CAST(attributeValue1 AS FLOAT)
WHEN '<=' THEN
lav.Value <= CAST(attributeValue1 AS FLOAT)
END
)

Is there any other way I can select the search condition instead of using CASE?

Thank you.

WHERE
CASE
WHEN comparision = 'Between' AND lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT) THEN 1
WHEN comparision='=' AND lav.Value = CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>' AND
lav.Value > CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision = '<' AND lav.Value < CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>=' AND lav.Value >= CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparison ='<=' AND lav.Value <= CAST(attributeValue1 AS FLOAT) THEN 1
ELSE 0
END = 1

|||Thank you. It solved my question.

I am wondering the possibility of building WHERE condition dynamically?

I think it is impossible, but my mate told me I could do it in other ways, but it needs to restructure the query.

Anyone got idea?

Use the code I posted as an example, is it possible if I want to do something like:
SELECT *
FROM TableName
WHERE condition1 or condition 2 or condition 3 etc.

The number of condition is not fixed.

Thank you.
sql

case statement TSQL

I'm trying to do something like the following:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
ORDER BY
lead_id
This causes an error at the >. How do a dynamic where clause like this?
Thanks for any help"gl" <gl@.discussions.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
>
Well, before showing you how to do this, I'm obliged to warn you against it.
Multiple different queries should be presented to SQL Server seperately so
the server can make intelligent choices about how to execute the query.
Such a compound query usually requires a full scan to execute. But if you
break it up, one or more of the resulting queries may be very cheap:
if @.lead_id_lookup = 0 then
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id >= @.lead_id
ORDER BY
lead_id
end
else if @.lead_id_lookup > 0
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id = @.lead_id
ORDER BY
lead_id
end
Now, finally, to answer your question:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 AND lead_id >= @.lead_id THEN 1
WHEN @.lead_id_lookup > 0 AND lead_id = @.lead_id
THEN 1
ELSE 0 END = 1
ORDER BY
lead_id
David|||gl
untested
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
lead_id = CASE WHEN @.lead_id_lookup = 0 THEN
lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
END
ORDER BY
lead_id
"gl" <gl@.discussi
ons.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
> Thanks for any help|||SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
(@.lead_id_lookup = 0 AND lead_id >= @.lead_id)
OR (@.lead_id_lookup > 0 AND lead_id = @.lead_id )
ORDER BY
lead_id

Case Statement Really Urgent Please

Why will this case statement not work?
It checks the length of a char field TRAN_TIME and pads it accordingly.
SELECT a.TRAN_TIME
FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE = b.Policy_desc
AND a.TRAN_DT >= '20051221'
AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00' +
a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GEI
/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')or what is that padding function
lpad or something'
"marcmc" wrote:

> Why will this case statement not work?
> It checks the length of a char field TRAN_TIME and pads it accordingly.
> SELECT a.TRAN_TIME
> FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00'
+
> a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
EI/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')|||SELECT a.TRAN_TIME
FROM
QUINN_ST..get_bcp_h_cvcmis a(nolock),
QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE =3D b.Policy_desc
AND a.TRAN_DT >=3D '20051221'
AND (
CASE len(a.TRAN_TIME)
When 5 THEN '0' + a.TRAN_TIME
When 4 Then '00' + a.TRAN_TIME
ELSE a.TRAN_TIME END
) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GE=
=ADI/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')
Syntactically it looks OK, but there could be some conversion issue
depending on your datatypes of the table (which you didn=B4t sned in a
ddl script with the issue)
BTW: What do you mean by lpad ?
HTH, jens Suessmeyer.|||lpad was an oracle padding func i was thinking of, how can i pad one or two
zeroes to the left of lets say the char '93826 '
'
"Jens" wrote:

> SELECT a.TRAN_TIME
> FROM
> QUINN_ST..get_bcp_h_cvcmis a(nolock),
> QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (
> CASE len(a.TRAN_TIME)
> When 5 THEN '0' + a.TRAN_TIME
> When 4 Then '00' + a.TRAN_TIME
> ELSE a.TRAN_TIME END
> ) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
E_I/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')
> Syntactically it looks OK, but there could be some conversion issue
> depending on your datatypes of the table (which you didn′t sned in a
> ddl script with the issue)
> BTW: What do you mean by lpad ?
> HTH, jens Suessmeyer.
>|||e.g., pad resulting length of 6
right(replicate('0',6) + convert(varchar, '93826'), 6)
marcmc wrote:
> lpad was an oracle padding func i was thinking of, how can i pad one or tw
o
> zeroes to the left of lets say the char '93826 '
> '
> "Jens" wrote:
>|||Thats easy:
DECLARE @.SOMEVALUE VARCHAR(20)
SET @.SOMEVALUE = '998'
SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
PRINT @.SOMEVALUE
HTH, Jens Suessmeyer.|||ah - missed the char, have to trim it first
right(replicate('0',6) + convert(varchar, rtrim('93826 ')), 6)
Trey Walpole wrote:
> e.g., pad resulting length of 6
> right(replicate('0',6) + convert(varchar, '93826'), 6)
> marcmc wrote:
>|||combining Trey's and Jen's suggestions...
If your input is character data, you should probably trim spaces from both
sides unless you know and can rely on the incoming data.
RIGHT('000000' + LTRIM(RTRIM(@.SOMEVALUE)), 6)
if you need it to be dynamic, use the replicate version
RIGHT(REPLICATE('0', @.PadSize) + LTRIM(RTRIM(@.SomeValue)), @.PadSize)
if it's numeric data,
RIGHT(REPLICATE('0', @.PadSize) + CONVERT(VARCHAR, @.SomeNumeric), @.PadSize)
if you prefer CAST instead of CONVERT, that works too.
Hope that helps,
Joe
"Jens" wrote:

> Thats easy:
> DECLARE @.SOMEVALUE VARCHAR(20)
> SET @.SOMEVALUE = '998'
> SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
> PRINT @.SOMEVALUE
>
> HTH, Jens Suessmeyer.
>

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

Hi all

I am having a small problem with the case statement,
I have two table, a status table and users table ( i have scripted them below)

create table users
(id int
, user_name char (10) )

insert into users (id, user_name)
values ( 1, 'bob')
insert into users (id, user_name)
values ( 2, 'sue')
insert into users (id, user_name)
values ( 3, 'richard')
insert into users (id, user_name)
values ( 4, 'john')
insert into users (id, user_name)
values ( 5, 'wendy')

create table status
(name char (10)
, status int, sales_manager int, account_manager int)

insert into status (name, status, sales_manager)
values ('test1', 1, 1 )
insert into status (name, status, sales_manager)
values ('test2', 1, 2 )
insert into status (name, status, account_manager)
values ('test3', 2, 3 )
insert into status (name, status, account_manager)
values ('test4', 2, 4 )
insert into status (name, status)
values ('test5', 2 )

What i need to do when i run the below statement it gives me a list of the names
and the managers, if there is a null value returned i want it to display
'No manager assigned' or something like that

select s.name
, 'manager' = case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

thanks

Like this, use COALESCE or ISNULL

select status,s.name
, 'manager' = case
when status = 1 then coalesce(u1.user_name,'No manager assigned')
when status = 2 then coalesce(u2.user_name,'No manager assigned')
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can do below:

select s.name
, coalesce(case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end, 'No manager assigned') as manager
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Also, please don't use the 'column_alias' = expr syntax. This has been deprecated in SQL Server 2005 and will be removed in a future version of SQL Server. See link below for more details:

http://msdn2.microsoft.com/en-us/ms143729(SQL.90).aspx

|||Thanks guys for the answers, sorted!

Case Statement Problem

I am trying to convert this to sql syntax

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

Thank you

Your "Sum(td_hours) as SumOfHours" must be moved out of the CASE statement; something like this maybe:

Code Snippet

select case
when SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'PTO','PTO_Holiday')
when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'Holiday','PTO_Holiday')
ELSE task_name
end,
Sum(td_hours) AS SumOfHours

|||Thank you

CASE Statement Opinion

I have the following case statement:
CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
WHEN 0 THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
It works ok, but i'd like t oknow if there is a better way to do this. It
feels messy, but that doesn't mean there's a better way.
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesOr, you could write:
CASE when i.idxhstRSXOPos = i2.idxhstRSXOPos THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
Perayu
"WebBuilder451" wrote:

> I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||When populating a calculated column, typically for reporting purposes, I've
written embedded case expressions more twisted than that. An expression like
that probably won't be indexed by the query optimizer, but so long as you
are not placing it in the where or group by clause, I expect it would only
be a marginal performance hit. One other option would be to return
idxhstRSXOPos, i2.idxhstRSXOPos, and idxhstRSXOLastAct in the query result
and let client application do the calculation.
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:245254F2-39A7-4DB9-AF86-4B86D8869685@.microsoft.com...
>I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||It looks good to me. Nesting CASE expressions is legal as long as the
data types are correct. If you want to flatten it out, you can:
CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
END AS rsXOxoflg
CASE expressions evaluate the WHEN clauses in order. This will do the
same thing as yours.|||this is what i was looking for.
Not to say the other answers were not good.
Thank You everyone
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"--CELKO--" wrote:

> It looks good to me. Nesting CASE expressions is legal as long as the
> data types are correct. If you want to flatten it out, you can:
> CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
> WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
> WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
> END AS rsXOxoflg
> CASE expressions evaluate the WHEN clauses in order. This will do the
> same thing as yours.
>