Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Incorrect Syntax near the keyword 'where'

    Can anyone help me with this Query:
    Iím sort of new to writing sql queries so Iím sure thatís the reason for my oversight.

    For some reason when I go from the first query pasted below (which I did not originate) to the second query pasted below (Iíll point out the added lines). I get the following message

    Msg 156, Level 15, State 1, Line 76
    Incorrect syntax near the keyword 'where'.
    Msg 156, Level 15, State 1, Line 156
    Incorrect syntax near the keyword 'where'.

    In the second query I will also highlight the line numbers mentioned below

    Any HELP is Greatly Appreciated



    First Query:
    select
    c3.[_ResourceGuid]
    ,'Added' as [Added or Removed]
    ,c3.[LatestSnapshotDate] as InventoryDate
    ,c3.[DisplayName]
    ,c3.[DisplayVersion]

    from
    (
    select
    c2.[_Resourceguid]
    ,c2.[LatestSnapshotDate]
    ,c2.[NextToLastSnapshotDate]
    ,ih4.[Snapshotid]
    ,ih4.[DisplayName]
    ,ih4.[DisplayVersion]
    from
    (
    select
    ih3.[_ResourceGuid]
    ,ih3.[LatestSnapshotDate]
    ,c1.[NextToLastSnapshotDate]
    from
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih3
    join
    (
    select
    ih2.[_ResourceGuid]
    ,max([InventoryDate]) as [NextToLastSnapshotDate]
    from
    InvHist_AddRemoveProgram ih1
    Left Join
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih2
    on
    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]
    where
    ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]
    group by
    ih2.[_ResourceGuid]
    ) c1
    on
    ih3.[_ResourceGuid]=c1.[_ResourceGuid]
    ) c2
    join
    InvHist_AddRemoveProgram ih4
    on
    c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[LatestSnapshotDate]=ih4.[InventoryDate]
    ) c3
    left join
    InvHist_AddRemoveProgram ih5
    on
    c3.[_ResourceGuid] = ih5.[_ResourceGuid]
    and c3.[NextToLastSnapshotdate]=ih5.[InventoryDate]
    and c3.[DisplayName]=ih5.[DisplayName]
    and c3.[DisplayVersion]=ih5.[DisplayVersion]
    where
    ih5.[Snapshotid] is null

    UNION

    select
    c3.[_ResourceGuid]
    ,'Removed' as [Added or Removed]
    ,c3.[NextToLastSnapshotDate] as InventoryDate
    ,c3.[DisplayName]
    ,c3.[DisplayVersion]
    from
    (
    select
    c2.[_Resourceguid]
    ,c2.[LatestSnapshotDate]
    ,c2.[NextToLastSnapshotDate]
    ,ih4.[Snapshotid]
    ,ih4.[DisplayName]
    ,ih4.[DisplayVersion]
    from
    (
    select
    ih3.[_ResourceGuid]
    ,ih3.[LatestSnapshotDate]
    ,c1.[NextToLastSnapshotDate]
    from
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih3
    join
    (
    select
    ih2.[_ResourceGuid]
    ,max([InventoryDate]) as [NextToLastSnapshotDate]
    from
    InvHist_AddRemoveProgram ih1
    Left Join
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih2
    on
    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]
    where
    ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]
    group by
    ih2.[_ResourceGuid]
    ) c1
    on
    ih3.[_ResourceGuid]=c1.[_ResourceGuid]
    ) c2
    join
    InvHist_AddRemoveProgram ih4
    on
    c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[NextToLastSnapshotDate]=ih4.[InventoryDate]
    ) c3
    left join
    InvHist_AddRemoveProgram ih5
    on
    c3.[_ResourceGuid] = ih5.[_ResourceGuid]
    and c3.[LatestSnapshotdate]=ih5.[InventoryDate]
    and c3.[DisplayName]=ih5.[DisplayName]
    and c3.[DisplayVersion]=ih5.[DisplayVersion]
    where
    ih5.[Snapshotid] is null

    END OF FIRST QUERY


    I want to change this query to include the computer name so I modify it to the following:

    select
    ci.[Name] THIS LINE WAS ADDED
    ,c3.[_ResourceGuid]
    ,'Added' as [Added or Removed]
    ,c3.[LatestSnapshotDate] as InventoryDate
    ,c3.[DisplayName]
    ,c3.[DisplayVersion]

    from
    [vRM_Computer_Item] ci THIS LINE WAS ADDED

    Join THIS LINE WAS ADDED
    (
    select
    c2.[_Resourceguid]
    ,c2.[LatestSnapshotDate]
    ,c2.[NextToLastSnapshotDate]
    ,ih4.[Snapshotid]
    ,ih4.[DisplayName]
    ,ih4.[DisplayVersion]
    from
    (
    select
    ih3.[_ResourceGuid]
    ,ih3.[LatestSnapshotDate]
    ,c1.[NextToLastSnapshotDate]
    from
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih3
    join
    (
    select
    ih2.[_ResourceGuid]
    ,max([InventoryDate]) as [NextToLastSnapshotDate]
    from
    InvHist_AddRemoveProgram ih1
    Left Join
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih2
    on
    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]
    where
    ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]
    group by
    ih2.[_ResourceGuid]
    ) c1
    on
    ih3.[_ResourceGuid]=c1.[_ResourceGuid]
    ) c2
    join
    InvHist_AddRemoveProgram ih4
    on
    c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[LatestSnapshotDate]=ih4.[InventoryDate]
    ) c3
    left join
    InvHist_AddRemoveProgram ih5
    on
    c3.[_ResourceGuid] = ih5.[_ResourceGuid]
    and c3.[NextToLastSnapshotdate]=ih5.[InventoryDate]
    and c3.[DisplayName]=ih5.[DisplayName]
    and c3.[DisplayVersion]=ih5.[DisplayVersion]
    where LINE 76 Incorrect syntax near the keyword 'where'

    ih5.[Snapshoti] is null

    UNION

    select
    ci.[Name] THIS LINE WAS ADDED
    ,c3.[_ResourceGuid]
    ,'Removed' as [Added or Removed]
    ,c3.[NextToLastSnapshotDate] as InventoryDate
    ,c3.[DisplayName]
    ,c3.[DisplayVersion]
    from

    [vRM_Computer_Item] ci THIS LINE WAS ADDED

    Join THIS LINE WAS ADDED
    (
    select
    c2.[_Resourceguid]
    ,c2.[LatestSnapshotDate]
    ,c2.[NextToLastSnapshotDate]
    ,ih4.[Snapshotid]
    ,ih4.[DisplayName]
    ,ih4.[DisplayVersion]
    from
    (
    select
    ih3.[_ResourceGuid]
    ,ih3.[LatestSnapshotDate]
    ,c1.[NextToLastSnapshotDate]
    from
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih3
    join
    (
    select
    ih2.[_ResourceGuid]
    ,max([InventoryDate]) as [NextToLastSnapshotDate]
    from
    InvHist_AddRemoveProgram ih1
    Left Join
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih2
    on
    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]
    where
    ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]
    group by
    ih2.[_ResourceGuid]
    ) c1
    on
    ih3.[_ResourceGuid]=c1.[_ResourceGuid]
    ) c2
    join
    InvHist_AddRemoveProgram ih4
    on
    c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[NextToLastSnapshotDate]=ih4.[InventoryDate]
    ) c3
    left join
    InvHist_AddRemoveProgram ih5
    on
    c3.[_ResourceGuid] = ih5.[_ResourceGuid]
    and c3.[LatestSnapshotdate]=ih5.[InventoryDate]
    and c3.[DisplayName]=ih5.[DisplayName]
    and c3.[DisplayVersion]=ih5.[DisplayVersion]
    where Line 156 Incorrect syntax near the keyword 'where'
    ih5.[Snapshotid] is null

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're missing the ON condition which joins ci to c3

    simple, innit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Hey thanks a ton r937 and I hate to do this to you but I added an ON statement as shown below.... and got the same error. Looks like I do need to get that book. Where should I be adding the ON statement?



    select
    ci.[Name] -------THIS LINE WAS ADDED------
    ,c3.[_ResourceGuid]
    ,'Added' as [Added or Removed]
    ,c3.[LatestSnapshotDate] as InventoryDate
    ,c3.[DisplayName]
    ,c3.[DisplayVersion]

    from
    [vRM_Computer_Item] ci ------THIS LINE WAS ADDED-----

    Left Join ------THIS LINE WAS ADDED------

    (
    select
    c2.[_Resourceguid]
    ,c2.[LatestSnapshotDate]
    ,c2.[NextToLastSnapshotDate]
    ,ih4.[Snapshotid]
    ,ih4.[DisplayName]
    ,ih4.[DisplayVersion]
    from
    (
    select
    ih3.[_ResourceGuid]
    ,ih3.[LatestSnapshotDate]
    ,c1.[NextToLastSnapshotDate]
    from
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih3
    join
    (
    select
    ih2.[_ResourceGuid]
    ,max([InventoryDate]) as [NextToLastSnapshotDate]
    from
    InvHist_AddRemoveProgram ih1
    Left Join
    (
    select
    [_ResourceGuid]
    ,max([InventoryDate]) as LatestSnapshotDate
    from
    InvHist_AddRemoveProgram
    group by
    [_ResourceGuid]
    ) ih2
    on
    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]
    where
    ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]
    group by
    ih2.[_ResourceGuid]
    ) c1
    on
    ih3.[_ResourceGuid]=c1.[_ResourceGuid]
    ) c2
    join
    InvHist_AddRemoveProgram ih4
    on
    c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[LatestSnapshotDate]=ih4.[InventoryDate]
    ) c3

    left join
    InvHist_AddRemoveProgram ih5
    on
    c3.[_ResourceGuid] = ih5.[_ResourceGuid]
    and c3.[NextToLastSnapshotdate]=ih5.[InventoryDate]
    and c3.[DisplayName]=ih5.[DisplayName]
    and c3.[DisplayVersion]=ih5.[DisplayVersion]

    join
    vRM_Computer_Item ci
    on
    c3.[_ResourceGuid] = ci.[Name]



    where

    ih5.[Snapshoti] is null

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've added a second copy of the ci table, and that's wrong

    delete these lines --

    join
    vRM_Computer_Item ci
    on
    c3.[_ResourceGuid] = ci.[Name]


    and add these lines --

    on
    c3.[_ResourceGuid] = ci.[Name]


    right after this line --

    ) c3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    4
    Oh My....

    on
    c3.[_ResourceGuid] = ci.[Name]

    worked well without the )c3 - didn't work when I added )c3 but anyhow I got past that stump... and thanks!

    The query runs now and returns the headers but stops with the following message

    Msg 8169, Level 16, State 2, Line 1
    Conversion failed when converting from a character string to uniqueidentifier.

    That's so strange because the first query works just fine. I didn't imagine that adding computer name would throw it off so....

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by byrdjg View Post
    didn't work when I added )c3
    of course not, i said to add the ON clause after that line (which already existed), not to add that line

    regarding your conversion error, the source of this problem is your new ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2009
    Posts
    4
    Got it, got it... Now that I read it again. sorry about that.

    And yes... regarding my ON statement - changed ci.[Name] to ci.[Guid]

    Looking better now. I one issue with my results... You probably know what it is but I'm going to see if I can fix that myself.

    Thanks So MUCH!!! Enjoy the New Year

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •