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 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 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 in Order by clause

Hi

I need a case statement with in the order clause,

There are fields like below

Feild A FieldB LevelA LevelB

xxxx xxxx

xxxx xxxx

xxxx xxxx 3 2

xxxxx xxxx 2 1

xxxxxx xxxxx 1 1

I need to sort LevelA and LevelB first leaving out the null or blank values in the top rows

Feild A FieldB LevelA LevelB

xxxx xxxx 1 1

xxxx xxxx 2 1

xxxx xxxx 3 2

xxxxx xxxx

xxxxxx xxxxx

Thanks

like this:

Code Snippet

create table #t (FieldA varchar(10), FieldB varchar(10), LevelA int, LevelB int)

insert into #t

select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', 3, 2

union all select 'xxxxx', 'xxxx', 2, 1

union all select 'xxxxxx', 'xxxxx', 1, 1

union all select 'xxxxxx', 'xxxxx', 1, 2

union all select 'xxxxxx', 'xxxxx', 1, 3

select *

from #t

order by case when LevelA is not null then 1 else 9 end,

LevelA,

case when LevelB is not null then 1 else 9 end,

LevelB

|||

You can use a CASE expression like below:

order by coalesce(LevelA, power(2., 31)-1), coalesce(LevelB, power(2., 31)-1)

|||Umachandar,

Some readers might why you said "CASE expression," so I'll remark that COALESCE(a,b) is basically the same as CASE WHEN a IS NULL THEN b ELSE a END.

Sowree,

I think the suggestion below will work, and here I've added the requirement that blank values appear last. (If or are number types, they can't be blank, and this code will incorrectly put values of 0 last, because '' is implicitly converted to 0 in the comparison.)

ORDER BY
CASE WHEN a IS NULL OR a = '' THEN 1 ELSE 0 END,
a,
CASE WHEN b IS NULL OR b = '' THEN 1 ELSE 0 END,
b

Your question isn't entirely clear, so you may have to adapt the suggestions to your exact need.

Steve Kass
Drew University
http://www.stevekass.com

Case Statement & View

I am executing a case statement list below,

USE Northwind

SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(CASE YEAR(OrderDate)
WHEN 1996 THEN 1
ELSE 0
END) AS c1996,
SUM(CASE YEAR(OrderDate)
WHEN 1997 THEN 1
ELSE 0
END) AS c1997,
SUM(CASE YEAR(OrderDate)
WHEN 1998 THEN 1
ELSE 0
END) AS c1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

According to BOL I should be able to save this query as a view.
However when I try to save the query as a view I get a error message
stating

"View definition includes no output columns or includes no items in
the FROM clause"

According to what I have read although the case statement is not
supported via the enterprise query pane, the query should still run
and be saved. In my case however I cannot seem to save it no matter
what I try.

Can anyone shed any light on the matter?

Thanks in advance

BryanWhat version of SQL Server? I get the following error when I try to save
the query as a view using SQL 2000 SP3a:

The ORDER BY clause is invalid in views, inline functions,
derived tables, and subqueries, unless TOP is also specified.

It creates fine when I remove the ORDER BY. I then retrieved the results
using the following query

SELECT *
FROM MyView
ORDER BY OrderMonth

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bryan" <bryanmcguire@.btinternet.com> wrote in message
news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
>I am executing a case statement list below,
> USE Northwind
> SELECT
> MONTH(OrderDate) AS OrderMonth,
> SUM(CASE YEAR(OrderDate)
> WHEN 1996 THEN 1
> ELSE 0
> END) AS c1996,
> SUM(CASE YEAR(OrderDate)
> WHEN 1997 THEN 1
> ELSE 0
> END) AS c1997,
> SUM(CASE YEAR(OrderDate)
> WHEN 1998 THEN 1
> ELSE 0
> END) AS c1998
> FROM Orders
> GROUP BY MONTH(OrderDate)
> ORDER BY MONTH(OrderDate)
>
> According to BOL I should be able to save this query as a view.
> However when I try to save the query as a view I get a error message
> stating
> "View definition includes no output columns or includes no items in
> the FROM clause"
> According to what I have read although the case statement is not
> supported via the enterprise query pane, the query should still run
> and be saved. In my case however I cannot seem to save it no matter
> what I try.
> Can anyone shed any light on the matter?
> Thanks in advance
> Bryan|||I'm using SQL Server 2000 sp3

thanks

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan|||Dan,

Hi sorry, I just checked I was only running sp 1 not 3a as I posted
earlier. I have now changed to sp3a and ran the query again. This time
I manged to save the view by removing the "Order By"...

Thanks again

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan

Case in SqlServer

Hi,
I have a table and field below.

Payroll table
Id Period Net_pay
001 010105 5000
001 011605 300
001 030105 1000
001 040105 150
002 010105 1000
002 011505 500

I want the display like this if posible.. The first to character of the field period is the Month January to December.
Id January February March April ...........
001 5300 00 1000 150 ..........
002 1500 00 00 00 .............

Thank...You want a CROSSTAB query. Look it up in Books Online and you will get as good an explanation of how to do this as you could get on this forum.

Your only challenge should be converting the numeric string date format to the MonthName columns you want. You will need to do a CONVERT, CAST, or some other function, depending upon whether you have to worry about spanning years or not.|||Thanks Mr. Blindman for the reply...

case and group by problem

Hi I have this code below it gives me the right results but there are load o
f
null values. Is there a way to group by on a case statement to elimiinate
these nulls to smarten up ther report
select ,c.name ,c.custno,c.salesno, case catno
when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
from ius_cust c
inner join ius_detail d
on d.custno=c.custno
inner join ius_prods p
on p.prod=d.prod
where invdate > '2005-01-01'
group by c.name,c.custno,c.salesno,p.catno
thanks for any help
Sammychange the case catno
when 'L' then sum(qtyshp* netprice) end as
to case catno
when 'L' then sum(qtyshp* netprice) else 0 end as
put else 0 in between
http://sqlservercode.blogspot.com/
Sammy wrote:
> Hi I have this code below it gives me the right results but there are load
of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||Sammy
Can you show us your table's structure?
create table #test
(
col1 int,
col2 int,
col3 char(1)
)
insert into #test values (1,10,'h')
insert into #test values (1,40,'h')
insert into #test values (1,20,'s')
insert into #test values (2,20,'h')
insert into #test values (2,10,'h')
insert into #test values (2,850,'a')
select col1,sum(case when col3='h' then col2 end),
sum(case when col3='h' then col2 end)
from #test
group by col1
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:CE89E103-FD29-4B44-A12B-D10C7D836A13@.microsoft.com...
> Hi I have this code below it gives me the right results but there are load
> of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||The nulls are from empty result sets, so try try this to get zero
amounts
SUM (CASE cat_no WHEN 'n'
THEN (qty_shp* net_price)
ELSE 0.00 END) AS cat-N
Do not put the alias names in single quotes since that is proprietary
syntax that will screw up other tools and portability.|||On Wed, 21 Dec 2005 08:23:03 -0800, Sammy wrote:

>Hi I have this code below it gives me the right results but there are load
of
>null values. Is there a way to group by on a case statement to elimiinate
>these nulls to smarten up ther report
>select ,c.name ,c.custno,c.salesno, case catno
>when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
>when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
>when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
>when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
>from ius_cust c
>inner join ius_detail d
>on d.custno=c.custno
>inner join ius_prods p
>on p.prod=d.prod
>where invdate > '2005-01-01'
>group by c.name,c.custno,c.salesno,p.catno
Hi Sammy,
Try if this works:
SELECT c.name, c.custno, c.salesno,
SUM(CASE catno WHEN 'n' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-N",
SUM(CASE catno WHEN 'L' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-L",
SUM(CASE catno WHEN 'G' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-G",
SUM(CASE catno WHEN 'I' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-I"
FROM ius_cust AS c
INNER JOIN ius_detail AS d
ON d.custno = c.custno
INNER JOIN ius_prods AS p
ON p.prod = d.prod
WHERE invdate > '20050101' -- Note the recommended format
GROUP BY c.name, c.custno, c.salesno, p.catno
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Dec 2005 08:33:07 -0800, --CELKO-- wrote:

>The nulls are from empty result sets, so try try this to get zero
>amounts
> SUM (CASE cat_no WHEN 'n'
> THEN (qty_shp* net_price)
> ELSE 0.00 END) AS cat-N
>Do not put the alias names in single quotes since that is proprietary
>syntax that will screw up other tools and portability.
Hi Joe,
The alternative you recommend is portable - it results in an error on
ALL platforms. ;->
Try enclosing it in double quotes: ... AS "cat-N"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading path problem

Hello,
The following SQL code produces the famous multiple cascading paths problem.
How should I design the tables to have the below functionality, but keep the
cascading paths? A Doc doesn't necessarily have to be related to a Folder,
but must be related to a Cust.
Changing ON UPDATE to NO ACTION would solve it partly, but it just doesn't
feel right.
Thanks for any help!
cheers,
Jonah
CREATE TABLE Cust (
usr_name varchar(20) NOT NULL,
usr_pwd varchar(40) NOT NULL,
customer_name nvarchar(50) NOT NULL,
created_date datetime default getdate() NOT NULL,
change_date datetime default getdate() NOT NULL,
deactivate_date datetime default getdate() NULL
) ON [PRIMARY]
GO
ALTER TABLE Cust ADD CONSTRAINT
PK_Cust PRIMARY KEY CLUSTERED
(
usr_name
) ON [PRIMARY]
GO
CREATE TABLE Folders (
folder_id int NOT NULL ,
folder_name nvarchar(20) NOT NULL ,
folder_description nvarchar(150) NULL ,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
PK_Folders PRIMARY KEY CLUSTERED
(
folder_id
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
FK_Folders_Cust FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
GO
CREATE TABLE Docs (
doc_id int NOT NULL,
header nvarchar(255) not null,
created_date datetime default getdate() NOT NULL,
updated_date datetime default getdate() NOT NULL,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
PK_Docs PRIMARY KEY CLUSTERED
(
doc_id
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
FK_Cust_Docs FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
ON DELETE NO ACTION
GO
CREATE TABLE DocsInFolders
(
doc_id int NOT NULL,
folder_id int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
PK_DocsInFolders PRIMARY KEY CLUSTERED
(
doc_id,
folder_id
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Folders FOREIGN KEY
(
folder_id
) REFERENCES Folders
(
folder_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Docs FOREIGN KEY
(
doc_id
) REFERENCES Docs
(
doc_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GOYou say 'A Doc *doesn't necessarily have to be* related to a Folder,
but *must be* related to a Cust.'
Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
relationship in procedures. You make it sound as if the importance of the
Cust<--Docs relationship supercedes the importance of the Folders<--Docs
relationship.
Vital relationships should not be cascading.
ML|||Correct. The Cust<--Docs relationship is more importance, but that's why I
have only ON UPDATE CASCADE (to make possible usr_name changes up to date)
and not ON DELETE because I don't want Custs having Docs deleted by mistake.
An SP takes care of that.
So what you are saying is that I should change the ON CHANGE to NO ACTION as
well?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:601F4976-E669-4865-9D92-FBAF9A16CA8F@.microsoft.com...
> You say 'A Doc *doesn't necessarily have to be* related to a Folder,
> but *must be* related to a Cust.'
> Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
> relationship in procedures. You make it sound as if the importance of the
> Cust<--Docs relationship supercedes the importance of the Folders<--Docs
> relationship.
> Vital relationships should not be cascading.
>
> ML|||If usr_name can be changed then using it as a primary key (and/or referencin
g
it from a foreign key table) is really bad practice. Either disallow usr_nam
e
changes or use a better kandidate key.
I wouldn't allow cascades for this one.
ML|||OK. So if I disallow cascades for usr_name, you would consider the design to
be correct?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:90F300A3-7104-4924-A594-E28AC05D645D@.microsoft.com...
> If usr_name can be changed then using it as a primary key (and/or
> referencing
> it from a foreign key table) is really bad practice. Either disallow
> usr_name
> changes or use a better kandidate key.
> I wouldn't allow cascades for this one.
>
> ML|||As far as I can see, the design is fine. I would, however, do something abou
t
the Folders and Docs entities. Right now you allow a single document to exis
t
in more than one folder, which can lead to problems. The same goes for
folders - you should focus on preventing circular references.
Oh, and if any given document cannot exist in more than one folder, then the
DocsInFolders table is obsolete. You could simply add a nullable folder_id
foreign key to the Docs table (nullable since you've mentioned that a
document need not exist in any folder).
I hope you started on paper. :) And in case you haven't, maybe you'll do it
next time.
ML|||My design question applied to the cascading paths, not the business rules
themselves. One Doc may actually exist in several Folders.
- One Cust may have zero or more Folders
- One Cust may have zero or more Docs not connected to a Folder
- One Folder may have zero or more Docs related
Thus, my question was only related to if there's a better way of designing
the relations and tables to avoid circular references, which now occurs.
FYI, I didn't start on paper. I use Visio.
Thank you,
Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:F981A87A-893F-4940-9EBD-566C385CF1CB@.microsoft.com...
> As far as I can see, the design is fine. I would, however, do something
> about
> the Folders and Docs entities. Right now you allow a single document to
> exist
> in more than one folder, which can lead to problems. The same goes for
> folders - you should focus on preventing circular references.
> Oh, and if any given document cannot exist in more than one folder, then
> the
> DocsInFolders table is obsolete. You could simply add a nullable folder_id
> foreign key to the Docs table (nullable since you've mentioned that a
> document need not exist in any folder).
> I hope you started on paper. :) And in case you haven't, maybe you'll do
> it
> next time.
>
> ML|||No pun intended.
You are right - there is a better way to avoid circular references. You
might find more answers studying trees and hierarchies. Consider this model:
ItemInstance : ItemID : BelongsToInstance : ItemType
ItemInstance is unique.
ItemID can be either cust_id, folder_id or doc_id.
BelongsToInstance is a foreign key referencing ItemInstance.
ItemType designates whether ItemID is customer, folder or document.
Valid relationships are:
1) Customer/Folder/Document
2) Customer/Document
3) Folder/Folder (<-- not sure about this one, but seems logical, however:
parent folder_id must should be equal to child folder_id).
A customer can only exist as a root element (BelongsToInstance is null).
A Document can only exist as a leaf element (its ItemInstance is never
referenced in a BelongsToInstance).
The above constraints could be reinforced through the use of indexed views.
ItemInstance and BelongsToInstance prevent circular references while still
allowing all possible relationships between the three entities.
ML|||Thank you for your detailed answer.
I do have a copy of a trees and hierarchies book which I could take a closer
look into (Joe Celko's Trees and Hierarchies in SQL for Smarties). Maybe I
can find some more answers and examples there.
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:D5B9F5D8-4790-4088-BF18-14DE5119891D@.microsoft.com...
> No pun intended.
> You are right - there is a better way to avoid circular references. You
> might find more answers studying trees and hierarchies. Consider this
> model:
> ItemInstance : ItemID : BelongsToInstance : ItemType
> ItemInstance is unique.
> ItemID can be either cust_id, folder_id or doc_id.
> BelongsToInstance is a foreign key referencing ItemInstance.
> ItemType designates whether ItemID is customer, folder or document.
> Valid relationships are:
> 1) Customer/Folder/Document
> 2) Customer/Document
> 3) Folder/Folder (<-- not sure about this one, but seems logical, however:
> parent folder_id must should be equal to child folder_id).
> A customer can only exist as a root element (BelongsToInstance is null).
> A Document can only exist as a leaf element (its ItemInstance is never
> referenced in a BelongsToInstance).
> The above constraints could be reinforced through the use of indexed
> views.
> ItemInstance and BelongsToInstance prevent circular references while still
> allowing all possible relationships between the three entities.
>
> ML|||What about using a nested sets model for the hierarchy? I am not a big
fanof misxed node trees, but this case is pretty easy:
1) If it is the root node, it is a Customer
2) If it is a leaf node, it is a document
3) other it is a folder
CREATE TABLE DocumentHierarchy
(customer_id INTEGER NOT NULL
REFERENCES Customers
ON UPDATE CASCADE
ON DELETE CASCADE,
folder_id INTEGER -- null means no folder
REFERENCES Folders (folder_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
document_id INTEGER -- null means no document
REFERENCES Documents(doc_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
lft INTEGER NOT NULL CHECK (lft >0),
rgt INTEGER NOT NULL CHECK (rgt >lft),
PRIMARY KEY (customer_id, lft, rgt));
untested. You can also get a copy of TREES & HIERARCHIES IN SQL for
more ideas.

Capturing SQL Data via Triggers

Good afternoon!

The code below only captures previous recorded profile information but not the profile information currently being saved.

How can I correct this issue?

TRIGGER INFORMATION:

USE heat601
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trk_profile' AND type = 'TR')
DROP TRIGGER trk_profile
GO
CREATE TRIGGER trk_profile
ON profile
FOR INSERT
AS
DECLARE @.vTracker varchar(10), @.vTrackername varchar (25), @.vAuditDate varchar(10), @.vCustId varchar(10)

SELECT @.vTracker = profile.Tracker, @.vTrackername = calllog.RbyFullName, @.vAuditDate = profile.DTLMod, @.vCustId = profile.CustId
From profile JOIN calllog On profile.custid = calllog.custid

Insert Into AuditTrail Values(@.vTracker,@.vTrackername,@.vAuditDate,@.vCusti d)

EXEC master..xp_sendmail 'tward@.caremark.com',
'HEAT CMS customer profile is currently being manipulated.'
GODo U use the Temporary Inserted & Deleted Tables ?

Capture Execution Time Then Rollback Transaction

I would like to execute a stored procedure listed below then capture or
print out the execution time. Lastly rollback the transaction so that data
does not change in the database.
dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
Please help me with this procedure.
Thanks,Joe
See if this helps you
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
rollback
select datediff(ss,@.dt,getdate())
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:F6B145E9-8520-4DF3-95ED-1E4BC20E5DFE@.microsoft.com...
> I would like to execute a stored procedure listed below then capture or
> print out the execution time. Lastly rollback the transaction so that
> data
> does not change in the database.
> dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
> Please help me with this procedure.
> Thanks,|||Uri Dimant (urid@.iscar.co.il) writes:
> Joe
> See if this helps you
> declare @.dt datetime
> set @.dt =getdate()
> begin tran
> --do something here
> rollback
> select datediff(ss,@.dt,getdate())
That's not good. You need to do:
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
select datediff(ss,@.dt,getdate())
rollback
ROLLBACK can take considerable time and should not be measured.
Also, in many situations, ms (milliseconds) is better than ss (seconds).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Capacity planning question

I am working on a RFI for a SQL Server 2000 database apllication, I am
looking for some general answer the question below:
Capacity and resource planning for SQL Server 2000
1) resource requirements for 500, 1000, 2000 concurrent users (database,
memory, CPU, etc.)
2) deployment requirements for 500, 1000, 2000 concurrent users (server
configuration, architecture model, etc.)You will probably get very little, except it depends on the transaction, are
they reads, or writes? .. Do they use transaction control or not, how long
are the transactions, etc.
Other than that.
SQL loves memory.
More processors are better (Generally even if they are slower) than fewer
faster processors.
Multi-core processors are good
More on-board cache is good.
Keep your transaction logs mirrored on different drives than your data
Configure disk not only for space but for throughput - you might need more
disk heads to carry the volume, even if you have enough space with fewer
drives.
Just some general guidelines.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"George Kwong" wrote:
> I am working on a RFI for a SQL Server 2000 database apllication, I am
> looking for some general answer the question below:
> Capacity and resource planning for SQL Server 2000
> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
> memory, CPU, etc.)
> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> configuration, architecture model, etc.)
>
>|||I will add, that for an installation with those projected sizes and issues,
if you do not bring in someone with adequate experience to assist in the
design, planning, and deployment, you will be making a major mistake.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:O15VKVplGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am working on a RFI for a SQL Server 2000 database apllication, I am
> looking for some general answer the question below:
> Capacity and resource planning for SQL Server 2000
> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
> memory, CPU, etc.)
> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> configuration, architecture model, etc.)
>
>|||We developed the applcation under VB, we are trying to bid on a customer's
job. is there a way to do some test to find out the resource usage?
No, we use very minimum transaction controls. transaction are relative
small, we do both read and writes.
thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> You will probably get very little, except it depends on the transaction,
> are
> they reads, or writes? .. Do they use transaction control or not, how long
> are the transactions, etc.
> Other than that.
> SQL loves memory.
> More processors are better (Generally even if they are slower) than fewer
> faster processors.
> Multi-core processors are good
> More on-board cache is good.
> Keep your transaction logs mirrored on different drives than your data
> Configure disk not only for space but for throughput - you might need more
> disk heads to carry the volume, even if you have enough space with fewer
> drives.
> Just some general guidelines.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "George Kwong" wrote:
>> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> looking for some general answer the question below:
>> Capacity and resource planning for SQL Server 2000
>> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
>> memory, CPU, etc.)
>> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
>> configuration, architecture model, etc.)
>>
>>|||Hi George
Are you able to benchmark other customers' installations of your application
& project the performance characteristics from those installations against
the one you're bidding on?
I'd be tracking various perfmon counters & SQL diagnostics for this,
including at least:
Perfmon:
SQLBufferManager counter object, especially Buffer Page Life Expectancy to
determine memory characteristics
CPU Utilisation - collect system wide counter & also the sqlservr process'
CPU utilisation counter
Physical & Logical disk counters - expecially disk bytes read / write p/sec
& disk queues
There are other useful counters, but these are fundamental to pulling
together an informative picture on how your existing installations are
operating under specific hardware specs.
I'd also be taking a close look at how SQL Server is using memory
internally, using dbcc memorystatus to ensure you understand how your
system's using memory.
Performing some SQL Traces might also help you to ensure your application is
well tuned, which is important when drawing benchmark conclusions.
HTH
Regards,
Greg Linwood
SQL Server MVP
"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> We developed the applcation under VB, we are trying to bid on a customer's
> job. is there a way to do some test to find out the resource usage?
> No, we use very minimum transaction controls. transaction are relative
> small, we do both read and writes.
> thanks.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
>> You will probably get very little, except it depends on the transaction,
>> are
>> they reads, or writes? .. Do they use transaction control or not, how
>> long
>> are the transactions, etc.
>> Other than that.
>> SQL loves memory.
>> More processors are better (Generally even if they are slower) than fewer
>> faster processors.
>> Multi-core processors are good
>> More on-board cache is good.
>> Keep your transaction logs mirrored on different drives than your data
>> Configure disk not only for space but for throughput - you might need
>> more
>> disk heads to carry the volume, even if you have enough space with fewer
>> drives.
>> Just some general guidelines.
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "George Kwong" wrote:
>> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> looking for some general answer the question below:
>> Capacity and resource planning for SQL Server 2000
>> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
>> memory, CPU, etc.)
>> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
>> configuration, architecture model, etc.)
>>
>>
>|||"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> We developed the applcation under VB, we are trying to bid on a customer's
> job. is there a way to do some test to find out the resource usage?
>
Yes. MS Press had a book on this for SQL 2000 and I assume there is one for
SQL 2005.
> No, we use very minimum transaction controls. transaction are relative
> small, we do both read and writes.
>
Well, fisrt pass, figure, "how many bytes will be read and written" for each
transaction.
How many transactions/sec do you need to cover?
Things like indices may greatly impact that. As will caching.
But first pass, it can give you a sense of stuff like disk I/o which is
generally the slowest part of a system.
If you're reading/writing say 100 bytes/transaction and doing 100/sec, well
you need 10,000 byte throughput on your disks.
This ain't much.
If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
another kettle of fish.
> thanks.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> > You will probably get very little, except it depends on the transaction,
> > are
> > they reads, or writes? .. Do they use transaction control or not, how
long
> > are the transactions, etc.
> >
> > Other than that.
> >
> > SQL loves memory.
> > More processors are better (Generally even if they are slower) than
fewer
> > faster processors.
> > Multi-core processors are good
> > More on-board cache is good.
> > Keep your transaction logs mirrored on different drives than your data
> > Configure disk not only for space but for throughput - you might need
more
> > disk heads to carry the volume, even if you have enough space with fewer
> > drives.
> >
> > Just some general guidelines.
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "George Kwong" wrote:
> >
> >> I am working on a RFI for a SQL Server 2000 database apllication, I am
> >> looking for some general answer the question below:
> >>
> >> Capacity and resource planning for SQL Server 2000
> >>
> >> 1) resource requirements for 500, 1000, 2000 concurrent users
(database,
> >> memory, CPU, etc.)
> >> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> >> configuration, architecture model, etc.)
> >>
> >>
> >>
> >>
>|||it is actually the nature of my program worrys me. because, my program does
not have a complex transaction requirement, but the most significant part of
my program is writing and reading binary data, namely, a photo graph, it is
typically at about 35- 50 k each binary file (it is a jpeg image). this in
term will make all my other data type not significant by comparison
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uw1ZHaylGHA.3752@.TK2MSFTNGP02.phx.gbl...
> "George Kwong" <geokwo@.Lexingtontech.com> wrote in message
> news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
>> We developed the applcation under VB, we are trying to bid on a
>> customer's
>> job. is there a way to do some test to find out the resource usage?
> Yes. MS Press had a book on this for SQL 2000 and I assume there is one
> for
> SQL 2005.
>> No, we use very minimum transaction controls. transaction are relative
>> small, we do both read and writes.
> Well, fisrt pass, figure, "how many bytes will be read and written" for
> each
> transaction.
> How many transactions/sec do you need to cover?
> Things like indices may greatly impact that. As will caching.
> But first pass, it can give you a sense of stuff like disk I/o which is
> generally the slowest part of a system.
> If you're reading/writing say 100 bytes/transaction and doing 100/sec,
> well
> you need 10,000 byte throughput on your disks.
> This ain't much.
> If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
> another kettle of fish.
>
>> thanks.
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
>> > You will probably get very little, except it depends on the
>> > transaction,
>> > are
>> > they reads, or writes? .. Do they use transaction control or not, how
> long
>> > are the transactions, etc.
>> >
>> > Other than that.
>> >
>> > SQL loves memory.
>> > More processors are better (Generally even if they are slower) than
> fewer
>> > faster processors.
>> > Multi-core processors are good
>> > More on-board cache is good.
>> > Keep your transaction logs mirrored on different drives than your data
>> > Configure disk not only for space but for throughput - you might need
> more
>> > disk heads to carry the volume, even if you have enough space with
>> > fewer
>> > drives.
>> >
>> > Just some general guidelines.
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it''s
>> > community of SQL Professionals.
>> >
>> >
>> > "George Kwong" wrote:
>> >
>> >> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> >> looking for some general answer the question below:
>> >>
>> >> Capacity and resource planning for SQL Server 2000
>> >>
>> >> 1) resource requirements for 500, 1000, 2000 concurrent users
> (database,
>> >> memory, CPU, etc.)
>> >> 2) deployment requirements for 500, 1000, 2000 concurrent users
>> >> (server
>> >> configuration, architecture model, etc.)
>> >>
>> >>
>> >>
>> >>
>>
>|||"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:%23oAEynOmGHA.492@.TK2MSFTNGP05.phx.gbl...
> it is actually the nature of my program worrys me. because, my program
does
> not have a complex transaction requirement, but the most significant part
of
> my program is writing and reading binary data, namely, a photo graph, it
is
> typically at about 35- 50 k each binary file (it is a jpeg image). this in
> term will make all my other data type not significant by comparison
Well, still basically the same. Figure out how often you'll read/write
those images and calculate from there.
BTW, many people prefer to store images in the file system, not the DB.
There's arguments either way.
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:uw1ZHaylGHA.3752@.TK2MSFTNGP02.phx.gbl...
> >
> > "George Kwong" <geokwo@.Lexingtontech.com> wrote in message
> > news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> >> We developed the applcation under VB, we are trying to bid on a
> >> customer's
> >> job. is there a way to do some test to find out the resource usage?
> >>
> >
> > Yes. MS Press had a book on this for SQL 2000 and I assume there is one
> > for
> > SQL 2005.
> >
> >> No, we use very minimum transaction controls. transaction are relative
> >> small, we do both read and writes.
> >>
> >
> > Well, fisrt pass, figure, "how many bytes will be read and written" for
> > each
> > transaction.
> >
> > How many transactions/sec do you need to cover?
> >
> > Things like indices may greatly impact that. As will caching.
> >
> > But first pass, it can give you a sense of stuff like disk I/o which is
> > generally the slowest part of a system.
> >
> > If you're reading/writing say 100 bytes/transaction and doing 100/sec,
> > well
> > you need 10,000 byte throughput on your disks.
> >
> > This ain't much.
> >
> > If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
> > another kettle of fish.
> >
> >
> >> thanks.
> >>
> >>
> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> >> > You will probably get very little, except it depends on the
> >> > transaction,
> >> > are
> >> > they reads, or writes? .. Do they use transaction control or not, how
> > long
> >> > are the transactions, etc.
> >> >
> >> > Other than that.
> >> >
> >> > SQL loves memory.
> >> > More processors are better (Generally even if they are slower) than
> > fewer
> >> > faster processors.
> >> > Multi-core processors are good
> >> > More on-board cache is good.
> >> > Keep your transaction logs mirrored on different drives than your
data
> >> > Configure disk not only for space but for throughput - you might need
> > more
> >> > disk heads to carry the volume, even if you have enough space with
> >> > fewer
> >> > drives.
> >> >
> >> > Just some general guidelines.
> >> > --
> >> > Wayne Snyder MCDBA, SQL Server MVP
> >> > Mariner, Charlotte, NC
> >> >
> >> > I support the Professional Association for SQL Server ( PASS) and
it''s
> >> > community of SQL Professionals.
> >> >
> >> >
> >> > "George Kwong" wrote:
> >> >
> >> >> I am working on a RFI for a SQL Server 2000 database apllication, I
am
> >> >> looking for some general answer the question below:
> >> >>
> >> >> Capacity and resource planning for SQL Server 2000
> >> >>
> >> >> 1) resource requirements for 500, 1000, 2000 concurrent users
> > (database,
> >> >> memory, CPU, etc.)
> >> >> 2) deployment requirements for 500, 1000, 2000 concurrent users
> >> >> (server
> >> >> configuration, architecture model, etc.)
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>

can't use identity column in where clause.

Hello,
Can anybody tell me why I can't use the identity column in the where clause
of the openxml query below? I am getting the following error "Invalid column
name 'DepId' ", but if I change the column "DepId" to not be an identity, the
query compiles.
********************Query************************* ********
CREATE PROCEDURE ESS_UpdateEmergencyCnt
@.emgData as nvarchar(4000)
AS
Declare @.hDoc int
DECLARE @.ReturnCode INT
exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
update prdepend
set prdepend.name = XMLPRDEPEND.name
from OPENXML(@.hDoc, '/ROOT/Contact')
with prdepend XMLPRDEPEND
where prdepend.DepId = XMLPRDEPEND.DepId
Exec sp_xml_removedocument @.hDoc
GO
This is a restriction of the OpenXML WITH clause since IDENTITY values are
generated automatically and we do not know, whether you want it to take from
the XML document or not.
The solution is to give an explicit WITH clause (instead of using the table
name).
Best regards
Michael
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
> Hello,
> Can anybody tell me why I can't use the identity column in the where
> clause
> of the openxml query below? I am getting the following error "Invalid
> column
> name 'DepId' ", but if I change the column "DepId" to not be an identity,
> the
> query compiles.
> ********************Query************************* ********
> CREATE PROCEDURE ESS_UpdateEmergencyCnt
> @.emgData as nvarchar(4000)
> AS
> Declare @.hDoc int
> DECLARE @.ReturnCode INT
> exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
> update prdepend
> set prdepend.name = XMLPRDEPEND.name
> from OPENXML(@.hDoc, '/ROOT/Contact')
> with prdepend XMLPRDEPEND
> where prdepend.DepId = XMLPRDEPEND.DepId
> Exec sp_xml_removedocument @.hDoc
> GO
>
|||Hi Michael,
Thank you for the response. However, I am not sure what you mean by use an
explicit with clause instead of the table name. I already have a with clause
in the query. Would you mind giving me an example of what you mean?
thanks,
Sher
"Michael Rys [MSFT]" wrote:

> This is a restriction of the OpenXML WITH clause since IDENTITY values are
> generated automatically and we do not know, whether you want it to take from
> the XML document or not.
> The solution is to give an explicit WITH clause (instead of using the table
> name).
> Best regards
> Michael
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
>
>
|||If your table prdepend looks like (key int identity, foo nvarchar(50), bar
int)
then instead of saying
WITH prdepend
say
WITH (
foo nvarchar(50).
bar int)
if you do not have values for key or
WITH (
key int,
foo nvarchar(50),
bar int)
if you have.
Best regards
Michael
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thank you for the response. However, I am not sure what you mean by use an
> explicit with clause instead of the table name. I already have a with
> clause
> in the query. Would you mind giving me an example of what you mean?
> thanks,
> Sher
> "Michael Rys [MSFT]" wrote:
|||Thanks Michael. It worked.
regards,
SA
"Michael Rys [MSFT]" wrote:

> If your table prdepend looks like (key int identity, foo nvarchar(50), bar
> int)
> then instead of saying
> WITH prdepend
> say
> WITH (
> foo nvarchar(50).
> bar int)
> if you do not have values for key or
> WITH (
> key int,
> foo nvarchar(50),
> bar int)
> if you have.
> Best regards
> Michael
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...
>
>