SQL Statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL Statement

  1. #1
    Join Date
    Apr 2004
    Posts
    134

    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

  2. #2
    Join Date
    Apr 2006
    Posts
    70
    just a quick load into a sql validator

    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

  3. #3
    Join Date
    Apr 2004
    Posts
    134
    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?

  4. #4
    Join Date
    Dec 2004
    Posts
    717
    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.
    Best Regards,
    Michael Sync
    http://michaelsync.net

    The more you share,The more you get

  5. #5
    Join Date
    Apr 2004
    Posts
    134
    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

Similar Threads

  1. SQL - WHERE clause with variable value
    By beecool in forum Database
    Replies: 10
    Last Post: 01-27-2010, 02:28 PM
  2. Replies: 2
    Last Post: 05-18-2003, 11:16 PM
  3. Replies: 0
    Last Post: 05-07-2002, 09:34 PM
  4. SQL Statement: Case sensitive
    By WooGor in forum Database
    Replies: 2
    Last Post: 04-11-2002, 02:31 PM
  5. SQL Statement Logging
    By John Senford in forum Java
    Replies: 1
    Last Post: 09-26-2001, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center