Error in Inner Join and IIF

Question

I hope you can help me with that two questions. I copied a bunch of code from an Accessfile into the MSSQL Server to create some Views. Until now everything has worked fine so far but now I stuck...

One Problem is related to the "IIF"-Command:

IIf([T024_ArtStamm]![C002] Like "A*",[dbo_T023_ArtPreise]![C006],[A PreisListe 01]![C013]) AS Verrechnungspreis

I changed it to:

IIf([Buran.dbo.T024_ArtStamm].[c002] like 'A%',
    [Buran.dbo.T023_ArtPreise].[C006],
    [Buran.dbo.A_PreisListe_01].[C013]) AS Verrechnungspreis

To me it looks correct. But the Server has some issues with that...

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'like'.

I tried '=' as well but meanwhile I ran out of ideas...

The second issue is related to an inner join: Again in Access it works fine:

Select [...] 
FROM ((((T024_ArtStamm INNER JOIN 
dbo_t057 AS dbo_T057_ArtZusatz ON
(T024_ArtStamm.c106 = dbo_T057_ArtZusatz.c000) AND 
(T024_ArtStamm.mesocomp = dbo_T057_ArtZusatz.mesocomp) AND 
(T024_ArtStamm.mesoyear = dbo_T057_ArtZusatz.mesoyear)) INNER JOIN [--goes on for a while--]

I converted it so all the tables can be found...

Select [...] 
--Line 54
    FROM ((((Buran.dbo.T024_ArtStamm INNER JOIN 
--/
    CWLDATEN_91.dbo.t057 AS Buran.dbo.T057_ArtZusatz ON 
    (Buran.dbo.T024_ArtStamm.c106 = dbo.T057_ArtZusatz.c000) AND 
    (Buran.dbo.T024_ArtStamm.mesocomp = dbo.T057_ArtZusatz.mesocomp) AND 
    (Buran.dbo.T024_ArtStamm.mesoyear = dbo.T057_ArtZusatz.mesoyear)) INNER JOIN [--and it goes on--]

But the error is

Msg 102, Level 15, State 1, Line 54 Incorrect syntax near 'Buran'.

I checked all the Tables - they exist. I tried with Squirrel SQL instead of the SQL Server manager and get the same error...

Where am I wrong?

SQL Server does not support `IIF` until SQL Server 2012. If you are on a lower version, you need to change that logic to a [`CASE expression`](http://msdn.microsoft.com/en-us/library/ms181765(v=sql.100).aspx). Also are you sure `[Buran.dbo.T024_ArtStamm]` should be enclosed in a single `[square bracket]`? Do you really have a table named `Buran.dbo.T024_ArtStamm`? Finally, get rid of all the stupid `((((((((parentheses nonsense)))))` that Access has littered your code with. — Aaron Bertrand, Jun 26 '13 at 17:54
It is the SQL Server 2012. I thought about that first, but the IIF works. Unfort after the inner-join a whole rat-tail of other inner-joins follows... I added the square bracket... Tried a few "experiments"... No changes :-/ — Qohelet, Jun 26 '13 at 17:58
Right, sorry - this Error-Code was from one of these experiments. The correct code is the following: Msg 102, Level 15, State 1, Line 54 Incorrect syntax near 'Buran'. — Qohelet, Jun 26 '13 at 18:02

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

0

what version of sql server do you have??? iif doesn't exist prior 2008R2 version

To your second question, your mistake is in the "alias", you must write "Tables AS aliasName", but you have "Table AS Database.Table"

answered Jun 26 '13 at 18:40

Jairon Alava

71
3

As shown in the Tags it's 2012-Server. Your answer makes sense, but is wrong... As shown the error is in the line before... – Qohelet Jun 27 '13 at 09:11

Error in Inner Join and IIF

1 Answers1