July 10, 2010 at 8:17 pm
Hi Friends,
I need a help in creating a function. The problem is described below.
CREATE TABLE TB1
(
NAME VARCHAR(225),
age int
)
CREATE TABLE TB2
(
NAME VARCHAR(225),
age int
)
CREATE TABLE TB3
(
NAME VARCHAR(225),
age int
)
CREATE TABLE TB4
(
NAME VARCHAR(225),
age int
)
CREATE TABLE TB5
(
NAME varchar(225),
age int
)
insert into TB1
values('kin',17)
insert into TB1
values('sin',17)
insert into TB2
values('tin',17)
insert into TB2
values('bin',16)
insert into TB3
values('son',17)
insert into TB3
values('sun',17)
insert into TB4
values('harry',17)
insert into TB4
values('jhon',17)
insert into TB5
values('sam',0)
insert into TB5
values('jack',0)
tables
tb1
---------
NAME age
ki 17
sin 17
tb2
--------
NAME AGE
tin 17
bin 16
tb3
-------
NAME AGE
son 17
sun 17
tb4
-------
NAME AGE
harry 17
jhon 17
tb5
---------
NAME AGE
sam 0
jack 0
Problem:
in the above tables tabl1,tabl3 and tabl4 got age values 17
and tabl2 got age value 17&16
now i need to create a view or table which looks like
output view or table
tbalename age17 age17&16
--------------------------------------------
tb1 1 null
tb2 null 1
tb3 1 null
tb4 1 null
i got nearly 1000 tables like that.
i need to create a function which checks for the values for age columns in the tables and updates the final table columns with 1 or null
means if a particular table contains age column values 17 then the final table columns should be updated with the tablename column with the table name, age17 column with 1 and age17&16 column with null.
Sorry if i confuse you.
July 10, 2010 at 11:51 pm
You have to use cursor
Use the sysobjects table to get the list of table.
Then loop through one by one and check for the condition you want
July 11, 2010 at 2:11 am
If this is a brute force single action operation, you could use something like:
create table #wrk16_17 ( TbName sysname not null, Age17 bit, Age16_17 bit )
truncate table #wrk16_17
set nocount on
declare @sqlstmt varchar(4000)
Select @sqlstmt = '
Insert into #wrk16_17 ( TbName, Age16_17 )
Select top ( 1 )
''?''
, 1
from ?
where age = 17
and exists ( select 1
from ?
where age = 16 );
Insert into #wrk16_17 ( TbName, Age17 )
Select top ( 1 )
''?''
, 1
from ?
where age = 17
and not exists ( select 1
from #wrk16_17
where TbName = ''?'' ) ;
'
exec sys.sp_MSforeachtable @sqlstmt, @replacechar = N'?'
select *
from #wrk16_17
order by TbName
Keep in mind I do not reccommend using this in regular code !!
This will execute the code against every table, so it will come up with errors for the tables that don't have an "age" column.
TEST IT BEFORE YOU USE IT
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 11, 2010 at 2:45 am
Why do you have the same structured data in different tables, to me that smacks of bad design ?
Personally here , if i had to, i would generate a view which union's (ALL) all the tables together.
Then the aggregate operations are simple.
July 11, 2010 at 3:19 pm
rvasanth (7/10/2010)
You have to use cursorUse the sysobjects table to get the list of table.
Then loop through one by one and check for the condition you want
Anyone can say such a thing... let's see the CODE for your cursor for the example the op gave. 😉 After all, the op DID follow the rules of ettiquette and posted the definitions of all the tables along with readily consumable code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2010 at 6:24 pm
Joe Celko (7/12/2010)
These "tables need to have keys. and constraints; they are not really tables at all.CREATE TABLE TB1
(something_name VARCHAR(255) NOT NULL PRIMARY KEY,
something_age INTEGER NOT NULL CHECK (something_age > 0));
CREATE TABLE TB2
(something_name VARCHAR(255) NOT NULL PRIMARY KEY,
something_age INTEGER NOT NULL CHECK (something_age > 0));
CREATE TABLE TB3
(something_name VARCHAR(255) NOT NULL PRIMARY KEY,
something_age INTEGER NOT NULL CHECK (something_age > 0));
CREATE TABLE TB4
(something_name VARCHAR(255) NOT NULL PRIMARY KEY,
something_age INTEGER NOT NULL CHECK (something_age > 0));
CREATE TABLE TB5
(something_name VARCHAR(255) NOT NULL PRIMARY KEY,
something_age INTEGER NOT NULL CHECK (something_age > 0));
Having said that, the real problem is that they should not exist at all! This design flaw is called "Attribute splitting". It means you have taken what should be one table, and turned an attribute into a separate table:
Unless you're trying to build a partitioned view (and, you're not because something_name does not have the prerequisite constraint on it), I agree with Joe. I also agree with Joe that you could create a UNIONed view and adress this as a single entity. If you do decide to do that, look up and study "partitioned view" and "partitioned table" before you write the view so you can enjoy some of the benefits associated with partitioning.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply