September 19, 2006 at 10:27 am
I am trying to use OPENQUERY for the first time. The script below is getting the following messages:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODI' is too long. Maximum length is 128.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODI'.
Any help is very much appreciated.
SCRIPT:
SELECT * FROM OPENQUERY([HHFNT-DW],"SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODIFIER_CODE AS MOD__MODIFIER_CODE,
= C.PX__PROCEDURE_CODE + '-' + ISNULL(D5.MODIFIER_CODE,' ') + '-' + CONVERT(CHAR(4),YEAR(C.SER_FROM_DT)),
ADJ_UNITS = ISNULL(MBCL.UNITS,0) + ISNULL(MBCL.DAYS,0),
MBCL.UNITS, MBCL.DAYS,C.BILLED_AMT,C.APP_AMT,C.COPAY_AMT,C.SER_FROM_DT, C.LAST_STATUS_DT,C.DX_ONE_DE,C.DX_ONE__ICD9_CODE,
C.DX_TWO_DE,C.DX_TWO__ICD9_CODE,C.DX_THREE_DE,C.DX_THREE__ICD9_CODE,C.VENDOR_DE,C.VENDOR__VENDOR_NAME,C.MASTER_VENDOR_DE,
C.MASTER_VENDOR__VENDOR_NAME,C.LNM + ',' + C.FNM AS MBR__NAME,C.NUM AS MBR__NUM,MF.LOC,C.DOB,
DATEDIFF(YEAR,C.DOB,C.SER_FROM_DT) AS AGE,MF.SEX,C.PLAN_TYPE_DE,C.PLAN_TYPE__NAME,C.PLAN_TYPE_B_DE,C.PLAN_TYPE_B__NAME,
C.BILL_AREA_DE,C.BILL_AREA__BILLING_AREA,C.LOC_DE,C.LOC__NAME,C.STATIS,C.STATUS__NAME,
LINE_TYPE = CASE WHEN C.STATIS = 'Y' THEN 'CAP'
WHEN C.STATIS = 'N' THEN 'FFS'
ELSE 'OTHER' END,
C.CHECK_NUM,MBC.U_BANK_CHK,MBC.U_CHECK_DT,C.REF,MBC.DT_REC,MBC.U_SJHH_DT_REC,MBC.U_EDI_AREA,MBC.VENDOR_ACC_NUM,
PERIOD = CASE WHEN C.SER_FROM_DT BETWEEN '07/01/2004' AND '06/30/2005' THEN 'PRIOR'
WHEN C.SER_FROM_DT BETWEEN '07/01/2005' AND '06/30/2006' THEN 'CURRENT'
END,
DOS_YR_MO = CONVERT(CHAR(4),YEAR(C.SER_FROM_DT)) + '/' +
CASE WHEN LEN(CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))) = 2 THEN CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))
ELSE '0'+ CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))
END,
E_M_CODE = CASE WHEN EM.DE IS NOT NULL THEN 'YES' ELSE 'NO ' END,
0 AS YR_MO_ORDER#
INTO REPORTS..TBL_MEDICARE_1
FROM IADIS..ISM_F_CLAIM AS C
LEFT JOIN IADIS..ISM_F_MEMBER_FULL AS MF
ON C.HMO = MF.HMO AND C.ID = MF.ID AND C.CIN = MF.CIN AND C.MEMFULL_START_DT = MF.START_DT
LEFT JOIN IADIS..ISMS_MCA_B_CLAIM_LINE AS MBCL
ON C.HMO = MBCL.HMO AND C.ID = MBCL.ID AND C.LINE = MBCL.LINE AND C.CLAIM = MBCL.CLAIM
LEFT JOIN IADIS..ISM_IDX_V_DICT5 AS D5
ON MBCL.MOD = D5.DE
LEFT JOIN IADIS..ISMS_MCA_B_CLAIM AS MBC
ON C.CLAIM = MBC.CLAIM AND C.HMO = MBC.HMO AND C.ID = MBC.ID
LEFT JOIN REPORTS.JOHNSONM2.TBL_FIN_EM_PROCEDURES AS EM
ON C.PX_DE = EM.DE
WHERE C.SER_FROM_DT BETWEEN '07/01/2004' AND '06/30/2006' AND MF.LOC IN (176)
AND (C.VENDOR_DE IN (83,138,405,1342,1393,3191,3973,5402,5403,21020) OR C.MASTER_VENDOR_DE IN (5402,1342,405))
AND (STATUS__NAME NOT LIKE '%DEN%' AND STATUS__NAME NOT LIKE '%DUP%')")
September 19, 2006 at 11:01 am
try this
SELECT
* FROM OPENQUERY([HHFNT-DW],'SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODIFIER_CODE AS MOD__MODIFIER_CODE,
= C.PX__PROCEDURE_CODE + ''-'' + ISNULL(D5.MODIFIER_CODE,'' '') + ''-'' + CONVERT(CHAR(4),YEAR(C.SER_FROM_DT)),
ADJ_UNITS = ISNULL(MBCL.UNITS,0) + ISNULL(MBCL.DAYS,0),
MBCL.UNITS, MBCL.DAYS,C.BILLED_AMT,C.APP_AMT,C.COPAY_AMT,C.SER_FROM_DT, C.LAST_STATUS_DT,C.DX_ONE_DE,C.DX_ONE__ICD9_CODE,
C.DX_TWO_DE,C.DX_TWO__ICD9_CODE,C.DX_THREE_DE,C.DX_THREE__ICD9_CODE,C.VENDOR_DE,C.VENDOR__VENDOR_NAME,C.MASTER_VENDOR_DE,
C.MASTER_VENDOR__VENDOR_NAME,C.LNM + '','' + C.FNM AS MBR__NAME,C.NUM AS MBR__NUM,MF.LOC,C.DOB,
DATEDIFF(YEAR,C.DOB,C.SER_FROM_DT) AS AGE,MF.SEX,C.PLAN_TYPE_DE,C.PLAN_TYPE__NAME,C.PLAN_TYPE_B_DE,C.PLAN_TYPE_B__NAME,
C.BILL_AREA_DE,C.BILL_AREA__BILLING_AREA,C.LOC_DE,C.LOC__NAME,C.STATIS,C.STATUS__NAME,
LINE_TYPE = CASE WHEN C.STATIS = ''Y'' THEN ''CAP''
WHEN C.STATIS = ''N'' THEN ''FFS''
ELSE ''OTHER'' END,
C.CHECK_NUM,MBC.U_BANK_CHK,MBC.U_CHECK_DT,C.REF,MBC.DT_REC,MBC.U_SJHH_DT_REC,MBC.U_EDI_AREA,MBC.VENDOR_ACC_NUM,
PERIOD = CASE WHEN C.SER_FROM_DT BETWEEN ''07/01/2004'' AND ''06/30/2005'' THEN ''PRIOR''
WHEN C.SER_FROM_DT BETWEEN ''07/01/2005'' AND ''06/30/2006'' THEN ''CURRENT''
END,
DOS_YR_MO = CONVERT(CHAR(4),YEAR(C.SER_FROM_DT)) + ''/'' +
CASE WHEN LEN(CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))) = 2 THEN CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))
ELSE ''0''+ CONVERT(CHAR(2),MONTH(C.SER_FROM_DT))
END,
E_M_CODE = CASE WHEN EM.DE IS NOT NULL THEN ''YES'' ELSE ''NO '' END,
0 AS YR_MO_ORDER#
INTO REPORTS..TBL_MEDICARE_1
FROM IADIS..ISM_F_CLAIM AS C
LEFT JOIN IADIS..ISM_F_MEMBER_FULL AS MF
ON C.HMO = MF.HMO AND C.ID = MF.ID AND C.CIN = MF.CIN AND C.MEMFULL_START_DT = MF.START_DT
LEFT JOIN IADIS..ISMS_MCA_B_CLAIM_LINE AS MBCL
ON C.HMO = MBCL.HMO AND C.ID = MBCL.ID AND C.LINE = MBCL.LINE AND C.CLAIM = MBCL.CLAIM
LEFT JOIN IADIS..ISM_IDX_V_DICT5 AS D5
ON MBCL.MOD = D5.DE
LEFT JOIN IADIS..ISMS_MCA_B_CLAIM AS MBC
ON C.CLAIM = MBC.CLAIM AND C.HMO = MBC.HMO AND C.ID = MBC.ID
LEFT JOIN REPORTS.JOHNSONM2.TBL_FIN_EM_PROCEDURES AS EM
ON C.PX_DE = EM.DE
WHERE C.SER_FROM_DT BETWEEN ''07/01/2004'' AND ''06/30/2006'' AND MF.LOC IN (176)
AND (C.VENDOR_DE IN (83,138,405,1342,1393,3191,3973,5402,5403,21020) OR C.MASTER_VENDOR_DE IN (5402,1342,405))
AND (STATUS__NAME NOT LIKE ''%DEN%'' AND STATUS__NAME NOT LIKE ''%DUP%'')'
)
September 19, 2006 at 2:40 pm
Thanks, but that gives these error messages:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODIFIER_CODE AS MOD__MODIFIER_CODE,
= C.PX__PROCEDURE_CODE + '-' + ISNULL(D5.MODIFIER_CODE,' ') + '-' + CONVERT(CHAR(4),YEAR(C.SER_FROM_DT)),
ADJ_UNITS = ISNULL(MBCL.UNITS,0) + ISNULL(MBCL.DAYS,0),
MBCL.UNITS, MBCL.DAYS,C.BILLED...
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=SELECT 0 AS MANUAL,C.HMO,C.ID,C.CLAIM,C.LINE,C.ORIG_CLM_NUM,C.PX_DE,C.PX__PROCEDURE_CODE,C.PX__NAME,MBCL.MOD AS MOD_DE,
D5.MODIFIER_CODE AS MOD__MODIFIER_CODE,
= C.PX__PROCEDURE_CODE + '-' + ISNULL(D5.MODIFIER_CODE,' ') + '-' + CONVERT(CH...
September 19, 2006 at 2:52 pm
First of all, try to execute the Query in Remote Server, if it is executed succesfully then try with simple select query to remote server then
try again OPENQUERY with the executed query in remote server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply