• ** 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)

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Good morning AWF,

With what began in this thread, I attempted my first TVF and although it partially works, I cannot figure out what I am doing wrong.

The name of the TVF is dbo.TVF_DashBoard and the only parameter is @CompDate (datetime) and I have the default value set to NULL.

I call it with a Pass-Through Query and if I put an actual date (SELECT * FROM dbo.DashBoard('2020-04-10'), it returns the correct number of records. However if I want to see all the records with a NULL value (SELECT * FROM dbo.DashBoard(default), it returns no records. I have tried NULL,ISNULL, IS NULL - I either get the same results or an error.

I am using AC2013 with an SQL Server BE v17.4

Any ideas?
 

Minty

AWF VIP
Local time
Today, 09:36
Joined
Jul 26, 2013
Messages
7,332
Can you show us the Function in full please?
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Not at this moment, it is on a computer at work. I used the template that comes with SSMS if that helps. If not, I will post it in full tomorrow. Happy Easter, enjoy the day with family and friends!
 
Last edited:

WayneRyan

AWF VIP
Local time
Today, 09:36
Joined
Nov 19, 2002
Messages
7,095
I'd imagine that your where clause in the TVF should have something like:

Code:
Where Comp_Date = @CompDate      OR
           (Comp_Date Is Null AND
            @CompDate  Is Null)

I'm not using Access much nowadays ... you can't link the TVF and treat it like a table can you?

I suppose you're using it as a TSQL entity and joining it with your "real" tables.

Wayne
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Can you show us the Function in full please?

Here is it Minty. I have tried a few things including not setting a Default value - same results. It works a treat as long as I provide an actual date. Anything else throws an error or no results.

Code:
USE [RMC_Tracker] GO
/****** Object:    UserDefinedFunction [dbo].[TFV_Dashboard]
9:08:08 AM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
 


Script Date: 4/13/2020
 
--------------------------------------------
Author:    John Clark
Create date: 4/12/2020
Description: Returns a recordset for the RMC Tracker

ALTER FUNCTION [db o].[TV_FDashboadr]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIEV.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes, dbo.ACTIVE.CASREP,dbo.ACTIV.E[UPDATE], dbo.ACTIVE.TYAST,     dbo.ACTIEV.STATUS,dbo.ACTIEV.JCN, dbo.COMMANDS.Trigrpah
 
FROM    dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIEV.CMD_ID = dbo.COMMANDS.CMD ID


WHERE    (dbo.ACTIVE. RECCLS = @CompDate)
)
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
I'd imagine that your where clause in the TVF should have something like:
Code:
Where Comp_Date = @CompDate      OR
           (Comp_Date Is Null AND
            @CompDate  Is Null)
I'm not using Access much nowadays ... you can't link the TVF and treat it like a table can you?

I suppose you're using it as a TSQL entity and joining it with your "real" tables.
Wayne

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.
 

cheekybuddha

AWF VIP
Local time
Today, 09:36
Joined
Jul 21, 2014
Messages
557
Did you get your solution to your original problem?

As suggested by @WayneRyan:
Code:
-- ...
WHERE    dbo.ACTIVE. RECCLS = @CompDate
   OR    @CompDate IS NULL
-- ...
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Wayne's Suggestion works to some extent. If I pass
Code:
SELECT * FROM dbo.TVF_DashBoard(default)
' or
SELECT * FROM dbo.TVF_DashBoard(NULL)

I get the desired results. However, if I want to see ALL the records or just records where the date field is NOT NULL, it still gives me the records that have a NULL value.

What I am trying to do is have a Function that will allow me to retrieve/toggle
1) ALL the records
2)Completed records
3)Open/Active records

I know I could do this with three separate views but I would prefer not to.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:36
Joined
Jan 20, 2009
Messages
12,077
Use a CASE statement to interpret the parameter and select the SQL as appropriate.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
It turns out it isn't as easy as I thought Galaxiom; Everything I have tried will not execute. I even tried writing 2 separate SQL statements and the syntax will just not take. Dr. Google has shown that you CAN use a CASE statement in a WHERE clause but I am doing something wrong.

Based on the code I have provided, can you show me how you would do it?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:36
Joined
Jan 20, 2009
Messages
12,077
Code:
SELECT    dbo.ACTIEV.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes, dbo.ACTIVE.CASREP,dbo.ACTIV.E[UPDATE], dbo.ACTIVE.TYAST,     dbo.ACTIEV.STATUS,dbo.ACTIEV.JCN, dbo.COMMANDS.Trigrpah

FROM    dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIEV.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE. RECCLS = @CompDate)

That Select statement wouldn't work at all. Better to paste your actual code than post code that is full of typos.

One of the keys to writing SQL is layout. It is incredibly important as you get in more complex queries with subqueries etc which should be indented. It costs nothing to use extra lines and empty lines.

I usually avoid capitals for the objects and reserve that for keywords. Comma at the beginning of each selected column so they are easy to confirm their presence. Leave out the unnecessary default schema. I woudn't use UPDATE as a column name. It is too distracting.

I'm guessing this is what it was supposed to be with my layout style:
Code:
SELECT
      ACTIVE.RECORD_ID
     ,ACTIVE.CMD_ID
     ,ACTIVE.EQUIPMENT
     ,ACTIVE.EquipDes
     ,ACTIVE.CASREP
     ,ACTIVE.[UPDATE]
     ,ACTIVE.TYAST
     ,ACTIVE.[STATUS]
     ,ACTIVE.JCN
     ,COMMANDS.Trigrpah

FROM
     ACTIVE

INNER JOIN COMMANDS
     ON ACTIVE.CMD_ID = COMMANDS.CMD_ID

WHERE
     CASE
          WHEN @CompDate Is Not Null THEN ACTIVE. RECCLS = @CompDate
           ELSE ACTIVE.RECCLS Is Null
     END
;

I don't recall ever using a Null like that in a function. However you said you need three different cases so I don't understand how you can do that with one parameter that is either a Null or a Date.
 

cheekybuddha

AWF VIP
Local time
Today, 09:36
Joined
Jul 21, 2014
Messages
557
Hi, NG,

Ii think you're going to have a hard time determining your state with just a single parameter, unless you give it a 'magic value'

At the moment your parameter can be either a date or NULL.

If it is a date you want your table to be filtered.

If it is NULL you want either all records, or just records where Comp_Date IS NULL. How do you determine which?

Unfortunately there is no equivalent in T-SQL of IsMissing() in Access VBA for optional parameters.

So you can add an extra parameter for 'All' records, or do something like give the default value of @CompDate as '1900-01-01 00:00:00'.

First test whether the parameter has the default value ('1900-01-01 00:00:00') and return all records otherwise filter according to @CompDate

hth,

d

[Ah, I see Galaxiom replied whilst I was replying 👍 ]
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Better to paste your actual code than post code that is full of typos.
Apologies. I had to scan a PDF because it came from a classified system and it didn't "come over" as well as I thought and I did not take the time to validate it.

One of the things I did try was to change the variable to a bit and then I would call the Function with either 0 or 1; 0 to give me all completed records, 1 to give me completed. But every time I tried to use a CASE statement, SSMS would underline "stuff".

I ended up getting frustrated and asked for some assistance. I will go with the bit datatype for my variable and use your code example.

Really appreciate y'all's help. My VBA skills are meager at best and I have NO business messing around with SQL. Unfortunately, our IT knows less than I do. Scary.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
I made a change:



Code:
@comp bit = 0

_____


SELECT
      ACTIVE.RECORD_ID
     ,ACTIVE.CMD_ID
     ,ACTIVE.EQUIPMENT
     ,ACTIVE.EquipDes
     ,ACTIVE.CASREP
     ,ACTIVE.[UPDATE]
     ,ACTIVE.TYAST
     ,ACTIVE.[STATUS]
     ,ACTIVE.JCN
     ,COMMANDS.Trigrpah

FROM
     ACTIVE

INNER JOIN COMMANDS
     ON ACTIVE.CMD_ID = COMMANDS.CMD_ID

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

However, underlined words are underlined in red and it will not execute. Any ideas?

ps. I like your format, very easy to read.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:36
Joined
Jan 20, 2009
Messages
12,077
But every time I tried to use a CASE statement, SSMS would underline "stuff".

Stuff() is actually an SQL function for modifying strings.

My VBA skills are meager at best and I have NO business messing around with SQL. Unfortunately, our IT knows less than I do. Scary.

Don't be intimidated. SQL Server is awesome once you get going. A first I slowly infused T-SQL then realised its syntax is much simpler than Access SQL and never looked back. The incredible Intellisense really keeps pointing you in the right direction. At some point it became far easier to write in SQL Server Studio than even use the designer in Access. I stopped using the query designer in Management Studio pretty quickly as it doesn't support all the available features.

Many times when trying to do something I would imagine there must be a way to do it efficiently in SQL and I have never been disappointed. Other times I would go exploring just to see what something was and often put what I found to immediate use.[/QUOTE]
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:36
Joined
Jan 20, 2009
Messages
12,077
Underlines have not come through on the post.

If you hover over the underlines it will show a tool tip about the problem.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
I just noticed. The underlined parts are:
RECCLS IS NOT NULL

and

ELSE
 

cheekybuddha

AWF VIP
Local time
Today, 09:36
Joined
Jul 21, 2014
Messages
557
I don't think you can use CASE in a where clause like you have.

Try:
Code:
-- ...
WHERE (@Comp = 0 AND ACTIVE. RECCLS IS NOT NULL)
   OR (@Comp = 1 AND ACTIVE. RECCLS IS NULL)
;

[Edited: forgot the brackets!]
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 04:36
Joined
Apr 27, 2015
Messages
3,009
Well CB, you nailed it. I'm not sure why the CASE statement didn't work, but it doesn't matter, I have what I desire. Thanks to all who contributed, TeamWork Makes the DreamWork!
 

Users who are viewing this thread

Top Bottom