SQL Statement

Hi,

Please can anybody tell me what is wrong with this SQL Statement?

Code:

---

strsql = "TRANSFORM Max(Availability.Avail) AS MaxOfAvail " & _
        "SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffMaster.Discipline, " & _
        "StaffMaster.Designation FROM (StaffMaster LEFT JOIN Availability ON " & _
        "StaffMaster.EmpCode = Availability.EmpCode WHERE (StaffMaster.Designation<>'Project Manager' " & _
        "AND StaffMaster.Designation NOT LIKE 'Head%' AND StaffMaster.Designation NOT LIKE " & _
        "'%Manager%')) LEFT JOIN Designation ON StaffMaster.Discipline = Designation.Discipline " & _
        "GROUP BY StaffMaster.Designation ORDER BY StaffMaster.Discipline, Designation.Priority, " & _
        "StaffMaster.EmpName PIVOT Availability.Month_Year"

---

Thanks

just a quick load into a sql validator

Quote:

---

Result:
TRANSFORM Max(Availability.Avail) AS MaxOfAvail " & "SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffMaster.Discipline, " & "StaffMaster.Designation FROM (StaffMaster LEFT JOIN Availability ON " & "StaffMaster.EmpCode Availability.EmpCode WHERE (StaffMaster.Designation<>'Project Manager' " & "AND StaffMaster.Designation NOT LIKE 'Head%' AND StaffMaster.Designation NOT LIKE " &"'%Manager%')) LEFT JOIN Designation ON StaffMaster.Discipline = Designation.Discipline " & "GROUP BY StaffMaster.Designation ORDER BY StaffMaster.Discipline, Designation.Priority, " & "StaffMaster.EmpName PIVOT Availability.Month_Year"
^ ^- ^----- ^ ^---------- ^--- ^---------- ^ ^---- ^---- ^---------- ^---- ^---- ^---------- ^----------- ^-
syntax error: .
correction: ,
syntax error: AS
correction: JOIN
syntax error: SELECT
correction: ON
syntax error: ,
correction: (
syntax error: StaffMaster
correction: )
syntax error: FROM
correction: =
syntax error: " & " StaffMaster
correction: ( StaffMaster
syntax error: .
correction: JOIN <identifier> .
syntax error: WHERE
correction: ON
syntax error: 'Project Manager' " & "
correction: ( " & "
syntax error: " &" '%Manager%'
correction: + '%Manager%'
syntax error: " & " GROUP BY StaffMaster .
correction: .
syntax error: ORDER
correction: ) ) ORDER
syntax error: " & " StaffMaster
correction: ( StaffMaster
syntax error: Availability
correction: JOIN Availability
syntax error: " <end>
correction: <identifier> ON <identifier> ) <end>

---

see http://developer.mimer.com/validator...dex.tml#parser
for some help

A long silence!

My whole purpose is to get the result from two tables and it is to be sorted with a third table. Can this be done?

Quote:

---

My whole purpose is to get the result from two tables and it is to be sorted with a third table. Can this be done?

---

Generate the SQL Script for the tables that you used in SQL statement which has errors.
And Show us the script.. So, We can know the structure of your tables and we can create those tables in our database. Then, we can tell which thing you need to fix in your SQL Statement.

Sync, Thanks for the reply.

I generated the SQL Script for the tables that I need to be queried. Here it is:

Code:

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Designation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Designation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Availability]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Availability]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StaffMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StaffMaster]
GO

CREATE TABLE [dbo].[Designation] (
        [Priority] [int] NULL ,
        [Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Availability] (
        [Empcode] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [Month_Year] [smalldatetime] NOT NULL ,
        [Avail] [int] NOT NULL ,
        [AvailDate] [smalldatetime] NULL ,
        [Id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StaffMaster] (
        [EmpCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [Designation] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [Discipline] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

---