Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
powersj
Starting Member
1 Post |
 Posted - 2014-01-17 : 16:47:13
|
| Hi all,I inherited a query and I am getting an error of Unsupported literal in join in the INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2 ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX = '00')statement. Specifically the AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX = '00')part. Does anyone have an idea how to fix this?SELECT frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID AS 'group number' , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SGSG_ID AS 'subgroup number' , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_CUR_STS AS 'claim status' , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_ID AS 'claim number' , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_SEQ_NO AS 'claim line num' , dbo_CMC_IPCD_PROC_CD2.PROC_CODE_NO_MOD as 'proc code' , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.IPCD_ID_DESC as 'proc code desc' , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_FROM_DT , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.MEMBER_ID , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_ID , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.PRPR_ID , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_PRPR_ID_PERF_NAME , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_PAYEE_PR_ID , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_PAYEE_PR_NAME , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.IDCD_ID_1 , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.IDCD_ID_1_DESC , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.AGRG_ID , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLHP_INPUT_AGRG_ID , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_CHG_AMT , frdm.dbo.CLCL_DENY_TABLE_VIEW.ALLOWED_AMT , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_COINS_AMT , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_COPAY_AMT , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_DED_AMT , frdm.dbo.CLCL_DENY_TABLE_VIEW.CLAIM_EXPENSE_AMT , ISNULL(frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDCB_COB_AMT, 0) AS cob_amt , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_PAID_DT , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.AGAG_ID , frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_EOB_EXCD , frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_EOB_EXCD_ID , (CASE WHEN FRDM_dbo_MEMBER_SUBSC2.GRGR_CK = 13948 THEN rtrim(FRDM_dbo_MEME_MASK_VIEW.MEME_LAST_NAME) ELSE rtrim(FRDM_dbo_MEMBER_SUBSC2.MEME_LAST_NAME) END) AS sub_last_name , --MVP Service Corp GRGR_CK (CASE WHEN FRDM_dbo_MEMBER_SUBSC2.GRGR_CK = 13948 THEN rtrim(FRDM_dbo_MEME_MASK_VIEW.MEME_FIRST_NAME) ELSE rtrim(FRDM_dbo_MEMBER_SUBSC2.MEME_FIRST_NAME) END) AS sub_first_name , --MVP Service Corp GRGR_CK rtrim(FRDM_dbo_MEME_MASK_VIEW.MEME_LAST_NAME) AS meme_last_name, rtrim(FRDM_dbo_MEME_MASK_VIEW.MEME_FIRST_NAME) AS meme_first_nameFROM frdm.dbo.CMC_IPCD_PROC_CD dbo_CMC_IPCD_PROC_CD2 RIGHT OUTER JOIN frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW ON (dbo_CMC_IPCD_PROC_CD2.IPCD_ID = frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.IPCD_ID) INNER JOIN frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_ID = frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CLCL_ID) INNER JOIN FRDM.dbo.MEMBER_SUBSC ON (FRDM.dbo.MEMBER_SUBSC.MEME_CK = frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.MEME_CK) INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2 ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX = '00') INNER JOIN FRDM.dbo.MEME_MASK_VIEW FRDM_dbo_MEME_MASK_VIEW ON (FRDM_dbo_MEME_MASK_VIEW.MEME_CK = FRDM.dbo.MEMBER_SUBSC.MEME_CK) INNER JOIN frdm.dbo.CLCL_DENY_TABLE_VIEW ON (frdm.dbo.CLCL_DENY_TABLE_VIEW.clcl_id = frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CLCL_ID AND frdm.dbo. CLCL_DENY_TABLE_VIEW.cdml_seq_no = frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_SEQ_NO)WHERE frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_CL_TYPE = 'M' AND frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_CUR_STS IN ('02', '91') AND frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.MVP_NEW_LOB = 'ASO' AND frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID = '409784' AND frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_PAID_DT BETWEEN '10/01/2013 00:0:0' AND '10/31/2013 00:0:0' AND frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.CLCL_ID = 'E05451484700V'ORDER BY frdm.dbo.CLAIM_LINE_WITH_VOIDS_VIEW.CDML_SEQ_NO |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-01-17 : 18:42:27
|
| You have joined the same table (FRDM.dbo.MEMBER_SUBSC) twice but only given an alias for one of them. It can't decide which one you want from there. Try aliasing the first join also (FRDM.dbo.MEMBER_SUBSC1).=================================================A man is not old until regrets take the place of dreams. - John Barrymore |
 |
|
|
|
|
|
|
|
syntax to join with an and statement