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 Error in an Insert Statement

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.Blimey - lots of errors :)

1) 2 opening parentheses, 1 closing
2) Fields in the select not separated by commas
3) The area where you have your case statement is where you specify the destination fields. The bit after the select clause is where yuou define your data so...
4) 7 destination fields, 6 source fields

I advise you read INSERT in BoL. Construct your SQL without the case and only include it once you have it working.

HTH

EDIT - 4 is wrong - can't count :o|||That is some pretty messed up syntax. I strongly encourage you to (re-)read the BOL sections on INSERT statements and the CASE function.

This is the basic syntax of an INSERT statement. You can't perform logic in the column list; only in the SELECT clause.

I left out the CASE statement because it is unclear how you want it to work. Do you want it to reference model_ID?
Insert into myTblA
(TblA_ID,
alt_min,
alt_max,
longitude,
latitude)
Select MTB.LocationID,
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB|||Thanks for the input. Sorry about the "incompleteness" of the sql, I was trying to edit it down, in reality it is larger than what I posted.
In ref to the parentheses, they're all there in the original, I just missed putting it in the sample.
The fields in the select are separated by commas, except for the case portion and for information purposes I put commas after each Case statement but it still errors.
I do have a matching number of Select fields for my Insert, again, it's an error on my part when trying to just give the pertinent issues.
Lastly, I did run the sql statement w/o the case statement and it works (yeah, I know, it probably shouldn't but hey, I'm not going to complain).

I'll try to clean up the query and repost it to see if that helps in identifying why the case won't work.
Thanks for the input.|||Here is the sql again, this time I tried to ensure all the basic stuff is correct (i.e. matching selected to inserted, commas, etc).
Insert into operation
(LocationID,
instance_id =
case
when instance_id = 1 then 99861
when instance_id = 2 then 99862
when instance_id = 3 then 99863
when instance_id = 4 then 99864
when instance_id = 5 then 99865
when instance_id = 6 then 99866
when instance_id = 7 then 99867
when instance_id = 8 then 99868
when instance_id = 9 then 99855
when instance_id = 10 then 99839
end,
altitude_minimum,
altitude_maximum,
longitude,
latitude)
(
Select l.Locationid,
(SELECT Equipment.ModelID
FROM Assignment INNER JOIN Equipment ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location ON Assignment.LocationID = Location.LocationID
INNER JOIN Product ON Assignment.AssignmentID = ProductDataFile.AssignmentID),
l.elevation, --alt min
null, --alt max
l.longitude, --longitude
l.latitude --latitude
from Location l
);|||Hi

Point 3 still stands :)|||Hey PootleFlump, I don't think I follow your 3rd point but would like to clarify.
I'm well aware that the case statement is in the destination fields, I don't want to insert the value coming from the Selected Records, I need to change it (hence the case statement). Are you saying to move the case statement down into the Select?

Thanks for any assistance....|||You can't have a CASE statement in an INSERT column list...it needs to be in the SELECT

And since it doesn't make any sense, I don't know how to help|||Does INSTANCE ID = MODEL ID?|||Hey Brett,
Yes it does. Thanks for the input about the Case Statement not being permitted in an Insert. I'll look for another avenue for inserting the records from one table to another.

Thanks!|||This is a flat out, shot in the dark, but it could be what you need:INSERT INTO operation (
LocationID, instance_id, altitude_minimum
, altitude_maximum, longitude, latitude)
Select l.Locationid,
, (SELECT
CASE Equipment.ModelID
WHEN 1 THEN 99861
WHEN 2 then 99862
WHEN 3 then 99863
WHEN 4 then 99864
WHEN 5 then 99865
WHEN 6 then 99866
WHEN 7 then 99867
WHEN 8 then 99868
WHEN 9 then 99855
WHEN 10 then 99839
END
FROM Assignment
INNER JOIN Equipment
ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel
ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location
ON Assignment.LocationID = Location.LocationID
INNER JOIN Product
ON Assignment.AssignmentID = ProductDataFile.AssignmentID)
, l.elevation --alt min
, null --alt max
, l.longitude --longitude
, l.latitude --latitude
FROM Location AS l
);-PatP

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyFirst, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov|||Thank you all
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
First, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov
|||Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
sql

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyFirst, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov|||Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

CASE and INSERT statements

Hi,
I need to write a simple script which i am getting rather on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.

Carriage return within column alias

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

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

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

Hope this helps.

carriage return inside a field of text data type?

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

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

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

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

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

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

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

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

Carriage Return in Crystal Syntax

I am sure there is a solution already out there, but I couldn't find it. I am trying to insert a carriage return in some Crystal Syntax I am writing. I am doing the following

cstr(currentfieldvalue) + chr(13) + "FG"

and instead of displaying

"$300.00
FG"

it displays

"$300.00 FG"

Can someone help?Try sending a linefeed only
cstr(currentfieldvalue) + chr(10) + "FG"

or

sending a cr + linefeed
cstr(currentfieldvalue) + chr(13) + chr(10) + "FG"|||Try sending a linefeed only
cstr(currentfieldvalue) + chr(10) + "FG"

or

sending a cr + linefeed
cstr(currentfieldvalue) + chr(13) + chr(10) + "FG"

I tried both of those and it does not work. I have attached a screen shot of the coding area, maybe you can see what I am missing?

Thanks,
Michael|||Forgot to tell you that you have to check the Can Grow property
You can find it in FormatField / Common / CanGrow
Sorry!

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.
Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.
|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:
> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> > I'm using SQL Server 2005 Manage Studio to edit some varchar and text
> fields
> > I have in my database. I want to insert carriage returns in my text, and I
> > tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
> in
> > Management Studio. Further more, when I import data from my SQL 2000 and
> then
> > open the table for editing in Management Studio I find that all the
> carriage
> > returns are not visible. I say not visible, because I know they're still
> > there because my application reads them fine. Is this a UI bug in
> Management
> > Studio?!!! Anyone knows of a fix or work around? Thanks.
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work i
n
Management Studio. Further more, when I import data from my SQL 2000 and the
n
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in th
e
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.
Hi
Run SQL Server Profiler on the target server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:
> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideall
y
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing rows inserted from bulk insert

Is there any way to capture the count of rows processed by a bulk insert task?
A property perhaps?
I can run queries post import but would prefer it if there was a way to capture that number directly. It was something we had in the old DTS as the package ran. Anything we can do to discover it in SSIS?

Paul PisarekOne option is to enable the out-of-box logging to sysdtslog90 table - the components displays number of rows processed. It's in the 'message' text colum though, so you need to add a little bit of parsing to get your specific data.
KDog|||That's right. We're working on a sample that parses the string and creates a report from it, hopefully should be available to you soon. But, yes, parsing the log is the right approach.

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/