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:
WHEN @Comp = 0 THEN ACTIVE.RECCLS IS NOT NULL
ELSE ACTIVE.RECCLS Is Null
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" ???
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
WHEN (@Comp = 0 AND ACTIVE.RECCLS IS NOT NULL) Or
(@Comp = 1 AND ACTIVE. RECCLS IS NULL) THEN 1
END = 1
It doesn't matter what the Case's datatype is:
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:
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
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.
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.