1.7k questions

3.2k answers

1.7k comments

398 users

Categories

1.7k questions

3.2k answers

1.7k comments

398 users

0 votes

I have changed a view to a table valued function in a QueryView for Query because I need more complex logic than I could get with a view. Now, I am getting Function has too many arguments specified error message when
I run the query in Query. The function returns data correctly when run in SSMS.

<QueryViewSpec
xmlns="bb_appfx_queryview"
xmlns:common="bb_appfx_commontypes"
ID="d052897e-3347-41b7-9f95-296b56d69e71"
Name="SFU Primary Contact Query"
Description="SFU Primary Contact Query"
Author="Meg Puffer"

IsRoot="false"
PrimaryKeyField="CONSTITUENTID"
RecordType="Query View Spec"
common:SecurityUIFolder="Query"
>

<!-- define the view used to return data for the query -->
<TVFImplementation FunctionName="USR_UFN_SFU_INDIVIDUAL_CONTACT">
	<CreateFunctionSQL>
		<![CDATA[

create function [dbo].USR_UFN_SFU_INDIVIDUAL_CONTACT
returns @ReturnTabletable TABLE
(
CONSTITUENTID uniqueidentifier,
LOOKUPID nvarchar(9),
NAME nvarchar(100),
PRIMARYCONSTITUENCY nvarchar(50),
FIRSTNAME nvarchar(50),
MIDDLENAME nvarchar(50),
LASTNAME nvarchar(100),
TITLE nvarchar(25),
AGE nvarchar(4),
DECEASED nvarchar(10),
SOLICITCODES nvarchar(250),
LIFETIME money,
LASTGIFTAMOUNT money,
LASTGIFTDATE date,
PRIMARYADDRESS nvarchar(150),
PRIMARYCITY nvarchar(50),
PRIMARYPROVINCE nvarchar(50),
PRIMARYCOUNTRY nvarchar(50),
PRIMARYPOSTAL nvarchar(12),
WORKADDRESS nvarchar(150),
WORKCITY nvarchar(50),
WORKPROVINCE nvarchar(50),
WORKCOUNTRY nvarchar(50),
WORKPOSTAL nvarchar(12),
PRIMARYEMAIL nvarchar(100),
PRIMARYPHONE nvarchar(100),
SPOUSELOOKUPID nvarchar(9),
SPOUSENAME nvarchar(150),
DEGREE nvarchar(50),
DATEGRADUATED DATE,
ACADPLANLIST nvarchar(150),
DEGREELIST nvarchar(150))

begin

INSERT INTO @ReturnTabletable

         select 												
            C.ID as CONSTITUENTID,												
		       C.LOOKUPID as LOOKUPID,									
		       isnull(C.NAME,'') as NAME,									

...

        from 												
             --V_QUERY_CONSTITUENT C left outer join USR_V_QUERY_SFU_SOLICIT_CODES VSC on C.ID = VSC.ID	
			 CONSTITUENT C left outer join USR_V_QUERY_SFU_SOLICIT_CODES VSC on C.ID = VSC.ID											
             left outer join [V_QUERY_SMARTFIELD98B67F5EEFFD425C9DF8BD8271EF6D71] as [Primary Constituency] on [C].[ID] = [Primary Constituency].[ID]	
			 left outer join [dbo].[V_QUERY_SMARTFIELD45D5B447D0A64F99B451DDF25ADBA1DE] as [Lifetime] on [C].[ID] = [Lifetime].[ID]
             left outer join [dbo].[V_QUERY_SMARTFIELD0F0343570D1C41C1AA43756FDE4750B2] as [LastGiftAmount] on [C].[ID] = [LastGiftAmount].[ID]
             left outer join [dbo].[V_QUERY_SMARTFIELD6FD17AAF2F2444F8B4DFE065F5E6703B] as [LastGiftDate] on [C].[ID] = [LastGiftDate].[ID]
			 left outer join DECEASEDCONSTITUENT DC on C.ID = DC.ID											
             left outer join TITLECODE TC on C.TITLECODEID = TC.ID	
			 										
        where 
			       ISORGANIZATION = 0 	
				--and 	 C.ID = @CONSTITUENTID
				
			
			return
			end]]>
	</CreateFunctionSQL>



</TVFImplementation>

<!-- describe each field in the view output -->
<Output>
	<OutputFields>
	<OutputField Caption="CONSTITUENTID" Category="System Fields" Name="CONSTITUENTID"  DataType="Guid"/>
	<OutputField Name="LOOKUPID" Caption="LookupID" DataType="String" />
  <OutputField Name="NAME" Caption="Name" DataType="String" />
  <OutputField Name="PRIMARYCONSTITUENCY" Caption="Primary Constituency" DataType="String" />
  <OutputField Name="FIRSTNAME" Caption="First Name" DataType="String" />
  <OutputField Name="MIDDLENAME" Caption="Middle Name" DataType="String" />
  <OutputField Name="LASTNAME" Caption="Last Name" DataType="String" />
  <OutputField Name="TITLE" Caption="Title" DataType="String" />
  <OutputField Name="AGE" Caption="Age" DataType="String" />
  <OutputField Name="DECEASED" Caption="Deceased" DataType="String" />
  <OutputField Name="SOLICITCODES" Caption="Solicit Codes" DataType="String" />
  <OutputField Name="LIFETIME" Caption="Lifetime (GiftsAndPledges)" DataType="Money" />
  <OutputField Name="LASTGIFTAMOUNT" Caption="Last Gift Amt (GiftsAndPledges)" DataType="Money" />
  <OutputField Name="LASTGIFTDATE" Caption="Last Gift Date (GiftsAndPledges)" DataType="Date" />
  <OutputField Name="PRIMARYADDRESS" Caption="Primary Address" DataType="String" />
  <OutputField Name="PRIMARYCITY" Caption="Primary City" DataType="String" />
  <OutputField Name="PRIMARYPROVINCE" Caption="Primary Province" DataType="String" />
  <OutputField Name="PRIMARYCOUNTRY" Caption="Primary Country" DataType="String" />
  <OutputField Name="PRIMARYPOSTAL" Caption="Primary Postal" DataType="String" />
  <OutputField Name="WORKADDRESS" Caption="Work Address" DataType="String" />
  <OutputField Name="WORKCITY" Caption="Work City" DataType="String" />
  <OutputField Name="WORKPROVINCE" Caption="Work Province" DataType="String" />
  <OutputField Name="WORKCOUNTRY" Caption="Work Country" DataType="String" />
  <OutputField Name="WORKPOSTAL" Caption="Work Postal" DataType="String" />     
  <OutputField Name="PRIMARYEMAIL" Caption="Primary Email" DataType="String" />
  <OutputField Name="PRIMARYPHONE" Caption="Primary Phone" DataType="String" />  
  <OutputField Name="SPOUSELOOKUPID" Caption="Spouse LookupID" DataType="String" />
  <OutputField Name="SPOUSENAME" Caption="Spouse Name" DataType="String" />
  <OutputField Name="DEGREE" Caption="Degree" DataType="String" />
  <OutputField Name="DATEGRADUATED" Caption="Date Graduated" DataType="String" />
  <OutputField Name="ACADPLANLIST" Caption="Acad Plan List" DataType="String" />
  <OutputField Name="DEGREELIST" Caption="Degree List" DataType="String" />

</OutputFields>
</Output>

<AddToParentView Field="CONSTITUENTID" ParentView="V_QUERY_CONSTITUENT" ParentViewRelatedField="ID" PathAlias="SFU Individual Contact Information"   DisplayAliasResourceKey="$$SFU_Primary_Contact" />
  <!--<AddTVFRelationship LinkFunction="USR_UFN_SFU_INDIVIDUAL_CONTACT" LinkField="CONSTITUENTID"  Field="ID" PathAlias="SFU Individual Contact Information tvf"/>-->

asked by mpuffer (177 points)
edited by mpuffer

Post your code please.

Please log in or register to answer this question.

...