IT Professionals

[PARAMASHIVAN]

Help needed App na / Kirukan pls

I have a table called Customer, with fields called customer_id, cust_name and nick name, I want a trigger that would update nick_name,
when ever a value is inserted into cust_name .

the sql command starts some thing like this

create trigger test
on customer
for update as

I amnot sure of the code after this ...

[kirukan]

Help needed App na / Kirukan pls

I have a table called Customer, with fields called customer_id, cust_name and nick name, I want a trigger that would update nick_name,
when ever a value is inserted into cust_name .

the sql command starts some thing like this

create trigger test
on customer
for update as

I amnot sure of the code after this ...

Create TRIGGER test ON customer FOR Update AS
set nocount on
declare @nickname varchar(50)

--- Selecting Columns form Updated table
select @nickname=u.Customername
from inserted u
update nickname=@nickname where Customername=@nickname

Hope this helps

[PARAMASHIVAN]

Sorry for the late reply Kirukan, been very busy at work. Thanks for your help, I have done it now. One more Question

Say you have Storeprcedure called SP1 with a variable called @orderno varchar(20) and you have another procedure called SP2 without any variables, can you call SP1 's variable into SP2 and excecute SP2. Is it some thing like this

Create proc SP2
as
Exec SP1,@orderno

??

Thanks in advance

[kirukan]

Sorry for the late reply Kirukan, been very busy at work. Thanks for your help, I have done it now. One more Question

Say you have Storeprcedure called SP1 with a variable called @orderno varchar(20) and you have another procedure called SP2 without any variables, can you call SP1 's variable into SP2 and excecute SP2. Is it some thing like this

Create proc SP2
as
Exec SP1,@orderno

??

Thanks in advance

I dont think its possible unless u use global variable @@orderno.

[PARAMASHIVAN]

Thanks Kirukan, I have used a Global variable , and it works !

[ramdas2005]

Thanks Kirukukan and App na , I will try that. BTW, I have question , how do you find the second highest value or 3rd highest value in a column, say you have vcolumn called salary, so ' Select Max(salary) from table' would return the highest salary, but how about the second or thir highest ?

Hi Paramashivan,
One way to do this in sql server 2005/2008 is to use the RANK() function and get the salary ranked in desecending order, Run a select on the temp table/derived table and look for rank=2 or 3.

SELECT * FROM (SELECT RANK() OVER (ORDER BY Salary DESC) AS RankNum) as a
WHERE RankNum=2.
If you use a construct similar to the above, you should get your result.



Thank You

[PARAMASHIVAN]

Welcome to the thread Ramdas

Thanks, I will try that and let you know

[ramdas2005]

Welcome to the thread Ramdas

Thanks, I will try that and let you know

Thank you Paramashivan , I also work with SSRS/SSIS, In case you work in those areas, i can share my experiences.

[PARAMASHIVAN]

Ramdas

That's great stuff, I am actually working as SQL server / BI Developer, Primarily on SSIS , SSRS on 2008.

Have you got experience in Datawarehouse development, If so can you pls briefly explain the procedure of creating a datawarehouse? Like for Relational Database , you would normalise the data, come up with enitities, then draw up an entity relation ship diagram, then from that you would create the tables, Pk, FK, views, ...etc

I know in Datawarehouse, it is de-normalised data, and you have fact tables/dimensional tables..etc. Can you briefly explain them to me pls

thanks

[ramdas2005]

Ramdas

That's great stuff, I am actually working as SQL server / BI Developer, Primarily on SSIS , SSRS on 2008.

Have you got experience in Datawarehouse development, If so can you pls briefly explain the procedure of creating a datawarehouse? Like for Relational Database , you would normalise the data, come up with enitities, then draw up an entity relation ship diagram, then from that you would create the tables, Pk, FK, views, ...etc

I know in Datawarehouse, it is de-normalised data, and you have fact tables/dimensional tables..etc. Can you briefly explain them to me pls

thanks

Hi Paramashivan,
A datawarehouse/data mart contain fact and dimension tables. I am attaching couple of documents which explains fact and dimension tables. The document has sample fact and dimension tables. It also has an image describing Fact and Dimension table relationship. This should give you a good starting point.

Fact and dimension tables are usually populated through Stored procedures/SSIS packages with the source data coming form OLTP relational systems. The SP's and SSIS denormalise the data and populate the fact and dimension tables. Usually the databases for a datawarehouse would be on a different server. The SSIS package would grab data from different source tables in OLTP transfer it to staging tables on datawarehouse database. From the satging tables Stored procedures would transform the data into denormalise form and load them into fact and dimension tables.

Fact Table:
A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Each fact table also includes a multipart index that contains as foreign keys the primary keys of related dimension tables, which contain the attributes of the fact records.

Also if you could give me you e-mail id we can correspond better for explanation of concepts.

On a lighter note : Rajini fan / kamal fan/?