How to fix syntax errors in this query?

[helpquery]

SELECT     EmpID, amount, CASE isnull(StartDate, '') WHEN '' THEN CONVERT(varchar, cast('06/30/2010' AS datetime), 101) ELSE CONVERT(varchar, StartDate, 
 
                      101) END AS StartDate, percentage, PayrollCompany, [41] AS Period1, [42] AS Period2
 
FROM         (SELECT     t .EmpID, amount, StartDate, percentage, PayrollCompany
 
                       FROM          ESPPPeople e, PeriodInformation p, tmpEmpcontribution t
 
                       WHERE      t .EmpID = e.empID AND t .Period *= p.PeriodID) AS p PIVOT(MAX(EmpID) FOR Period IN ([41], [42])) AS pvt
 
ORDER BY pvt.EmpID, pvt.Period;

[gpl]

Is *= a part of the tsql language? If not, theres your problem, you need to add in some inner and left outer join statements

[helpquery]

No even if i remove that condition I 'l get the same message....i get error as "incorrect syntax near PIVOT"

[Rabbit]

So many errors...

1. You have *= instead of just =
2. You have a lot of spaces between the table qualifier and the field qualifier, i.e. t .fieldname instead of t.fieldname
3. You don't return Period as a field in your subquery
4. You seem to be trying to create the pivot fields in the select clause when you only have to do so in the pivot clause

[helpquery]

the spaces it is taking automatically even if i remove.pivot clause syntax specifies after select clause pivoted fields have to be written ..now am getting confused

[Rabbit]

In the SELECT clause, you specify the field to be pivoted, not the pivoted fields. You specify the pivoted fields in the PIVOT clause. But you don't even return the field you're trying to pivot so you're trying to pivot on a field that doesn't exist.

[ck9663]

Also, *= is the old way of doing a LEFT JOIN. If you remove the asterisk, it will become an INNER JOIN. Try to use the ANSI Standard of doing a join by specifying LEFT or INNER join, whichever case you need it to be.

Happy Coding!!!

~~ CK

[helpquery]

hey guys thank u....i ve fixed it .....