never done this before !! - 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 2005 Forums

Transact-SQL (2005)

never done this before !!

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-11 : 08:33:57

i have the following table


type_details
type		val1	val2	val3	val4	val5	.. this table has 225 columns
widget	1	23	55	66	77			
bar		99	33	44	1121	76	
raw		12	43	65	66	66

i have another table
type_setup
type	valid_columns	Table_name
widget	val1		type_details
widget	val2		type_details
bar		val3		type_details
bar		val4		type_details
raw		val5		type_details
.. an entry for every type in type_details table

the way this should be read is :
For widget, only accept values in the type_details table from columns val1 and val2 if there are any other values thow exceptions ....
For bar, only only accept values in the type_details table from columns val3 and val4 if there are any other values thow exceptions ....
and for raw, only accept values in the type_details table from columns val5 if there are any other values thow exceptions ....

So if i was to write a query for my exceptions it would be ....

select * from type_details where type ='widget' and (val3 is not null or val4 is not null or val5 is not null)

same with bar excpetions

select * from type_details where type ='bar' and (val1 is not null or val2 is not null or val5 is not null)

and the same with raw

select * from type_details where type ='widget' and (val1 is not null or val2 is not null or val3 is not null or val4 is not null)

Is it possible to automate the process of writing the exception queries if the type details table has 225 columns ??

or in other words

I would like to construct the queries automatically just by knowing the type_setup table ... maybe with exec 'query' in a cursor ??

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-11 : 18:46:01

Are you saying that for a record in the type_details, where type = widget, that val1 and val2 will be populated, and val3 to val224 will all contain nulls? If so, you need to re-think your table design.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-12 : 07:41:41

thanks DBA in the making

i cannot change structure of the table - this is what i am given to work with ....

please share your suggestions though ... i will review.

thanks again.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 08:02:33

You could do something like this:

SELECT * 
FROM type_details 
WHERE type ='widget' 
	AND COALESCE(val3, val4, val5) IS NOT NULL

But as for a solution that doesn't require specifically listing all the columns, *scratched head*... I don't think there is one.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources