Incorrect Syntax near ';' (Microsoft SQL Server Error 102)

Question

First off, I have been searching all over for an answer to this, and I am also new to SQL coding, so please bear with me.

I am trying to discover the current health status of client machines in SCCM 2012r2 with a custom report, please see code below:

SELECT     v_r_system.name0                                       AS 'Computer Name', 
           v_gs_antimalwarehealthstatus.lastquickscanage          AS 'Days since last quick scan',
           v_gs_antimalwarehealthstatus.lastfullscanage           AS 'Days Since Last Full Scan',
           v_gs_antimalwareinfectionstatus.computerstatus         AS 'Current Status', 
           v_gs_antimalwareinfectionstatus.lastcleaneddectiontime AS 'Last Cleaned' 
FROM       v_gs_antimalwarehealthstatus 
INNER JOIN v_gs_antimalwarehealthstatus 
INNER JOIN v_r_system 
ON         v_gs_antimalwarehealthstatus.resourceid = v_r_system.resourceid 
INNER JOIN v_gs_antimalwareinfectionstatus;

When I try it out in Report Builder 3.0 I keep getting following error:

I can't seem to find the problem, it's driving me crazy...

Anyway, anything you can do to point me in the right direction would be great.

Cheers

`INNER JOIN v_gs_antimalwareinfectionstatus;` - add join condition — Backs, Jun 13 '17 at 13:17
You miss the on clause in some inner join here: `INNER JOIN v_gs_antimalwarehealthstatus` and `INNER JOIN v_gs_antimalwareinfectionstatus` — Jens, Jun 13 '17 at 13:17
inner join [something] on [first bit's field] = [second bit's field] — , Jun 13 '17 at 13:18
And i guess `FROM v_gs_antimalwarehealthstatus INNER JOIN v_gs_antimalwarehealthstatu`s` should be only `FROM v_gs_antimalwarehealthstatus ` — Jens, Jun 13 '17 at 13:19

score 0 · Answer 1 · answered Jun 13 '17 at 13:18

0

On whith columns do you join at the last table v_gs_antimalwareinfectionstatus? just add conditions in an ON-Clause and it should work fine

answered Jun 13 '17 at 13:18

Esteban P.

2,789
2
27
43

score 0 · Answer 2 · answered Jun 13 '17 at 13:22

I think you have two Problems in your query. first:

FROM       v_gs_antimalwarehealthstatus 
INNER JOIN v_gs_antimalwarehealthstatus

Why the inner join with it selfs? I think it is unnecessary and should be simply:

FROM       v_gs_antimalwarehealthstatus

The second one is that in the last join the ON condition is missing:

INNER JOIN v_gs_antimalwareinfectionstatus ON ... ....;

So the FROM .. JOIN part must look like:

FROM       v_gs_antimalwarehealthstatus 
INNER JOIN v_r_system 
ON         v_gs_antimalwarehealthstatus.resourceid = v_r_system.resourceid 
INNER JOIN v_gs_antimalwareinfectionstatus
ON v_gs_antimalwareinfectionstatus.<column> = <otherTable>.<column>

score 0 · Accepted Answer · answered Jun 13 '17 at 14:02

0

You just missed the ON Clause in the statement, also you can use table alias as it makes the query look clean.

SELECT     v_r_system.name0                                       AS 'Computer Name', 
           v_gs_antimalwarehealthstatus.lastquickscanage          AS 'Days since last quick scan',
           v_gs_antimalwarehealthstatus.lastfullscanage           AS 'Days Since Last Full Scan',
           v_gs_antimalwareinfectionstatus.computerstatus         AS 'Current Status', 
           v_gs_antimalwareinfectionstatus.lastcleaneddectiontime AS 'Last Cleaned' 
FROM       v_gs_antimalwarehealthstatus AWT
INNER JOIN v_r_system VRS
    ON  AWT.resourceid = VRS.resourceid 
INNER JOIN v_gs_antimalwareinfectionstatus AWI 
    ON [Place the Matching columns here];

answered Jun 13 '17 at 14:02

DataWrangler

1,804
17
32

You'd need to use the aliases in the `SELECT` clause as well know since the names like `v_gs_antimalwarehealthstatus` will no longer be in scope. – Damien_The_Unbeliever Jun 13 '17 at 14:10
Awesome, thanks for the answers guys.it is now glaringly obvious that I need to go learn some more, so I bought a book, and I'm gonna study up. Thanks again guys. – Paddy Cairns Jun 13 '17 at 14:26
@Damien_The_Unbeliever oops my mistake missed the same in the `SELECT` thanks for the catch :) – DataWrangler Jun 13 '17 at 14:31

Incorrect Syntax near ';' (Microsoft SQL Server Error 102)

3 Answers3