1

I am using an SQL function to return entries, which are in two tables. Table A contains information about an UserId, FileID and Created DateTime. Table B contains information about an UserID, FileID and Transmission State.

The function should return the entries of UserID, FileID, Created and State

The function looks like this:

WITH FindNewestVersion as
    (
        Select Created AS cDate, FileId AS fId, UserId AS uId, ROW_NUMBER() over (partition by FileId, UserId ORDER BY Created DESC)rn from TableA
        where Created <= @dueDate
    )

    SELECT * from TableB AS B, TableA AS A 
    INNER JOIN (Select cDate, userId, FileId from FindNewestVersion where rn = 1) AS x
    ON A.Created = cDate AND A.UserId = uId AND A.FileId = fId  
    WHERE A.UserId = ISNULL(@userId, A.UserId) AND A.FileId = ISNULL(@fileId, q.FileId)
    AND B.TaskType = 'Transmission'

And the call of the function I am using in C# looks like this:

select A.UserId, A.FileId, A.Created, A.VersionId, 
case when B.variableColumn1 is null then null else B.variable1 end AS TransState 
from dbo.LSFN_GetMatrixAtDueDate([PARAMETERS]) AS A left join TableB AS B 
ON A.VersionId = B.variableColumn2 AND A.VersionId = B.variableColumn3

I am using this call, because the Columns in Table B can contain different information for various tasks and I have to determine the correct column with the help of a config file for the "Transmission Task". But I don't wanted to use a dynamic SQL, so I can use columnNames as parameters within the function itself.

So, this function and the call are almost working correctly. Let's do an example.

Table A contains:

UserID   | FileID   | Created
--------------------------------
UserA    | FileA    | 01.01.2011
UserA    | FileB    | 01.01.2011
UserC    | FileC    | 01.01.2011

Table B contains:

Variable1 (State)  | Variable2 (Version/File) | Variable3 (UserId) 
------------------------------------------------------------------
Completed          | FileA                    | UserA
Completed          | FileC                    | UserC

So, lets say, I want to get all entries of both tables, which belong to UserA the result should look like this:

UserID | FileID | Created    | State
----------------------------------------
UserA  | FileA  | 01.01.2011 | Completed
UserA  | FileB  | 01.01.2011 | NULL

However, I am getting multple entries for each results, so the result looks like this:

UserID | FileID | Created    | State
----------------------------------------
UserA  | FileA  | 01.01.2011 | Completed
UserA  | FileA  | 01.01.2011 | Completed
UserA  | FileB  | 01.01.2011 | NULL
UserA  | FileB  | 01.01.2011 | NULL

It is somehow related to the amount of entries in Table B. If I delete the entry of UserC, I will get all result entries correct, but if I add another row to TableB (so that there are 3 entries), I will get each result three times. I guess it has something to do with the select, where I am calling the function, but I don't know what is wrong there...

Echelon
  • 255
  • 6
  • 14
  • shouldn't you use `GROUP BY`? – fedorqui Jun 29 '16 at 09:12
  • Hmm ok, I guess, that would be a possible solution, but can I use a 'GROUP BY' without putting all columns, which I have in the select, in the 'GROUP BY'-clause? – Echelon Jun 29 '16 at 09:20
  • Yes! Just use for example `GROUP BY FileId`. – fedorqui Jun 29 '16 at 09:25
  • I already tried it this way and I am getting the error: `Column 'XYZ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` So it seems every column I am using in the select needs to be mentioned in the `Group By` – Echelon Jun 29 '16 at 09:27
  • Yep, I see it is [this problem](http://stackoverflow.com/a/13999903/1983854). It seems that you are joining too much. If you can share a SQLFiddle I may be able to test it. – fedorqui Jun 29 '16 at 09:34

0 Answers0