SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Parameter of Inline UDF with View problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ananthasri
Starting Member

Thailand
3 Posts

Posted - 06/11/2004 :  02:34:28  Show Profile  Visit ananthasri's Homepage  Reply with Quote
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  Show Profile  Visit ananthasri's Homepage  Reply with Quote
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?
Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 06/11/2004 :  08:20:17  Show Profile  Reply with Quote
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.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/11/2004 :  08:50:37  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

ananthasri
Starting Member

Thailand
3 Posts

Posted - 06/11/2004 :  18:14:19  Show Profile  Visit ananthasri's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000