Solved Table Valued Function returning wrong results/empty record set (1 Viewer)

[Galaxiom]

Thanks for your response Wayne. I believe you are correct, TVF's cannot be linked - it would kind of defeat the purpose and a standard View, which CAN be linked, would suffice.

TVFs can be linked by using a subquery

INNER JOIN
( SELECT * FROM yourTVF(parameters)
) AS someAlias
     ON someAlias.someColumn = anotherTable.someColumn

 

[WayneRyan]

I don't know if I should be replying to a SOLVED thread.
I just read this and don't think we really finished.
And they want activity in this forum.

So with apologies to NauticalGent:

Post #19 had a Case statement in the Where clause:

WHERE
     CASE
          WHEN @Comp = 0 THEN ACTIVE.RECCLS IS NOT NULL
          ELSE ACTIVE.RECCLS Is Null
     END
;

The parser reads "CASE" ... OK
The parser reads the "WHEN" ... OK I have my 1st condition
The parser reads "@COMP = 0" ... OK That was my condition
The parser reads the "THEN" keywork ... OK What's my VALUE going to be?
Parses "ACTIVE.RECCLS" ... OK maybe I'm returning this column value
Parses "IS" ... NOT OK (how can I modify the value of ACTIVE.RECCLS with --> "IS" ???
It can't.

Your solution with the CASE statement.
Unfortunately, the correct syntax JUST adds to the "SOLVED" Where clause.
cheekybuddha's Where clause is the GOOD way to do it ... but

WHERE
     CASE
          WHEN (@Comp = 0 AND ACTIVE.RECCLS IS NOT NULL) Or
               (@Comp = 1 AND ACTIVE. RECCLS IS NULL) THEN 1
          ELSE 0
     END = 1
;

It doesn't matter what the Case's datatype is:

WHERE
     CASE
          WHEN (@Comp = 0 AND ACTIVE.RECCLS IS NOT NULL) Or     -- This is just the WHERE
               (@Comp = 1 AND ACTIVE. RECCLS IS NULL) THEN 'OK' -- embedded in CASE
          ELSE 'Not OK'
     END = 'OK'
;

The Case statement is great in the Select clause and you implement some very complex logic.
But it ALWAYS returns only one value (and always the SAME datatype).
It can also do some weird stuff:

Select
     CASE
          WHEN SomeValue = 'X' THEN (Select ... From)  -- The Select returns 1 row; 1 column
          When SomeValue = 'Y' THEN Table.SomeValue    -- value straight from the table
          When SomeValue = 'Z' Then Case ... END       -- Nested
          ELSE Null
     END as Computed_Value
;

When I use them in the Where or Order by clauses I have to cut and paste it from the Select into the Where.
You can't refer to it by the Select column name, you have to reproduce it entirely.
You can use CTEs to "save" their value, but not for LARGE resultsets.

Last thoughts on Case statements is that when things go south, its usually datatyping.
The initial rows will cast the VALUE of the case statement to integer, then after a while a BIGINT shows up.
Or a date "column" will suddenly have a non-date.

Anyway, sorry that't too late for your initial posting, but your where clause was the real answer.

hth,
Wayne

p.s.

Galaxiom, I was referring to Access having a linked-table version of a tvf.
I know there's no way to pass the parameter(s); so I assume that even a tvf with NO parameters can't be a linked-table.

 

[Galaxiom]

I was referring to Access having a linked-table version of a tvf.
I know there's no way to pass the parameter(s); so I assume that even a tvf with NO parameters can't be a linked-table.

To link the output of a TVF to Access, simply call the function in a PassThroughQuery which can then be used in other queries just like a linked table.

SELECT * FROM yourTVF(parameters)

Parameters can be changed by editing the SQL property of the querydef.

 

[Jon]

Thanks @WayneRyan for adding to the activity here.

 

[NauticalGent]

So with apologies to NauticalGent:

Don't know why I did not see this before, probably because I was in the middle of my repatriation.

No apologies needed Wayne, it is always good to see knowledgeable input. It is threads like this that have improved my skills with Access. I have come a long way, but the "smarter " I get, I realize I have much more to learn...

That's some Fortune Cookie wisdom there!