Turn Oracle select statement into T-SQL select

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

kdeutschAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
kdeutschAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
kdeutschAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, that is a consequence of my misreading. Just replace it against your original code, substr(..., 1, ...)

0
 
kdeutschAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.