CODEWITHSUNDEEP
sqlfunction
Cindy Brozyno
Cindy Brozyno

Reputation: 181

SQL Function not returning all rows

What I need: All crossings, and the AppData record for that year (if there is one, else null). Should always return 38,244 records.

What's happening: If I run it for 2012, I get all rows; if I run it for 2013, I only get 19248 rows.

On 1st application, a record is INSERTED into the AppData table. It IS possible that there will be crossings that do NOT have a record for the year. On 2nd or 3rd application, it's an Update record; there will ALWAYS be a record associated to the crossing.

I've been at this for days. Any help greatly appreciated!!

Table Data Crossings Table - CRID, int

AppData table AppID, AppDate, AppYear.

    ALTER FUNCTION [dbo].[fnGetAppData2]
    (
-- Add the parameters for the function here
    @app as varchar(4),
     @year as varchar(4)
     )
    RETURNS 
    @SCLApps TABLE 

-- Add the column definitions for the TABLE variable here
(AppCRID int,
AppDate date,
AppNbr int, 
AppTruck varchar(10), 
Spray bit,
Cut bit,
Inspect bit,
Invoice date,
AppYear int)

    AS
    BEGIN
    --for 1st app
-- Fill the table variable with the rows for your result set

If @app = 1

INSERT @SCLApps (AppCRID,AppDate,AppTruck,Cut,Inspect,Spray,Invoice,AppNbr)
SELECT Crossings.CRID,

--application date
(CASE WHEN @app = 1 THEN
App1Date 
WHEN @app = 2 THEN
App2Date
WHEN @app = 3 THEN
App3Date
ELSE
Null
END), 

--application truck
(CASE WHEN @app = 1 THEN
App1Truck
WHEN @app = 2 THEN
App2Truck
WHEN @app = 3 THEN
App3Truck
ELSE
Null
END), 

--app cut
(CASE WHEN @app = 1 THEN
App1Cut
WHEN @app = 2 THEN
App2Cut
WHEN @app = 3 THEN
App3Cut
ELSE
Null
END), 

--app inspect
(CASE WHEN @app = 1 THEN
App1Inspect
WHEN @app = 2 THEN
App2Inspect
WHEN @app = 3 THEN
App3Inspect
ELSE
Null
END), 

--app spray  
(CASE WHEN @app = 1 THEN
    App1Spray
WHEN @app = 2 THEN
    App2Spray
WHEN @app = 3 THEN
    App3Spray
    ELSE
Null
END),

--invoice
(CASE WHEN @app = 1 THEN
    App1InvDate
WHEN @app = 2 THEN
    App2InvDate
WHEN @app = 3 THEN
    App3InvDate
    ELSE
Null
END),

--AppNbr
(CASE WHEN @app = 1 THEN
1
WHEN @app = 2 THEN
2
WHEN @app = 3 THEN
3
ELSE
Null
END)

FROM AppData full OUTER JOIN Crossings ON AppData.CRID = Crossings.CRID
WHERE (AppYear = @year or AppYear is null)

ELSE




--for 2nd and 3rd app--------------------------------------

INSERT @SCLApps (AppCRID,AppDate,AppTruck,Cut,Inspect,Spray,Invoice, AppNbr)
SELECT Crossings.CRID,

--application date
(CASE WHEN @app = 1 THEN
App1Date
WHEN @app = 2 THEN
App2Date
WHEN @app = 3 THEN
App3Date
ELSE
''
END), 

--application truck
(CASE WHEN @app = 1 THEN
App1Truck
WHEN @app = 2 THEN
App2Truck
WHEN @app = 3 THEN
App3Truck
ELSE
''
END), 

--app cut
(CASE WHEN @app = 1 THEN
App1Cut
WHEN @app = 2 THEN
App2Cut
WHEN @app = 3 THEN
App3Cut
ELSE
''
END), 

--app inspect
(CASE WHEN @app = 1 THEN
App1Inspect
WHEN @app = 2 THEN
App2Inspect
WHEN @app = 3 THEN
App3Inspect
ELSE
''
END), 

--app spray  
(CASE WHEN @app = 1 THEN
    App1Spray
WHEN @app = 2 THEN
    App2Spray
WHEN @app = 3 THEN
    App3Spray
    ELSE
''
END),

--invoice
(CASE WHEN @app = 1 THEN
    App1InvDate
WHEN @app = 2 THEN
    App2InvDate
WHEN @app = 3 THEN
    App3InvDate
    ELSE
''
END),

--AppNbr
(CASE WHEN @app = 1 THEN
1
WHEN @app = 2 THEN
2
WHEN @app = 3 THEN
3
ELSE
''
END)

FROM AppData full OUTER JOIN Crossings ON AppData.CRID = Crossings.CRID
    WHERE AppYear = @year

RETURN 
    END

Upvotes: 0

Views: 1290

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146587

First of all, you should be using an inline table-valued function, not a multi-statement table valued UDF. Second of all, your UDF has two main branches, one for when @app = 1, and the other for any other values of @app. Yet within each branch the case statements distinguish between values of @app = 1, 2, 3. This is CLEARLY NOT RIGHT.
Second, as @jBrooks notes, the filter on AppYear, if placed in Where clause, will eliminate all rows where there is no match in whichever table appyear is in. To prevent this, this predicate must be part of Join On clause.

Try this:

CREATE FUNCTION  dbo.fnGetAppData2
( @app as varchar(4),  @year as varchar(4))
Returns Table
As
Return
(
  Select c.CRID,
     Case When @app = 1 Then App1Date When @app = 2 Then App2Date
          When @app = 3 Then App3Date  Else '' End, 

     Case When @app = 1 Then App1Truck When @app = 2 Then App2Truck
          When @app = 3 Then App3Truck Else '' End, 

     Case When @app = 1 Then App1Cut When @app = 2 Then App2Cut
          When @app = 3 Then App3Cut  Else '' End, 

     Case When @app = 1 Then App1Inspect When @app = 2 Then App2Inspect
          When @app = 3 Then App3Inspect Else '' End, 

     Case When @app = 1 Then App1Spray When @app = 2 Then App2Spray
          When @app = 3 Then App3Spray Else '' End, 

     Case When @app = 1 Then App1InvDate When @app = 2 Then App2InvDate
          When @app = 3 Then App3InvDate Else '' End, 

     Case When @app In (1,2,3) Then @app Else '' End 

  From AppData a Full Join Crossings c
      On c.CRID = a.CRID
          And AppYear = Coalesce(@year, AppYear)
)
GO

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

Even though you are doing a FULL OUTER JOIN, this part will eliminate Crossings that do not have a matching AppData:

WHERE AppYear = @year

Change the last part to:

FROM Crossings LEFT JOIN  
(SELECT *
FROM AppData
WHERE WHERE AppYear = @year) AS AppData
ON Crossings.CRID = AppData.CRID

As a side note, there are 2 formats to the case statement. You can simplify by using this one:

CASE @app 
WHEN 1 THEN App1Truck
WHEN 2 THEN App2Truck
WHEN 3 THEN App3Truck
ELSE Null END

Upvotes: 1

Related Questions