Converting from SqlDeveloper Oracle query to MS Access Passthrough 3

[IngDep]

Have the following Oracle query that I would like to convert to a MS Access Passthrough query but continually receive a "ORA-00911: Invalid Character" error message.

Any idea as to what the problem/resolution is?

Greatly appreciate any insight as this is taking over a hour to resolve!


SELECT TAG_ID,
CDML_SEQ_NO,
TAG.PRPR_ID AS SVC_PRPR_ID,
TAG.TAG_PAYEE_PR_ID AS PAY_TO_PRPR_ID,
TAG.TAG_PRPR_ID_PCP AS PCP_PRPR_ID,
PRPR.PRPR_NAME,
MEME_CK,
(CASE
WHEN TAG.GRGR_CK = 4 THEN
CASE
WHEN TAG.CSCS_ID = 'LT04' THEN 'KIDS'
WHEN TAG.CSCS_ID = 'LB03' THEN 'CAID'
END
ELSE 'XX'
END) AS PRODUCT,
SBSB_ID AS MEMBER_ID,
TAG.GRGR_CK,
TAG.CSCS_ID,
CDML_FROM_DT,
CDML_TO_DT,
CDML_CONSIDER_CHG
CDML_PR_PYMT_AMT
CDML_SB_PYMT_AMT
TAG.TAG_PAID_DT,
(CASE
WHEN CO_STATUS = '01' THEN 'PRE-DISBURSED'
WHEN CO_STATUS = '02' THEN 'DISBURSED'
WHEN CO_STATUS IN ('42''43','45') THEN 'PEND'
WHEN CO_STATUS IN ('52','54','58') THEN 'ADJUSTED'

WHEN CO_STATUS IN ('26','28','29') THEN 'VOID'

WHEN CO_STATUS = '48' THEN 'ENCOUNTER'
ELSE '(*)'
END) AS INV_STATUS,

(CASE WHEN CO_STATUS IN ('26','28','29') THEN 'VOID'

WHEN CO_STATUS IN ('42','43','45') THEN 'IN PROCESS'
WHEN CDML_CONSIDER_CHG <> 0
AND CDML_DISALL_AMT = CDML_CONSIDER_CHG
AND CDML_PAID_AMT = 0 THEN 'DENIED'
ELSE 'PAID'
END) AS CLM_ADJ_RESULT,
CDML_DISALL_EXCD AS EXPLAIN_CD,
EXCD_SHORT_TEXT AS EXPLAIN_DESC,
(CASE WHEN CLHP.TAG_ID IS NULL THEN 'HCFA'
ELSE 'UB92' END) AS CLM_TYPE,
CLHP_FAC_TYPE || CLHP_BILL_CLASS || CLHP_FREQUENCY AS BILL_TYPE,
SUBSTR(CDML.IPCD_ID,3,8) AS PROC_CD,
SUBSTR(CDML.IPCD_ID,7,2) AS PROC_MOD1,
IDCD_ID AS DIAG_CD,
CDML.RCRC_ID AS REV_CD,
CLHP_INPUT_AGRG_ID AS SUBMIT_DRG_CD,
AGRG_ID AS FINAL_DRG_CD,
CLHP_COVD_DAYS AS DAYS_PAID,
CDML_UNITS AS UNITS_SUBMITTED,
CDML_UNITS_ALLOW AS UNITS_PAID
FROM CMC_TAG_CLAIM TAG
INNER JOIN CMT_PROT_PROV PRPR
ON TAG.PRPR_ID = PRPR.PRPR_ID
AND TAG.TRC_CK = 4
AND TAG.CSCS_ID = 'LB03'
AND TAG.TAG_PAID_DT BETWEEN '03/01/09' AND '04/12/09'
AND PRPR.PRPR_ID in ('142BRE356','142CE486','142DD345')
INNER JOIN CMC_CDML_CL_LINE CDML
ON TAG.TAG_ID = CDML.TAG_ID
INNER JOIN CMC_SBSB_SUBSC SBSB
ON TAG.SBSB_CK = SBSB.SBSB_CK
INNER JOIN CMC_EXCD_EXPL_CD EXCD
ON CDML.CDML_DISALL_EXCD = EXCD.EXCD_ID
LEFT JOIN CMC_CLHP_HOSP CLHP
ON TAG.TAG_ID = CLHP.TAG_ID;

 

[dhookom]

Begin paring the SQL down one column at a time until it works.

Duane
Hook'D on Access
MS Access MVP

 

[pjm]

Take out the semicolon [red];[/red] at the end of the statement...

pjm

 

[IngDep]

The error remains.

Reviewed the sql script once more and did take out the semicolon.

Also included a comma after each number in a series if a comma was missing such as below;

WHEN CO_STATUS IN ('42''43','45') THEN 'PEND'
WHEN CO_STATUS IN ('52','54','58') THEN 'ADJUSTED'

What am I missing?

 

[pjm]

What's the error now?

Suggest connecting to Oracle via SQL*Plus and running your query there so you can see which line is causing the problem?

What datatype is TAG_PD_DT? (Also is 'TAG_' part of the column name as well as the alias name you have used for CMC_TAG_CLAIM?)

If you are comparing dates then better to use:

to_date('04/12/09', 'DD/MM/RR')

so your server's NLS settings don't matter.

More questions than answers :-(

pjm

 

[IngDep]

Well,

The sql runs fine in Sql Developer version 1.5.3 but doesn't run within MS Access 2002 as a passthrough query.

"TAG" is the alias for the table named "CMC_TAG_CLAIM ."

Maybe, I have some hidden characters considering that I copied the sql script directly from the SQL Developer pane into MS Access query pane.

The review continues...

Appreciate any additional insight.

 

[SkipVought]

Hi,

Replace CASE syntax with IIF syntax.

use # date delimiters
[tt]
TAG.TAG_PAID_DT BETWEEN #03/01/09# AND #04/12/09#
[/tt]

Replace SUBSTR with MID






Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pjm]

Skip: IngDep wants to use as a passthrough query, so he needs Oracle syntax...

IngDep: What error message is Access giving you now - still an ORA-nnnnn error? If still "Invalid Character" then check that you don't have any non-ascii stuff e.g. non-breaking spaces, hard carriage returns, "smart quotes" etc.

Please try running by exact copy & paste into SQL*Plus to get details of location of invalid character in your script.

pjm