Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Missing keyword error when converting from oracle to sql

Posted on 2010-08-30
7
Medium Priority
?
468 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 900 total points
ID: 33561391
case when acn is null then auth + os - asgn else auth+os-asgn+acn end as vac
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 900 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 27

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 71

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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