1,642 questions

3,186 answers

1,714 comments

478 users

Can TVFImplementation be used in a QueryView for Query instead of ViewImplementation

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="bbappfxqueryview"
xmlns:common="bbappfxcommontypes"
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].USRUFNSFUINDIVIDUALCONTACT
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
--VQUERYCONSTITUENT C left outer join USRVQUERYSFUSOLICITCODES VSC on C.ID = VSC.ID
CONSTITUENT C left outer join USR
VQUERYSFUSOLICITCODES VSC on C.ID = VSC.ID
left outer join [VQUERYSMARTFIELD98B67F5EEFFD425C9DF8BD8271EF6D71] as [Primary Constituency] on [C].[ID] = [Primary Constituency].[ID]
left outer join [dbo].[VQUERYSMARTFIELD45D5B447D0A64F99B451DDF25ADBA1DE] as [Lifetime] on [C].[ID] = [Lifetime].[ID]
left outer join [dbo].[VQUERYSMARTFIELD0F0343570D1C41C1AA43756FDE4750B2] as [LastGiftAmount] on [C].[ID] = [LastGiftAmount].[ID]
left outer join [dbo].[VQUERYSMARTFIELD6FD17AAF2F2444F8B4DFE065F5E6703B] 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>



<!--<AddTVFRelationship LinkFunction="USR_UFN_SFU_INDIVIDUAL_CONTACT" LinkField="CONSTITUENTID" Field="ID" PathAlias="SFU Individual Contact Information tvf"/>-->

asked Dec 14, 2017 by mpuffer (177 points)   4 14 26
edited Jan 2 by mpuffer

Post your code please.

Please log in or register to answer this question.

...