• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

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

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Jan 20, 2009
Messages
12,078
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
Code:
INNER JOIN
( SELECT * FROM yourTVF(parameters)
) AS someAlias
     ON someAlias.someColumn = anotherTable.someColumn
 

WayneRyan

AWF VIP
Local time
Today, 10:20
Joined
Nov 19, 2002
Messages
7,095
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:

Code:
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

Code:
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:

Code:
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:

Code:
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

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Jan 20, 2009
Messages
12,078
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.
Code:
SELECT * FROM yourTVF(parameters)

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

Jon

Access World Site Owner
Staff member
Local time
Today, 10:20
Joined
Sep 28, 1999
Messages
3,533
Thanks @WayneRyan for adding to the activity here.
 

Users who are viewing this thread

Top Bottom