Missing keyword error when converting from oracle to sql

I was given a oracle query that I need to convert to a sql openquery statement, but I ran into a piece that I don't no what it does, don't work in oracle.  the follwoig are the whole statments but need to know how to convert this section because this is giving me a missing keyword exception.  i thought it was like a case statement but I gues not.

IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]) AS VAC,

Here is the oracle code
SELECT      ACN_ACN_CURRENT_SLOTS.RSC,
            ACN_ACN_CURRENT_SLOTS.UPC,
            ACN_ACN_CURRENT_SLOTS.POSN_TITLE,
            ACN_ACN_CURRENT_SLOTS.DMOS,
            ACN_ACN_CURRENT_SLOTS.AUTH,
            ACN_ACN_CURRENT_SLOTS.OS,
            ACN_ACN_CURRENT_SLOTS.ASGN,
            ACN_ACN_CURRENT_SLOTS.ACN,
            IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]) AS VAC,
            ACN_ACN_BONUS_PERCENT_TLKP.B_PERC
FROM      ACN_ACN_CURRENT_SLOTS INNER JOIN
            ACN_ACN_BONUS_PERCENT_TLKP ON (Left([ACN_ACN_CURRENT_SLOTS].[RSC],6) = ACN_ACN_BONUS_PERCENT_TLKP.RSC6) AND
            (ACN_ACN_CURRENT_SLOTS.DMOS = ACN_ACN_BONUS_PERCENT_TLKP.DMOS)
WHERE      (((IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]))>0) AND
            ((ACN_ACN_CURRENT_SLOTS.OESTS)="N") AND
            ((ACN_ACN_CURRENT_SLOTS.CRIT_BONUS) Is Null) AND
            ((ACN_ACN_CURRENT_SLOTS.MOB) Is Null))
ORDER BY ACN_ACN_CURRENT_SLOTS.RSC, ACN_ACN_CURRENT_SLOTS.UPC, ACN_ACN_CURRENT_SLOTS.PARA,
                  ACN_ACN_CURRENT_SLOTS.LINE; ====================
This is my sql code that I am converting to.
Select * from OPENQUERY(SIDPERS, 
				'Select UPC,
						ADDR_CITY,
						POSN_TITLE,
						PARA, 
						LINE,
						RANK,
						DMOS,
						GENDER, 
						AUTH, 
						OS,  
						ASGN, 
						ACN, 
						?????? Case when ACN = 0 THEN AUTH + OS - ASGN ELSE AUTH + OS - ASGN - ACN  AS VAC, 
						UNIT_TYPE 
				from	ACN.ACN_CURRENT_SLOTS where DMOS = ''25B1''')

Open in new window

kdeutschAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Or, more simple and straightforward:
auth + os - asgn - nvl(acn, 0)

BTW, the above should be
case when acn is null then auth + os - asgn else auth+os-asgn-acn end as vac
(minus acn, not: plus acn).
0
 
sdstuberConnect With a Mentor Commented:
case when acn is null then auth + os - asgn else auth+os-asgn+acn end as vac
0
 
Shaun KlineLead Software EngineerCommented:
If you are using the OPENQUERY to access an Oracle DB, you should not need to do anything to the query, other than wrap the open query call around it. If you try to "translate" it into SQL Server's syntax and pass it to the Oracle server, you may get syntax errors when the Oracle server tries to interpret the query.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sdstuberCommented:
i based the above on the isnull check you have

comparing to what you started with though   ACN= 0    vs acn is null,  those aren't equivalent conditions

so I'm not entirely sure what condition you really want.

the lack of an "END" on your case statement inside the openquery is definitely a problem though

CASE is supported in sqlserver
0
 
kdeutschAuthor Commented:
All,
thanks for quick replies,

First If I put this into the sql as is it gives me the missing skeyword exception, but If I take out the brackets like next it gives me a invalid identifier.
IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]) AS VAC,

IIf(IsNull(ACN), AUTH + OS - ASGN, AUTH + OS - ASGN - ACN) AS VAC,

Now I see what I did wrong with my case statement , i forgot to put in the end clause.

Qlemo,
Does this solution provide what I am looking for, never seen anything like this so i guess I an questionsing how it works, because it looks quick and easy code.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The case statement and my formula do exactly the same. If the value of ACN is null, is shall be ignored in the formula   Auth + OS - ASGN - ACN, and to ignore it, you set it to zero (subtracting zero is neutral).

NVL(value1, value2) is the Oracle function to get value1 if not null, else value2. The same on MSSQL is
ISNULL(value1, value2).
0
 
kdeutschAuthor Commented:
Thanks for the quick response,  I am dividing these, because sdstuber corrected my stuff but I like the solution of Qlemo.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.