Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Missing keyword error when converting from oracle to sql

Posted on 2010-08-30
7
458 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:kdeutsch
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 225 total points
ID: 33561391
case when acn is null then auth + os - asgn else auth+os-asgn+acn end as vac
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 225 total points
ID: 33561427
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
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 33561446
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 74

Expert Comment

by:sdstuber
ID: 33561448
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
 

Author Comment

by:kdeutsch
ID: 33561553
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 33561596
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
 

Author Closing Comment

by:kdeutsch
ID: 33561829
Thanks for the quick response,  I am dividing these, because sdstuber corrected my stuff but I like the solution of Qlemo.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question