How to test a value and provide a text result

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to test a value and provide a text result

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-12 : 12:32:17

Hello,

I am trying to write a select statement that not only selects multiple field values but can also test a particular field and based on the result, provide a text replacement.

Here is what I have now:

Select item_no As [Part No], work_order_no AS [WO No], stack_no AS [Stack No], auto_inspect As [Auto Inspected]
From prod_data
WHERE auto_insp_timestamp > Getdate()-1

And it returns:

Part No WO No Stack No Auto Inspected
ISI0032 1329 30018 1
ISI0032 1329 30019 1
ISI0080 1330 10020 NULL
ISI0080 1330 10022 NULL
ISI0080 1330 10026 1
ISI0080 1330 10027 1

The field in question here is auto_inspect. A result of 1 means this part was tested using an automated system. A NULL returned here means it was tested manually. here is what I would like to see instead:

Part No WO No Stack No Auto Inspected
ISI0032 1329 30018 AUTO
ISI0032 1329 30019 AUTO
ISI0080 1330 10020 MANUAL
ISI0080 1330 10022 MANUAL
ISI0080 1330 10026 AUTO
ISI0080 1330 10027 AUTO

Is there a way to test the the value and change (or Mask) the output based on a result of 1 or NULL ??

Any help you can provide is appreciated.

Chester
Starting Member

27 Posts

Posted - 2004-10-12 : 12:41:13

Use a case statement:

Select item_no As [Part No], work_order_no AS [WO No], stack_no AS [Stack No],
(case when auto_inspect = 1 then 'AUTO' else 'MANUAL' end)As [Auto Inspected]
From prod_data
WHERE auto_insp_timestamp > Getdate()-1

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-12 : 12:48:18

Thanks! thats got it....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources