Replacing a value in a SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replacing a value in a SELECT statement

Author

Topic

jberberette
Starting Member

1 Post

Posted - 2010-02-03 : 16:32:30

Hi Everyone,

I've only been using SQL for 2 days now and have been tasked to create a report for our agency. I can get the requested data with no issues other that the fact that I cannot sort it properly.

In a nutshell we use a proprietary piece of software to track missions entered by our watch standers. The system basically the system creates a value (MissionID) when the mission is created...then if a change is made to the mission the system creates a new record and changes the [MissionID] value to the next available increment number.....then the original mission number is tracked via the [PrevMissionID] field.

example: MissionID=1000
an update to the mission is changed and now the MissionID=1001 and the PrevMissionID=1000 and so on.....

Now here's the weird part....when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts.

example: original mission entered....MissionID=1000 ---- a change is made so now.....MissionID=1001 & PrevMissionID=1000 ---- once the mission is marked complete.....MissionID=1000 & PrevMissionID=0

What I need to do in my query is basically an "If Then" type statement that basically checks to see if the [PrevMissionID]=0 if so changes the value to be equal with the [MissionID] value.

Basically if [PrevMissionID]=0 then replace [PrevMissionID] with the [missionID]. So If the PrevMissionID=0 and the MissionID=1000 then I want the PrevMissionID now equals 1000.

I hope this isn't to confusing and I truly appreciate your time.

Jack

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-03 : 17:12:39

select
MissionId,
case
when PrevMissionId=0 then MissionId
else PrevMissionId
end as PrevMissionId,
other columns...
from table
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:30:47

you need to update in table? then just do


UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0

tony_sql
Starting Member

1 Post

Posted - 2010-02-04 : 11:08:44

Some help here

[url]http://www.yourcodefactory.com/forum/topics.aspx?ForumID=22[/url]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 04:12:37

quote:
Originally posted by visakh16

you need to update in table? then just do

UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0

Nope, OP did not want to update.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 04:13:15

quote:
Originally posted by tony_sql

Some help here

<edit: unwanted link removed>

I can see nothing there to solve OP's problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 04:27:20

WebFred you may want to edit your quote to remove the link to not further advertise them (and in case the MODs delete the original post)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 04:39:45

Done - thank you

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 08:41:55

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

you need to update in table? then just do

UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0

Nope, OP did not want to update.

No, you're never too old to Yak'n'Roll if you're too young to die.

Basically if [PrevMissionID]=0 then replace [PrevMissionID] with the [missionID]. So If the PrevMissionID=0 and the MissionID=1000 then I want the PrevMissionID now equals 1000.

sounded like op needed to update value of [PrevMissionID] with the [missionID] when PrevMissionID=0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 08:50:23

quote:

...have been tasked to create a report for our agency...

...when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts...

Maybe I am wrong but that sounds to me that the OP should not do any update if he wants the application to run properly in the future...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 08:52:20

quote:
Originally posted by webfred

quote:

...have been tasked to create a report for our agency...

...when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts...

Maybe I am wrong but that sounds to me that the OP should not do any update if he wants the application to run properly in the future...

No, you're never too old to Yak'n'Roll if you're too young to die.

No idea

Let us keep guessing until OP comes back with explanation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources