SIMPLE --> Default value in result set 3

[DDR]

Hi all. If I have A query like

select field1, field2, field3
from table1
where field1 = 'value'

how do I assign default values to the the result set (like 'default1') in case that field1 is null.

 

[DangerPowers]

To set a value when a column is null, use the IsNull() function:

select field1, IsNull(field2,'Default2') as field2, field3
from table1
where field1='value'

 

[DDR]

Thanks for the Response DangerPowers but I would like a default value for the records of the query not the columns

 

[tlbroadbent]

What do you mean by "a default value for the records of the query not the columns?" Is this what you mean?

select
field1=Isnull(field1,'default1'),
field2,
field3
from table1
where field1 = 'value'
Or field1 is null Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jschaddock]

I think I know what you mean. Try this:

SELECT field1 = CASE When table1.field1 is NULL Then 'default1'
Else field1
END,
field2,
field3
FROM table1

The where clause that you have (where field1 = 'value') makes it impossible for the results set to return records with field1 set as null. However, if you just want all records returning and then a default value setting for field1 then the above code will do the job.

 

[tlbroadbent]

Isnull(field1,'default1') produces the same result as CASE When field1 is NULL Then 'default1' Else field1. I'd opt to use the simpler query.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DDR]

Thanks for the response guys
I 've tried both of the suggestions but this is what I get

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

this an example where the error is

SELECT sls.mk_ttdsls040925.t_orno =
isnull(sls.mk_ttdsls040925.t_orno,'100000'), com.mk_ttccom001925.t_emno,
com.mk_ttccom001925.t_nama
FROM mk_ttdsls040925 sls
INNER JOIN mk_ttccom001925 com ON
sls.mk_ttdsls040925.t_crep = com.mk_ttccom001925.t_emno
order by sls.mk_ttdsls040925

 

[sunila7]

Hi,

try this.... before the "=" u need put table.fieldname, just put what ever name u want for that particular field

SELECT t_orno =
isnull(sls.mk_ttdsls040925.t_orno,'100000'), com.mk_ttccom001925.t_emno,
com.mk_ttccom001925.t_nama
FROM mk_ttdsls040925 sls
INNER JOIN mk_ttccom001925 com ON
sls.mk_ttdsls040925.t_crep = com.mk_ttccom001925.t_emno
order by sls.mk_ttdsls040925

Sunil

 

[DDR]

Ups! this is what I meant to post!!

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

this an example where the error is

SELECT sls.t_orno =
isnull(sls.t_orno,'100000'), com.t_emno,
com.t_nama
FROM mk_ttdsls040925 sls
INNER JOIN mk_ttccom001925 com ON
sls.t_crep = com.t_emno
order by sls.t_orno

 

[sunila7]

Hi,

did u try my previous post......


Sunil

 

[DDR]

Thanks sunila that worked.
sorry guys for being a pain in the neck
but thats common on newbies |