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.
Author |
Topic |
ananthasri
Starting Member
3 Posts |
Posted - 2004-06-11 : 02:34:28
|
CREATE FUNCTION UDF_GET_PAY_RECORD_FOR_REPORT_2_1( @Input_Plan_Id uniqueidentifier)RETURNS TABLEASRETURN ( SELECT DISTINCT PROJECT_NAME, STRATEGY_PLAN_ID FROM VW_SUB_REPORT_2_1 WHERE STRATEGY_PLAN_ID = @Input_Plan_Id)Create view VW1 as (SELECT STRATEGY_NAME, STRATEGY_PLAN_ID, (SELECT COUNT(project_name) FROM UDF_GET_PAY_RECORD_FOR_REPORT_2_1(Input_Parameter)) AS expr1FROM VW_REPORT_1_1)How to put a value of STRATEGY_PLAN_ID from VW_REPORT_1_1 to v for UDF_GET_PAY_RECORD_FOR_REPORT_2_1?I try to put VW_REPORT_1_1.STRATEGY_PLAN_ID , but it incorrect.Thank you.. |
|
ananthasri
Starting Member
3 Posts |
Posted - 2004-06-11 : 08:04:04
|
How to put a value of STRATEGY_PLAN_ID from VW_REPORT_1_1 to parameter of UDF_GET_PAY_RECORD_FOR_REPORT_2_1? |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-06-11 : 08:20:17
|
Try rewriting your view to something like...SELECT A.name,A.planid,B.expr1FROM vw_report_1_1 AINNER JOIN (SELECT plan_ID,COUNT(project_name) AS expr1 FROM vw_sub_report_2_1 GROUP BY plan_id) B ON A.planid=B.planidYou need to get rid of that function call basically.The derived table in the SQL above returns the same table as your function, and then by joining to your vw_report_1_1 on planid you'll get the name, planid, and the total count of entries in vw_sub_report_2_1 for that planid. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-06-11 : 08:50:37
|
if you want to pass a parameter to VW1, it cannot be a view. but it can be another UDF which takes a paramter and passed it to the function it calls.either way, I'm not what you are trying to obtain .. your subquery in your view has no relation to the resultset ...- Jeff |
|
|
ananthasri
Starting Member
3 Posts |
Posted - 2004-06-11 : 18:14:19
|
I have data something like..View 'rp1'PlanId Project Number00001 AAAAAAAA 100001 AAAAAAAA 200001 AAAAAAAA 300001 CCCCCCCC 100002 BBBBBBBBB 1 and view 'rp2'PlanId PlanName00001 1111111100002 22222222and I need to join view 'rp1' and 'rp2', and it return like..PlanId ProjectCount00001 200002 1that mean it return 1 for duplicate project in the same PlanId.Help me please.Thank you in advance. |
|
|
|
|
|
|
|