Advanced SQL question

  

I have a query where I need to add a table "employee" and when searching- only the specific results come up according to employeeID.

I need to add this to my SQL statement: Employee.EmployeeId = EmployeeId or EmployeeId = NullIdentifier()

I have attached the advanced query as a text file- could not figure out how to copy/paste it into here.

Thanks




I think it should be here 

SELECT EMPLOYEEDISPLAYNAME
,ISNULL(Sick.Total, 0.00) AS SickValue
,ISNULL(Vacation.Total, 0.00) AS VacationValue
FROM {Employee}
LEFT JOIN (
SELECT EMPLOYEEID
,Sum(DEPOSITVALUE) - Sum(WITHDRAWLVALUE) AS Total
FROM {TimeOffRegisterEntry}
JOIN {TimeOffRegister} ON {TimeOffRegisterEntry}.TimeOffRegisterId = {TimeOffRegister}.TimeOffRegisterId
JOIN {TimeOffYear} ON {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId
WHERE TIMEOFFTYPE = @VacationType
AND {TimeOffYear}.[TimeOffYearId] = @Year
GROUP BY EMPLOYEEID
HAVING Sum(DEPOSITVALUE) - Sum(WITHDRAWLVALUE) < 0
) AS Vacation ON {Employee}.EmployeeId = Vacation.EMPLOYEEID
LEFT JOIN (
SELECT EMPLOYEEID
,Sum(DEPOSITVALUE) - Sum(WITHDRAWLVALUE) AS Total
FROM {TimeOffRegisterEntry}
JOIN {TimeOffRegister} ON {TimeOffRegister}.TimeOffRegisterId = {TimeOffRegisterEntry}.TimeOffRegisterId
JOIN {TimeOffYear} ON {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId
WHERE TIMEOFFTYPE = @SickType
AND {TimeOffYear}.[TimeOffYearId] = @Year
GROUP BY EMPLOYEEID
HAVING Sum(DEPOSITVALUE) - Sum(WITHDRAWLVALUE) < 0
) AS Sick ON {Employee}.EmployeeId = Sick.EMPLOYEEID
WHERE {Employee}.EmployeeId = EmployeeId or {EmployeeId} = NullIdentifier()
AND (Vacation.total IS NOT NULL
OR Sick.Total IS NOT NULL)

Hi,

In an advanced query you can't use NullIdentifier(), yiu need to do a IS NULL like it is on the last conditions.
Also, always do the IS NULL before the other comparison and don't forget the ( )

Where ({Employee}.EmployeeId IS NULL or {EmployeeId} = EmployeeId)

Regards,
João Rosado
Oh wait... missread the post got a bit misslead by Matthias reply.

If you want to a search parameter what you need is actually this:

where (@EmployeeId = 0 or {Employee}.EmployeeId = @EmployeeId)


Not 100% sure if that should be "= 0" or "IS NULL" in this case ..just try and see what works.

Regards,
João Rosado