Any other way? - 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)

Any other way?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-15 : 09:57:35

I have a table like this:


TblA
RowId Identity, Not Null
ValidRecord Bit
Exception1 Bit
Eception2 Bit
Exception3 Bit

Values:
TblA
RowId    ValidRecord    Exception1    Exception2    Exception3
1             0             1             1             0
2             1             0             0             0
3             0             0             1             1
4             0             0             0             1

I have another table ExceptionsMaster


ExceptionId nvarchar(50) not null
ExceptionDescription nvarchar(300) not null

Values:
ExceptionNo       ExceptionDescription
Exception1        First Name is null / blank
Exception2        Email Id is null / blank
Exception3        Phone Number is null / blank

I have to create a log file from TblA in the following format:
Row number = 1 *** ERROR First Name is null / blank
Row number = 1 *** ERROR Email Id is null / blank
Row number = 3 *** ERROR Email Id is null / blank
Row number = 3 *** ERROR Phone Number is null / blank
Row number = 4 *** ERROR Phone Number is null / blank

To get this kind report I have to get the rowid from TblA and ExceptionDescription from ExceptionMaster for that exception
My current query is:


Select RowId,ExceptionDescription from TblA,ExceptionMaster where
(exception1=1 and exceptionno = 'exception1') or (exception2=1 and 
exceptionno='exception2') or (exception3=1 and exceptionno 
= 'exception3')

This gives me the RowId and ExceptionDescription for that rowid. Is there any other better way to do this?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-15 : 10:42:48

Is there a reason you have exception1, 2, 3

instead of another table of exceptions?

________________________________________________

As the only Republican that likes the Green Party. I am a contradiction.

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-15 : 10:47:36

This is bad design. You have data *values* in 1 table, that are *column names* in a related table. What's the point in using a relational database whose primary purpose is JOINING data, if you structure the design such that data can't be joined ?

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-15 : 11:08:14

The story is

TblA is a staging temp table for me, where I import some thousands of records from a excel file. Again that excel is an extract from an oracle database. From staging table TblA, I have to split the records and export them to 15 tables. When the records in the TblA meets certain conditions(exceptions, atleast 14 identified so far) those records should not be exported to any of the 15 tables. They have to be marked as invalid records and the detail of the exception should be sent in an log file.

Say, If a record meets 3 conditions then log file should have details about all the 3 exceptions for that record.Like this:


Row number = 1 *** ERROR First Name is null / blank
Row number = 1 *** ERROR Email Id is null / blank
Row number = 3 *** ERROR Email Id is null / blank
Row number = 3 *** ERROR Phone Number is null / blank

If you can suggest a different table structure, which can handle this scenario that would be great.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources