Solved

Missing keyword error when converting from oracle to sql

Posted on 2010-08-30
7
464 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
[X]
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
  • 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 70

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 70

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

635 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