Syntax Error in the query used for report

honeybee

New member
Local time
Today, 14:40
Joined
Mar 9, 2010
Messages
2
Hi am getting the following error in the query :
SELECT d.id, p.dtsid, d.dtsname, d.Complexity, d.Description, d.InUse, d.Server, d.Module, p.Procedure, t.Table, p.ProcedureId, t.tableid, dt.dtsTable, dt.dtsid, v.viewname AS ViewName, u.view AS ViewId
FROM (((Select d.id, d.DTSName, IIf(d.Complexity Is Null, '', (Select c.Complexity from complexity c where c.[id]=d.complexity)) AS Complexity, d.Description, d.InUse , IIf(d.Server Is Null,'',(Select ServerName from Server c where c.[id]=d.Server)) AS Server, IIf(d.SubModule Is Null,'',(Select SubModuleName from SubModule c where c.[id]=d.SubModule)) AS [Module] From Dts d) AS d LEFT JOIN (SELECT dtsid, u.
as [dtsTableId], dt.[TableName] AS [dtsTable] FROM DTSUses u, Tables dt Where u.
=dt.[id]) AS dt ON d.id = dt.dtsID) LEFT JOIN (SELECT dtsid, u.[Procedure] as ProcedureId, p.[ProcedureName] AS [Procedure] FROM Dts d, DTSUses u, ProceduresFunctions p Where d.[id] = u.dtsid and u.[procedure]=p.[id]) AS p ON d.id = p.dtsid) LEFT JOIN (SELECT dtsid, u.[Procedure] as ProcedureId, pu.
as [Tableid], t.[TableName] AS
FROM Dts d, DTSUses u, ProceduresFunctions p, ProcedureUses pu, Tables t Where d.[id] = u.dtsid and u.[procedure]=p.[id] and p.[id]=pu.[ProcedureId] and pu.
=t.[id]) AS t ON (p.ProcedureId = t.ProcedureId) AND (p.dtsid = t.dtsid))LEFT JOIN(
SELECT dtsid,u.[view] as ViewId, v.[ViewName] as [ViewName] FROM Dts d, DTSUses u,Views v where (d.[id] = u.dtsid) and (u.[procedure]=v.[id])AS v on (d.id=v.dtsid))
LEFT JOIN(SELECT dtsid,u.[view] as ViewId,vu.[TableId] as [TableId],t.[TableName] AS
FROM Dts d, DTSUses u,Views v,ViewUses vu ,Tables t where (d.[id] = u.dtsid) and (u.[procedure]=v.[id]) and (v.[id]=vu.[ViewId]) and (vu.[TableId]=t.[id]) AS t ON (v.dtsid = t.dtsid) AND (v.ViewId = t.ViewId));

"syntax error in from clause"

Unable to sort out why it comes where i need to change .Can anyone help me on this
 
Big statement... if that was me I'd try those select statements individually until you find the one that's at fault.
 
Code:
SELECT d.id, p.dtsid, d.dtsname, d.Complexity, d.Description, d.InUse, d.Server, d.Module, p.Procedure, t.Table, p.ProcedureId, t.tableid, dt.dtsTable, dt.dtsid, v.viewname AS ViewName, u.view AS ViewId
FROM 
(
 (
  (Select 
     d.id
   , d.DTSName
[b][u]   , IIf(d.Complexity Is Null 
         , ''
         , (Select c.Complexity 
            from   complexity c 
            where  c.[id]=d.complexity 
           )
        ) AS Complexity[/b][/u]
   , d.Description
   , d.InUse 
[b][u]   , IIf(d.Server Is Null
         , ''
         , (Select ServerName 
            from   Server c 
            where c.[id]=d.Server)
        ) AS Server
   , IIf(d.SubModule Is Null
         , ''
         , (Select SubModuleName 
            from SubModule c  
            where c.[id]=d.SubModule)
           ) AS [Module] 
   From Dts d[/b][/u]
  ) AS d 
LEFT JOIN (SELECT 
             dtsid
           , u.[Table] as [dtsTableId]
           , dt.[TableName] AS [dtsTable] 
           FROM DTSUses u
              , Tables dt 
           Where u.[table]=dt.[id]
          ) AS dt ON d.id = dt.dtsID
   )
LEFT JOIN (SELECT 
             dtsid
           , u.[Procedure] as ProcedureId
           , p.[ProcedureName] AS [Procedure] 
           FROM Dts d
           , DTSUses u
           , ProceduresFunctions p 
           Where d.[id] = u.dtsid 
             and u.[procedure]=p.[id]
          ) AS p ON d.id = p.dtsid
 ) 
LEFT JOIN (SELECT 
             dtsid
           , u.[Procedure] as ProcedureId
           , pu.[table] as [Tableid]
           , t.[TableName] AS [Table] 
           FROM Dts d
           , DTSUses u
           , ProceduresFunctions p
           , ProcedureUses pu
           , Tables t 
           Where d.[id] = u.dtsid 
             and u.[procedure]=p.[id] 
             and p.[id]=pu.[ProcedureId] 
             and pu.[table]=t.[id]
          ) AS t ON (p.ProcedureId = t.ProcedureId)  
                 AND (p.dtsid = t.dtsid)
)
LEFT JOIN (SELECT 
             dtsid
           , u.[view] as ViewId
           , v.[ViewName] as [ViewName] 
           FROM 
             Dts d
           , DTSUses u
           ,Views v 
           where (d.[id] = u.dtsid) 
             and (u.[procedure]=v.[id]) [B][Missing Bracket here][/B] AS v on (d.id=v.dtsid)[b][u])[/b][/u]
LEFT JOIN (SELECT 
             dtsid
           , u.[view] as ViewId
           , vu.[TableId] as [TableId]
           , t.[TableName] AS [Table] 
           FROM Dts d
           , DTSUses u
           , Views v
           , ViewUses vu
           , Tables t 
           where (d.[id] = u.dtsid) 
             and (u.[procedure]=v.[id]) 
             and (v.[id]=vu.[ViewId]) 
             and (vu.[TableId]=t.[id]) [B][Missing Bracket here][/B] AS t ON (v.dtsid = t.dtsid) 
                                           AND (v.ViewId = t.ViewId)[b][u])[/b][/u];

I just re-wrote your sql into something a little more readable....
there seem (to me) to be 2 brackets out of place

Also the IIFs I marked seem a little odd, you seem to be making a hard way for doing a simple left join?

Finaly your mixing of the "join" syntax and the "where" syntax doesnt make thing much easier either
 

Users who are viewing this thread

Back
Top Bottom