Parameter of Inline UDF with View problem

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parameter of Inline UDF with View problem

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 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

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.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

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 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources