Custom Column Print using 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

SQL Server 2000 Forums

Transact-SQL (2000)

Custom Column Print using SELECT STATEMENT

Author

Topic

rd_innovatives
Starting Member

18 Posts

Posted - 2006-11-24 : 09:46:19

Dear All,

I am facing a problem while trying to print a custom statement based on the value of the column ...

For e.g If I submit a SQl like
Select * from employees (I am using the NorthWind DB)
it will reflect all the rows ... its ok ... now there is a column named 'reportsto' .. here the manager id is noted for each employee and i want to show the string 'NOT APPLICABLE' where the 'reportsto' field is NULL ... by using the SELECT Statement , not using cursor or any other options ...
Hoping that i can make you understand what i am saying ...

Thanks in advance to you all .....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 10:15:46

[code]Select Col1, Col2, IsNull(reportsto, 'Not Applicable') as ReportsTo
From Employees[/code]

Harsh Athalye
India.
"Nothing is Impossible"

rd_innovatives
Starting Member

18 Posts

Posted - 2006-11-24 : 10:52:14

Hi harsh,

gr8!!!!!!!!! THANKS FOR THE REPLY ... i CAN USE isnull if the value is NULL and if not, let the value is not null and i want to implement IF statement ... Is it possible ?
Again thanks for the answer ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 10:59:27

You can't use IF statement, but you can use CASE statement like this:

Select 
Col1, Col2, 
Case When reportsto Is Null Then 'Not Applicable'
     else (case When SomeOtherCodition Then 'SomeValue' 
                ....
           End)
     End as ReportsTo
From Employees

Hope, I made it clear.

Harsh Athalye
India.
"Nothing is Impossible"

rd_innovatives
Starting Member

18 Posts

Posted - 2006-11-24 : 11:22:15

yesss .. thanks .... many thanks .... can i make you another question .. hope i am not boring you :-) ... I have tried IF .. but the sql failed to execute .. now my question ... whyy?? ISNULL, CASE can be used in SELECT Statement bt not CASE .. is there any rules or guidelines ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 12:55:22

Because CASE is a special type of function, while IF is a statement which can not be embedded in the DML statements.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-24 : 22:47:38

You can use if statement if you want to execute different statements based on the condition

IF @value=10
----some stuff
else
---other stuff

Note you can also use COALESCE in place of ISNULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources