selecting Failure column name for row

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

selecting Failure column name for row

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-10-07 : 06:20:40

Hi,



below is the query

Select t1.col1,t1.col2,t1.col3,t1.col4,t1.col5 ........ like 80 columns
from Table1 t1
inner join table2 t2 on t1.col1=t2.col1
inner join table3 t3 on t1.col2=t3.col2
inner join table4 t4 on t1.col3=t4.col3
inner join table5 t5 on t1.col4=t5.col4
inner join table6 t6 on t1.col5=t6.col5
.............
.............
inner join table20 t20 on t1.col19=t20


in table1 for some rows inner join condition may fail .

How to select on which field join condition fail for failure row

I need to select column names for failure row

Please help me

--Ranjit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:23:45

change inner join to left join and check for NULL values in column and based on that return the column name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 06:24:23

Not really sure what you want but maybe
Select t1.col1,t1.col2,t1.col3,t1.col4,t1.col5 ........ like 80 columns
from Table1 t1
left join table2 t2 on t1.col1=t2.col1
left join table3 t3 on t1.col2=t3.col2
left join table4 t4 on t1.col3=t4.col3
left join table5 t5 on t1.col4=t5.col4
left join table6 t6 on t1.col5=t6.col5
where t6.col5 is null.

You can see where the join failed by which column is the first one that is null in the output.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-10-07 : 07:09:43

Thank you visakh and nigelrivett

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources