Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Missing keyword error when converting from oracle to sql

Posted on 2010-08-30
7
Medium Priority
?
475 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

972 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