Views to be generated for particular ID

• Manish Joisar

  Ten Centuries

  Points: 1173

  More actions

  February 15, 2006 at 7:47 am

  #170690

  I have application in which whole application is depend upon the Values of PROJECT

  And this values comes from different Tables

  To get perfect values everywhere in Project i made a VIEW in which i take Summary of All data & Make one line Record Per PROJECT

  Now in one screen i want only for particular project & i used VIEW in it, but it is taking time to get record for only one PROJECT

  How can i restrict view to generate only for Particular PROJECT

  E.g.

  Table - Project

  Id    Project Name

  -----------------

  1     Project 1

  2     Project 2

  Table - Value1

  ProjectId       Value

  -------------------

  1               100

  1               200

  2               50

  2              150

  Table - Value2

  ProjectId         Value

  ----------------------

  1               20

  2               50

   

  I Made View like This

  --------------------

  CREATE VIEW View_Project

  AS

  SELECT ProjectId, SUM(Value) As ProjectValue

  FROM

  ( SELECT ProjectId, Value FROM Value1

  UNION ALL

     SELECT ProjectId, Value FROM Value2)

  )

  GROUP BY ProjectId

   

  By this way i am getting total Value of Project in One Line

  But when i want to use i will write query like this

  SELECT ProjectId, ProjectValue

  FROM View_Project

  WHERE ProjectId = 1

  But view will be created for All project & if Project/Data are more then it will take time for One Record also

  How can i solve this Problem

  It will be great help if i get some solution

  Thanks in advance

• Krishnan Kaniappan

  Ten Centuries

  Points: 1051

  More actions

  February 17, 2006 at 10:08 am

  #621738

  create function dbo.GetProjectvalue(@projectid int)

  returns int

  as

  begin

  declare @projectvalue int

  SELECT @projectvalue=SUM(Value)

  FROM

  ( SELECT  Value FROM Value1 where <A href="mailtorojectid=@projectid">Projectid=@projectid

  UNION ALL

     SELECT  Value FROM Value2 where <A href="mailtorojectid=@projectid">Projectid=@projectid

  ) as a

  return @projectvalue

  end

  -------------------------------------------

  select projectid,dbo.GetProjectvalue(projectid) from project

  where projectid=1

  -Krishnan

• Manish Joisar

  Ten Centuries

  Points: 1173

  More actions

  February 20, 2006 at 1:34 am

  #621936

  Thanks for your response

  I have done same way but instead of integer type

  i have taken RETURN type as TABLE & by this way i got Multiple Value in a Single Line

  & here i can send multiple Project No. in CSV format

  by this way i will get multiple project no values in a TABLE & link it to my other SELECT Statement

  Regards