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

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

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 'Operator' in Where clause

i have a sp like this
--
@.operator int --(1=smaller,2=bigger)
@.price decimal(18,2)
select id,price
from company
where (price case @.operator when 1 then <@.price when 2 then >@.price end )
how can i accomplish this?
thnxdeclare @.operator int,
@.price decimal(18,2)
select ID, Price
from Company
where (price < @.price and @.operator = 1 )
and (price > @.price and @.operator = 2)
But if you have this in a Store procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price < @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price > @.price
end
Also with the logic that you have if the price is the same you will
never see it.
So you need to do this if was in procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price <= @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price >= @.price
end|||On Wed, 25 Jan 2006 13:38:01 -0800, henk wrote:

>i have a sp like this
>--
>@.operator int --(1=smaller,2=bigger)
>@.price decimal(18,2)
>select id,price
>from company
>where (price case @.operator when 1 then <@.price when 2 then >@.price end )
>--
>how can i accomplish this?
>thnx
Hi Henk,
IF @.operator = 1
SELECT id, price
FROM company
WHERE price < @.price
ELSE
SELECT id, price
FROM company
WHERE price > @.price
Hugo Kornelis, SQL Server MVP|||You can use either options as follows
declare @.operator int --(1=smaller,2=bigger)
declare @.price decimal(18,2)
set @.operator = 1 -- 2
set @.price = 10.05
-- Option A
declare @.cmd varchar(8000)
set @.cmd = 'select id, price from company where price '+ case @.operator when
1 then '< @.price' else '> @.price' end
exec (@.cmd)
-- Option B
if @.operator = 1
select id
,price
from company
where price < @.price
if @.operator = 2
select id
,price
from company
where price >@.price
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||thanks for your reply
i realy would like to do this in one statment without making use of if-else.
the reason behind this is that i have multiple operators for multiple
columns and it realy gets complex with 4 or 5 operators.
is it possible or do i have to use the if-else senario?
"Amiller" wrote:

> declare @.operator int,
> @.price decimal(18,2)
>
> select ID, Price
> from Company
> where (price < @.price and @.operator = 1 )
> and (price > @.price and @.operator = 2)
> But if you have this in a Store procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price < @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price > @.price
> end
>
> Also with the logic that you have if the price is the same you will
> never see it.
> So you need to do this if was in procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price <= @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price >= @.price
> end
>|||DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price - @.price > 0 THEN 2
WHEN price - @.price <= 0 THEN 1
END
= @.operator
The following was tested on Northwind
USE Northwind
GO
DECLARE @.operator int
DECLARE @.price money
SET @.operator = 1
SET @.price = 17.95
select productid, unitprice
FROM Products
WHERE
CASE
WHEN UnitPrice > @.price THEN 2
WHEN UnitPrice < @.price THEN 1
END = @.operator
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||I am not sure if the first statement will work i have never tried
something like that. I would go with the if else and comment all layers
so you know what one does what.|||thank you all for your helpfull replys.
i think i go with the Mark 's solution. that is exactly what i wanted.
thank you once again.
"Mark Williams" wrote:
> DECLARE @.operator int
> DECLARE @.price decimal (18,2)
> SET @.price = 17.99
> --To find items that are less than @.price, set @.operator to 1
> --To find items that are more than @.price, set @.operator to 2
> SELECT id, price
> FROM company
> WHERE
> CASE
> WHEN price - @.price > 0 THEN 2
> WHEN price - @.price <= 0 THEN 1
> END
> = @.operator
>
> The following was tested on Northwind
> USE Northwind
> GO
> DECLARE @.operator int
> DECLARE @.price money
> SET @.operator = 1
> SET @.price = 17.95
> select productid, unitprice
> FROM Products
> WHERE
> CASE
> WHEN UnitPrice > @.price THEN 2
> WHEN UnitPrice < @.price THEN 1
> END = @.operator
> --
>
> "henk" wrote:
>|||Just to clean things up
DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price > @.price THEN 2
WHEN price <= @.price THEN 1
END
= @.operator
Not sure why I did the whole price - @.price > 0 thing when the above is much
cleaner.
"henk" wrote:

> thank you all for your helpfull replys.
> i think i go with the Mark 's solution. that is exactly what i wanted.
> thank you once again.
>|||yeah i got it,
your the man man!!!!:)sql

CASE statement in WHERE clause problem

Hello,
I would like to achieve the following within an stored procedure.
SELECT * FROM TableX WHERE ID = 1
OR
SELECT * FROM TableX WHERE ID IS NOT NULL
How can I solve this by using a condition in my WHERE clause?
eg.
//////
CREATE PROCEDURE TestID
@.ID INT
AS
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE NOT NULL
END
////
The problem is the ' NOT ' NULL in the ELSE Path
If I skip the ELSE Path then it will be implicitly NULL
Thanks for any help,
RemcoHi,
Try this
SELECT * FROM TableX WHERE ID = 1 OR ID IS NOT NULL
Hth
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Hello Remco,

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
if you want criteria:
1. @.ID != null --> ID = @.ID
2. @.ID IS NULL --> ID IS NOT NULL
then:
WHERE
(@.ID IS NOT NULL AND ID = @.ID)
OR (@.ID IS NULL AND ID IS NOT NULL)
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B|||this should get you started on how to do that.
http://www.aspfaq.com/show.asp?id=2501
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||Try,
SELECT * FROM TableX
WHERE [ID] = @.id or (@.id is null and [id] is null)
AMB
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||
SET ANSI_NULLS ON
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE ID
END
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Reading your post again, I realized that what you want is:
select * from tablex
where ([id] = @.id) or (@.id is null and [id] is not null)
AMB
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM TableX
> WHERE [ID] = @.id or (@.id is null and [id] is null)
>
> AMB
> "Remco" wrote:
>|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...

> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
Possibly (untested):
SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in message
news:1fdb1$420b726d$432498ca$16254@.allth
enewsgroups.com...
> SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
I think that will fail if ID is null. Better is
SELECT * FROM TableX where ISNULL(ID, '') = ISNULL(@.ID,'')|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
SELECT * FROM TableX WHERE ID IS NOT NULL
satisfies this condition, but I'm gathering that's not what you want. :)

case statement in where clause

I need a SQL statement that selects a specific year (@.yr type int) in the "createddate" column...if this @.yr is equal to 0 then I want to select ALL columns regardless of the year...

This is what I have so far, but it doesnt work...

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE
WHEN @.yr<>'0' THEN @.yr
ELSE NOT NULL
END

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE WHEN (@.yr= 0) THEN year(CreatedDate) ELSE @.yr END

CASE statement in dynamic query

This query runs ok: SELECT Mission FROM tblProviders WHERE ProviderID =
'rnpo'
When I pass the @.providerID and @.WhichOne values dynamically to this stored
procedure:
DECLARE @.ProviderID varchar(15)
SET @.ProviderID = 'rnpo'
DECLARE @.WhichOne int
SET @.WhichOne = 1
DECLARE @.QRY varchar(150)
SET @.QRY = 'SELECT ' + CASE @.WhichOne
WHEN 1 THEN
'Mission'
WHEN 2 THEN
'History'
WHEN 3 THEN
'Services'
END
+ ' FROM tblProviders WHERE ProviderID = ''' + @.ProviderID + ''
EXEC @.QRY
This ERROR Message returned:
Server: Msg 2812, Level 16, State 62, Line 16
Could not find stored procedure 'SELECT Mission FROM tblProviders WHERE
ProviderID = 'rnpo'.
Note the ' preceding the SELECT.
Any recommendations for this stored procedure?
Thanks.
DanAdd the ( ):
EXEC(@.QRY)
Andrew J. Kelly SQL MVP
"Dan Slaby" <dslaby3@.comcast.net> wrote in message
news:OU%23x8Ms1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> This query runs ok: SELECT Mission FROM tblProviders WHERE ProviderID =
> 'rnpo'
> When I pass the @.providerID and @.WhichOne values dynamically to this
> stored procedure:
> DECLARE @.ProviderID varchar(15)
> SET @.ProviderID = 'rnpo'
> DECLARE @.WhichOne int
> SET @.WhichOne = 1
> DECLARE @.QRY varchar(150)
> SET @.QRY = 'SELECT ' + CASE @.WhichOne
> WHEN 1 THEN
> 'Mission'
> WHEN 2 THEN
> 'History'
> WHEN 3 THEN
> 'Services'
> END
> + ' FROM tblProviders WHERE ProviderID = ''' + @.ProviderID + ''
> EXEC @.QRY
> This ERROR Message returned:
> Server: Msg 2812, Level 16, State 62, Line 16
> Could not find stored procedure 'SELECT Mission FROM tblProviders WHERE
> ProviderID = 'rnpo'.
> Note the ' preceding the SELECT.
> Any recommendations for this stored procedure?
> Thanks.
> Dan
>

CASE statement help

I'm receiving a parameter@.
@.deletions AS CHAR(1)
I want to use this in a select case statement:
--
SELECT
columnA,
columnB,
columnC
FROM
tableA
WHERE
columnC = 'a',
AND
columnD = 'b'
CASE @.deletions
WHEN 'N' THEN AND (Catalogue_Product.Update_Code <> 'D')
END
--
It doesn't seem to like this case statement in the WHERE section...it's
happy with it in the SELECT section.
Any ideas?
Thanks
GriffLet's start off with the basics of programming. There is no CASE
statement in SQL; there is a CASE **expression**; expression return
values. You are still trying to write a procedural language and SQL is
a declarative language.
Guessing at what you meant, try something like this:
SELECT columnA, columnB, columnC
FROM TableA , Catalogue_Products AS P
WHERE columnC = 'a',
AND columnD = 'b'
AND CASE
WHEN @.deletions = 'N'
AND P.update_code <> 'D'
THEN 'N' ELSE 'Y' END = 'Y' ;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
>SQL is a declarative language.
Apologies for use of wrong terminology...
Okay, not sure I explained what I was attempting to do particularly well, so
attempt #2
I want my WHERE statement to behave in one of two ways:
EITHER
WHERE colC='a' AND colD='b'
OR
WHERE colC='a' AND colD='b' AND colE<>'d'
So, the WHERE statement will either have the "AND colE<>'d'" bit or it
won't.
I thought of using a SELECT EXPRESION based on an input parameter to control
this, but it doesn't appear to like it.
For example
WHERE
colC='a'
AND
colD='b'
CASE @.myInputParameter
WHEN 'n' THEN AND colE<>'d'
END
Griff|||DECLARE @.myInputParameter CHAR(1)
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND ((@.myInputParameter = 'N' AND columnE <> 'D')
OR (@.myInputParameter <> 'N'))
You'll probably get better performance out of it (index issues may surface
from using OR, <> and variables in your WHERE clause) if you split it up
into two possible queries based on an IF statement:
DECLARE @.myInputParameter CHAR(1)
IF @.myInputParameter = 'Y'
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
END
ELSE
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND columnE <> 'D'
END
"Griff" <Howling@.The.Moon> wrote in message
news:OMdEHmAUFHA.3840@.tk2msftngp13.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
> Apologies for use of wrong terminology...
> Okay, not sure I explained what I was attempting to do particularly well,
> so attempt #2
> I want my WHERE statement to behave in one of two ways:
> EITHER
> WHERE colC='a' AND colD='b'
> OR
> WHERE colC='a' AND colD='b' AND colE<>'d'
> So, the WHERE statement will either have the "AND colE<>'d'" bit or it
> won't.
> I thought of using a SELECT EXPRESION based on an input parameter to
> control this, but it doesn't appear to like it.
> For example
> WHERE
> colC='a'
> AND
> colD='b'
> CASE @.myInputParameter
> WHEN 'n' THEN AND colE<>'d'
> END
> Griff
>
>
>|||Direct translation into SQL:
SELECT columnA, columnB, columnC
FROM TableA
WHERE CASE WHEN colC =' a' AND colD =' b' AND @.my_switch = 'Y'
THEN 'Y'
WHEN colC =' a' AND colD =' b' AND colE<>'d'
AND @.my_switch = 'N'
THEN 'Y' ELSE 'N' END = ' Y";
You can factor out the common sub-expression predicates, but this might
be easier to read and maintain.sql

Case Statement

Hi!

I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way:

select

case

when(select column from table where conditions) is null then -1

else(select column from table where conditions)

But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"?

Thank you!

Try this:

select column = case column
when null then -1
else column
end
from table
where conditions

|||

You can write it like below which is ANSI SQL syntax:

select coalesce(column , -1) as column from table

COALESCE is just a short-hand for a special form of CASE expression like:

case when expr1 is not null then expr1

when expr2 is not null then expr2

...

end

Another proprietary TSQL method is to use isnull function:

select isnull(column, -1) as column from table

|||

Try either of

select coalesce(col, -1) from tab
go

select isnull(col, -1) from tab
go

Unfortunately, Allen's suggestion doesn't work because the "null" appearing in the when_expression causes it to always evaluate to false.

|||

Now it seems clever! :)

Thank you!!!

|||

Allen was almost there.

If you change this example slightly:

select column = case column
when null then -1
else column
end
from table
where conditions

to this..

select column = case
when column is null then -1
else column
end
from table
where conditions

..it'll work as expected

=;o)
/Kenneth

CASE Statement

Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @.Segment_Field = 'Acct_Status_' + CASE @.Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @.Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @.Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @.Segment_Field acc to the value of variables @.Bankruptcy_Type and @.Acct_Status. It should first check for @.Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @.Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

The problem is the null comparison. NULL <> NULL. You are trying to compare NULL = NULL, both being UNKNOWN values then it is unknown if they match. SQL Server comparisons are based on TRUE only, all others do not match.

declare @.value varchar(10)
--set @.value = 'Fred'
set @.value = NULL

select case @.value
when 'Fred' then 'It''s Fred'
when 'Barney' then 'It''s Barney'
when NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Just rewrite to a searched case expression:

declare @.value varchar(10)
--set @.value = 'Barney'
set @.value = NULL

select case when @.value = 'Fred' then 'It''s Fred'
when @.value = 'Barney' then 'It''s Barney'
when @.value is NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Note also that technically CASE is an expression, not a statement. Not that you used the CASE expression wrong, but it helps to remember that it evaluates to a scalar, not control of flow statements like in many other languages.

|||

Thanks Louis.

But my prob is that I have to check for value of two different variables and not one as you specified @.Value..I can definitley check for two different values of one variable but assigning value to a variable on basis of two different variables is what I am not able to do..

|||

Is this what you want? You can put as complex a condition in a WHEN clause as you need, even including subqueries...

SELECT @.Segment_Field = 'Acct_Status_' +

CASE when @.Public_record_Type is null then ''
when @.Public_record = 'BP' THEN 'BP' +
CASE when @.Bankruptcy_Type is null THEN ' ' ELSE '1' END +
CASE when @.Acct_Status is NOT NULL and @.Bankruptcy_Type is null THEN '2' ELSE '' END

Case sensitivity of SQL selects

Hi all
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface )
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> > Hi all,
> >
> > Can anybody tell me how to give a case sensitive select on a non case
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
> >
> > Thanks
> > Roshan
>sql

Case sensitivity of SQL selects

Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>

Case sensitivity of SQL selects

Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensit
ive instance of sql server .Is there any setting to be set up ( Which needs
to be done programtically not through the interface ) ?
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>