[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Turn Oracle select statement into T-SQL select

Posted on 2009-04-30
10
Medium Priority
?
457 Views
Last Modified: 2012-05-06
HI, this code was originally written for asp direct to an ORacle DB, but we are changing it to ASP.net 1.1 and SQL to get information into another program to use it.  The problem is I am having trouble with all the DECODE statements and transfering them over to T-SQL.  I changed the qutoes from single to double in all DECODE statments and that took away the first error I received but now I am having trouble getting past this one.

[OLE/DB provider returned message: ORA-01756: quoted string not properly terminated
]
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.  
SELECT		* FROM OPENQUERY(SIDPERS, 
'SELECT		ut.RPT_SEQ_CODE,
		at.UPC,
		at.AUTH_PARA_DSG,
		at.AUTH_LINE_DSG,
		at.GRADE,
		SUBSTR(at.GRADE,1,1) MPC,
		DECODE(SUBSTR(at.GRADE,1,1),"O",SUBSTR(at.POSC,1,3), SUBSTR(at.POSC,1,4)) DMOS,
		at.AUTH_STR,
		DECODE(at.AUTH_PERS_IDENT,"E","M","W","M","O","M","I") GENDER,
		ut.OESTS,
		DECODE(SUBSTR(at.DOC_NBR,1,2),"NG","TDA","MTOE") UNIT_TYPE,
		at.AUTH_DOC_POSN_TITLE, 
		SUBSTR(ut.RPT_SEQ_CODE,1,6) RSC6,
		SUBSTR(b.J1,1,2),
		ut.zip,
		DECODE(UIC_Lock.Stat,"L","L",'') Locked,
		DECODE(UIC_Lock.Stat,"C","</br>Crit UIC","L","</br>Locked","&nbsp;") Crit_Bonus,
from		SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
		SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC')

Open in new window

0
Comment
Question by:kdeutsch
  • 5
  • 5
10 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 24270910
I also replaced substr(..,1,) thru left.

SELECT          * FROM OPENQUERY(SIDPERS, 
'SELECT         ut.RPT_SEQ_CODE,
                at.UPC,
                at.AUTH_PARA_DSG,
                at.AUTH_LINE_DSG,
                at.GRADE,
                left(at.GRADE,1) MPC,
                case left(at.GRADE,1) when 'O' then left(at.POSC,3) else left(at.POSC,4) end DMOS,
                at.AUTH_STR,
                case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
                ut.OESTS,
                case left(at.DOC_NBR,2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
                at.AUTH_DOC_POSN_TITLE, 
                left(ut.RPT_SEQ_CODE,6) RSC6,
                left(b.J1,2),
                ut.zip,
                case UIC_Lock.Stat when 'L' then 'L' else '' end Locked,
                case UIC_Lock.Stat when 'C' then '</br>Crit UIC' when 'L' then '</br>Locked' else ' ' end Crit_Bonus,
from            SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC')

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 24271000
Hi,
Ok it went bak to other error of error by 'O', so I changed all the single quotes to double quotes and it got rid of those errors, but another showed up.

[OLE/DB provider returned message: ORA-01741: illegal zero-length identifier
]
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.  

SELECT          * FROM OPENQUERY(SIDPERS,
'SELECT         ut.RPT_SEQ_CODE,
                at.UPC,
                at.AUTH_PARA_DSG,
                at.AUTH_LINE_DSG,
                at.GRADE,
                left(at.GRADE,1) MPC,
                case left(at.GRADE,1) when "O" then left(at.POSC,3) else left(at.POSC,4) end DMOS,
                at.AUTH_STR,
                case at.AUTH_PERS_IDENT when "E" then "M" when "W" then "M" when "O" then "M" else "I" end GENDER,
                ut.OESTS,
                case left(at.DOC_NBR,2) when "NG" then "TDA" else "MTOE" end UNIT_TYPE,
                at.AUTH_DOC_POSN_TITLE,
                left(ut.RPT_SEQ_CODE,6) RSC6,
                left(b.J1,2),
                ut.zip,
                case UIC_Lock.Stat when "L" then "L" else "" end Locked,
                case UIC_Lock.Stat when "C" then "</br>Crit UIC" when "L" then "</br>Locked" else " " end Crit_Bonus
from            SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC')
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 24271106
Replace each double quote thru two single quotes. Double quotes are used as delimiter for identifiers like field names with spaces.

Single quotes inside a string (the whole select is a string, since it start with a single quote) have to be doubled, as a single quote would mark the end of string.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:kdeutsch
ID: 24271178
Hi,
Ok if I do this I get the follwoing error.

Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'O'.
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 24271237
Did you use this code?


SELECT          * FROM OPENQUERY(SIDPERS,
'SELECT         ut.RPT_SEQ_CODE,
                at.UPC,
                at.AUTH_PARA_DSG,
                at.AUTH_LINE_DSG,
                at.GRADE,
                left(at.GRADE,1) MPC,
                case left(at.GRADE,1) when ''O'' then left(at.POSC,3) else left(at.POSC,4) end DMOS,
                at.AUTH_STR,
                case at.AUTH_PERS_IDENT when ''E'' then ''M'' when ''W'' then ''M'' when ''O'' then ''M'' else ''I'' end GENDER,
                ut.OESTS,
                case left(at.DOC_NBR,2) when ''NG'' then ''TDA'' else ''MTOE'' end UNIT_TYPE,
                at.AUTH_DOC_POSN_TITLE,
                left(ut.RPT_SEQ_CODE,6) RSC6,
                left(b.J1,2),
                ut.zip,
                case UIC_Lock.Stat when ''L'' then ''L'' else '''' end Locked,
                case UIC_Lock.Stat when ''C'' then ''</br>Crit UIC'' when ''L'' then ''</br>Locked'' else '' '' end Crit_Bonus
from            SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC')

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 24271284
Hi,
NO but I just tried it and it got rid of that error but this one

[OLE/DB provider returned message: ORA-00904: "UIC_LOCK"."STAT": invalid identifier
]
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 24271388
Oh, hold on, I didn't see you are still executing against Oracle. The decode-to-case transfer was not needed hence, but it does not harm. Only the two single quote transformation would have been needed.

The error message tells you that UIC_Lock is not found, or stat column is not part of that table. Most probably you are using the wrong security context (login info) for the Oracle DB Link SIDPERS, and not getting the correct schema. Have a look into the properties of the database link in Management Studio, and make sure the correct credentials are used (you will have to use explicit credentials, that is the last option for security contexts).
0
 

Author Comment

by:kdeutsch
ID: 24271404
HI,
When i take out the 2 statements with the statlocked it gets rid  of the error, but then this one creeps up about the left idetifier not correct.

[OLE/DB provider returned message: ORA-00904: "LEFT": invalid identifier
]
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 24271420
Sorry, that is a consequence of my misreading. Just replace it against your original code, substr(..., 1, ...)

0
 

Author Comment

by:kdeutsch
ID: 24271659
Hi,
Got it, I am getting info, and found out that I need to add more select stuff from different tables, but you have helped me immensly figure out how to translate ORacle to T-SQL.  Thanks
0

Featured Post

Technology Partners: 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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 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