| Author |
 Topic  |
|
|
ananthasri
Starting Member
Thailand
3 Posts |
 Posted - 06/11/2004 : 02:34:28
|
CREATE FUNCTION UDF_GET_PAY_RECORD_FOR_REPORT_2_1
(
@Input_Plan_Id uniqueidentifier
)
RETURNS TABLE
AS
RETURN ( 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 expr1
FROM 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
Thailand
3 Posts |
Posted - 06/11/2004 : 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
United Kingdom
158 Posts |
Posted - 06/11/2004 : 08:20:17
|
Try rewriting your view to something like...
SELECT A.name,A.planid,B.expr1
FROM vw_report_1_1 A
INNER JOIN (SELECT plan_ID,COUNT(project_name) AS expr1 FROM vw_sub_report_2_1 GROUP BY plan_id) B ON A.planid=B.planid
You 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
USA
7423 Posts |
Posted - 06/11/2004 : 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
Thailand
3 Posts |
Posted - 06/11/2004 : 18:14:19
|
I have data something like..
View 'rp1'
PlanId Project Number
00001 AAAAAAAA 1
00001 AAAAAAAA 2
00001 AAAAAAAA 3
00001 CCCCCCCC 1
00002 BBBBBBBBB 1
and view 'rp2'
PlanId PlanName
00001 11111111
00002 22222222
and I need to join view 'rp1' and 'rp2', and it return like..
PlanId ProjectCount
00001 2
00002 1
that mean it return 1 for duplicate project in the same PlanId.
Help me please.
Thank you in advance. |
|
|
| |
Topic |
|
Parameter of Inline UDF with View problem
