error with table values function used as a column value

[washaw]

I am having a problem with a table valued function, see the code attached,

what is wrong withe code, I am calling the tables valued function as a column value, but it is not allowing me to pass parameters to the funcion

declare @today datetime
set @today = getdate()
 
DECLARE @l_PPA_CONTACT_VALUES_Tbl TABLE(
      PARTY_ID    NUMERIC(10,0),
      CONTACT_VALUES VARCHAR(7000)
)
      INSERT INTO @l_PPA_CONTACT_VALUES_Tbl
      SELECT PARTY_ID   ,CONTACT_VALUES
      FROM PEGASUS.PEGASUS_AP_GET_PARTY_CONTACTS_TABLE_FUN(NULL, NULL,0, NULL, 'Business', 'Yes', 1, @today, NULL, 'ACTIVE', 0) 

select ppaCon.PARTY_ID,
(SELECT TOP 1 VALUE_TEXT FROM PEGASUS.PEGASUS_AP_SPLIT_FUN(ppaCon.CONTACT_VALUES, 0) WHERE VALUE_TEXT LIKE 'Business Phone:')  OfficePhone
from @l_PPA_CONTACT_VALUES_Tbl ppaCon
     right join PEGASUS.PARTY_PAIRS ppa 
     on ppaCon.PARTY_ID = ppa.PPA_ID

its error is

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '.'.

and it is pointing to the place where the function is called

Thanks

 

[washaw]

NO suggestion for this?

THanks,

 

[gmmastros]

The simple answer is... You can't write a query like this.

The problem is that table valued functions can return multiple rows. Returning multiple rows for each row is not possible. You tried to accommodate for this by using TOP 1, but unfortunately, the syntax fails to compile. You'll need to find another way to write the query.

One alternative would be to create another function that combines the 2 previous functions in to a single function that returns a scalar value. This is the 'easy' answer. Unfortunately, I would expect the query to be a bit slow.

The difficult answer to your problem would be to re-consider how you want to extract this data from your tables.

Makes sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[washaw]

THanks George