column check .... - SQL Server Forums

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)

column check ....

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2007-06-25 : 09:38:11

I'm doing a report to generate the error for the column before import the data.
This is what I have, and I wondering can this be rewritten different way to accomplish
the report.

thanks

INSERT INTO tbl
SELECT 'Error 1',ID FROM tbl2 WHERE len(ISNULL(col1,''))<10
UNION ALL
SELECT 'Error 2',ID FROM tbl2 WHERE len(ISNULL(col2,''))<10
UNION ALL
SELECT 'Error 3',ID FROM tbl2 WHERE len(ISNULL(col3,''))<10
UNION ALL
SELECT 'Error 4',ID FROM tbl2 WHERE len(ISNULL(col4,''))<10
UNION ALL
SELECT 'Error 5',ID FROM tbl2 WHERE len(ISNULL(col5,''))<10

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 09:56:13

[code]
Select
case when len(ISNULL(col1,''))<10 then 'Error 1'
when len(ISNULL(col2,''))<10 then 'Error 2'
when len(ISNULL(col3,''))<10 then 'Error 3'
when len(ISNULL(col4,''))<10 then 'Error 4'
when len(ISNULL(col5,''))<10 then 'Error 5'
end as error_number,
id
from
tbl2 [/code]

Madhivanan

Failing to plan is Planning to fail

hai
Yak Posting Veteran

84 Posts

Posted - 2007-06-25 : 11:18:08

Madhivanan,

I ran your suggestion and along with the union all statement, I get 2 complete different result.

thanks

declare @tbl2 table(id int,col1 varchar(20),col2 varchar(20),col3 varchar(20),col4 varchar(20),col5 varchar(20))
insert into @tbl2
values(1,'xxxx','mmmmm','kkkkkkk','yyyyy','uuuuuuuuuuuu')
insert into @tbl2
values(2,'uuuuuuuuuuuu','xxxx','mmmmm','kkkkkkk','yyyyy')
insert into @tbl2
values(3,'uuuuuuuuuuuu','xxxxxxxxxx','mmmmmmmmmmm','kkkkkkkkkk','yyyyyyyyyy')

Select
case when len(ISNULL(col1,''))<10 then 'Error 1'
when len(ISNULL(col2,''))<10 then 'Error 2'
when len(ISNULL(col3,''))<10 then 'Error 3'
when len(ISNULL(col4,''))<10 then 'Error 4'
when len(ISNULL(col5,''))<10 then 'Error 5'
end as error_number,
id
from
@tbl2

SELECT 'Error 1',ID FROM @tbl2 WHERE len(ISNULL(col1,''))<10
UNION ALL
SELECT 'Error 2',ID FROM @tbl2 WHERE len(ISNULL(col2,''))<10
UNION ALL
SELECT 'Error 3',ID FROM @tbl2 WHERE len(ISNULL(col3,''))<10
UNION ALL
SELECT 'Error 4',ID FROM @tbl2 WHERE len(ISNULL(col4,''))<10
UNION ALL
SELECT 'Error 5',ID FROM @tbl2 WHERE len(ISNULL(col5,''))<10

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 11:21:47

[code]SELECT d.Error_Number,
d.ID
FROM (
SELECT CASE
WHEN LEN(ISNULL(col1,''))<10 then 'Error 1'
WHEN LEN(ISNULL(col2,''))<10 then 'Error 2'
WHEN LEN(ISNULL(col3,''))<10 then 'Error 3'
WHEN LEN(ISNULL(col4,''))<10 then 'Error 4'
WHEN LEN(ISNULL(col5,''))<10 then 'Error 5'
END AS Error_number,
ID
FROM tbl2
) AS d
WHERE d.Error_Number IS NOT NULL[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 11:25:07

If you want ALL errors to be selected, the way you did is the way to go.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources