Inserting Nulls into a temp table

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

Inserting Nulls into a temp table

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-03-03 : 16:18:57

I'm trying to insert any null values into a temp table. I'm storing the ixo_rlt_code as OldRole, and 'Other' as NewRole. I need both the ixo_rlt_code as OldRole and 'Other' as NewRole in the temp talbe in order to run an update I'm writing.

I know a case statement isn't necessary here, but this section is only one small part of a larger script, so I just used it to be consistent, and it will be easier to make any future changes this way. Below is my code and the error message I'm getting...

select
ixo_key,
ixo_title as Title,
ixo_rlt_code as OldRole,
CASE WHEN ixo_title is null
or ixo_title = ''
or ixo_title = ' '
or ixo_title = ' ' THEN 'Other'

END as NewRole

from
co_customer (nolock)
join co_individual_x_organization (nolock) on ixo_key = cst_ixo_key --and ixo_delete_flag = 0
where
cst_delete_flag = 0
and cst_type = 'Individual'
and ixo_rlt_code is null

This is the Error Message I get:
Server: Msg 515, Level 16, State 2, Line 1095
Cannot insert the value NULL into column 'NewRole', table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________00000001698C'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Thanks ahead of time for any help/suggestions

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 16:22:04

you have this case expression:

CASE WHEN ixo_title is null
or ixo_title = ''
or ixo_title = ' '
or ixo_title = ' ' THEN 'Other' END

which clearly states that if your condition is true, it should return the value 'Other' ... but, what if it is NOT true? What should it return? If you don't specify an ELSE portion of your CASE, then NULL is returned.

CASE WHEN ixo_title is null
or ixo_title = ''
or ixo_title = ' '
or ixo_title = ' ' THEN 'Other' ELSE ???? END

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 16:23:27

Also -- when SQL compares two columns, it ignores trailing spaces. So, you don't need all of your OR's, you can just write:

... when ixo_title is null or ixo_title = '' then 'Other' else ???? end ...

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 08:04:19

or

when ixo_title >'' then ???? else 'Other' end ...

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources