Solved Table Valued Function returning wrong results/empty record set

[NauticalGent]

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]

Can you show us the Function in full please?

 

[NauticalGent]

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!

 

[WayneRyan]

I'd imagine that your where clause in the TVF should have something like:

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]

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.

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]

I'd imagine that your where clause in the TVF should have something like:

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]

Did you get your solution to your original problem?

As suggested by @WayneRyan:

-- ...
WHERE    dbo.ACTIVE. RECCLS = @CompDate
   OR    @CompDate IS NULL
-- ...

 

[NauticalGent]

Wayne's Suggestion works to some extent. If I pass

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]

Use a CASE statement to interpret the parameter and select the SQL as appropriate.

 

[NauticalGent]

Now why didn't I think of that??

 

[NauticalGent]

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]

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:

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]

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]

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]

I made a change:

@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]

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]

Underlines have not come through on the post.

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

 

[NauticalGent]

I just noticed. The underlined parts are:
RECCLS IS NOT NULL

and

ELSE

 

[cheekybuddha]

I don't think you can use CASE in a where clause like you have.

Try:

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

[Edited: forgot the brackets!]

 

[NauticalGent]

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!