September 18, 2013 at 11:46 am
I am attempting to run this script:
SELECT a.GRGR_CK, a.GRGR_ID, a.GRGR_NAME, s.SBSB_ID, s.SBSB_LAST_NAME, s.SBSB_FIRST_NAME, e.CLCL_ID, e.CLCL_CUR_STS,
e.CLST_MCTR_REAS, e.CLCL_CL_TYPE, e.CLCL_RECD_DT, GETDATE() AS today, e.Age,
CASE
WHEN e.Age <= 15 THEN 15
WHEN (e.Age > 15 AND e.Age <= 30) THEN 30
WHEN (e.Age > 30 AND e.Age <= 45) THEN 45
WHEN (e.Age > 45 AND e.Age <= 60) THEN 60
WHEN (e.Age > 60 AND e.Age <= 90) THEN 90
WHEN (e.Age > 90 AND e.Age <= 120) THEN 120
WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME,
CASE
WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END
FROM #temp1112b e INNER JOIN
[rpt_ALL].[dbo].[CER_SYMD_MSG_DEF] f INNER JOIN
[rpt_ALL].[dbo].[CER_SYML_MSG_LOG] g
ON g.SYMD_ID = f.SYMD_ID AND g.SYMD_MSG_CD = f.SYMD_MSG_CD INNER JOIN
[rpt_ALL].[dbo].[CMC_GRGR_GROUP] a INNER JOIN
[rpt_ALL].[dbo].[CMC_CLCL_CLAIM] i
ON a.GRGR_CK = i.GRGR_CK LEFT JOIN
[rpt_ALL].[dbo].[CMC_MCTR_CD_TRANS] j
ON i.CLST_MCTR_REAS = j.MCTR_ENTITY
I receive the following error:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'JOIN'.
Can someone assist me in resolving this error?
September 18, 2013 at 12:00 pm
One thing that I see is that you are using an alias that does not exist, as far as I can find "s"
SELECT
a.GRGR_CK
, a.GRGR_ID
, a.GRGR_NAME
, s.SBSB_ID
, s.SBSB_LAST_NAME
, s.SBSB_FIRST_NAME
, e.CLCL_ID
, e.CLCL_CUR_STS
, e.CLST_MCTR_REAS
, e.CLCL_CL_TYPE
, e.CLCL_RECD_DT
, GETDATE() AS today
, e.Age
,CASE
WHEN e.Age <= 15 THEN 15
WHEN (e.Age > 15 AND e.Age <= 30) THEN 30
WHEN (e.Age > 30 AND e.Age <= 45) THEN 45
WHEN (e.Age > 45 AND e.Age <= 60) THEN 60
WHEN (e.Age > 60 AND e.Age <= 90) THEN 90
WHEN (e.Age > 90 AND e.Age <= 120) THEN 120
WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME,
CASE
WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END
FROM #temp1112b e
INNER JOIN
rpt_ALL.dbo.CER_SYMD_MSG_DEF f
INNER JOIN
rpt_ALL.dbo.CER_SYML_MSG_LOG g
ON
g.SYMD_ID =
f.SYMD_ID
AND
g.SYMD_MSG_CD
= f.SYMD_MSG_CD
INNER JOIN
rpt_ALL.dbo.CMC_GRGR_GROUP a
INNER JOIN
rpt_ALL.dbo.CMC_CLCL_CLAIM i
ON
a.GRGR_CK
= i.GRGR_CK
LEFT JOIN
rpt_ALL.dbo.CMC_MCTR_CD_TRANS j
ON
i.CLST_MCTR_REAS
= j.MCTR_ENTITY
September 18, 2013 at 12:02 pm
Sorry about the color formatting. I am trying it again.
Andrew
SELECT
a.GRGR_CK
, a.GRGR_ID
, a.GRGR_NAME
, s.SBSB_ID
, s.SBSB_LAST_NAME
, s.SBSB_FIRST_NAME
, e.CLCL_ID
, e.CLCL_CUR_STS
, e.CLST_MCTR_REAS
, e.CLCL_CL_TYPE
, e.CLCL_RECD_DT
, GETDATE() AS today
, e.Age
,CASE
WHEN e.Age <= 15 THEN 15
WHEN (e.Age > 15 AND e.Age <= 30) THEN 30
WHEN (e.Age > 30 AND e.Age <= 45) THEN 45
WHEN (e.Age > 45 AND e.Age <= 60) THEN 60
WHEN (e.Age > 60 AND e.Age <= 90) THEN 90
WHEN (e.Age > 90 AND e.Age <= 120) THEN 120
WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME,
CASE
WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END
FROM #temp1112b e
INNER JOIN
rpt_ALL.dbo.CER_SYMD_MSG_DEF f
INNER JOIN
rpt_ALL.dbo.CER_SYML_MSG_LOG g
ON
g.SYMD_ID =
f.SYMD_ID
AND
g.SYMD_MSG_CD
= f.SYMD_MSG_CD
INNER JOIN
rpt_ALL.dbo.CMC_GRGR_GROUP a
INNER JOIN
rpt_ALL.dbo.CMC_CLCL_CLAIM i
ON
a.GRGR_CK
= i.GRGR_CK
LEFT JOIN
rpt_ALL.dbo.CMC_MCTR_CD_TRANS j
ON
i.CLST_MCTR_REAS
= j.MCTR_ENTITY
September 18, 2013 at 12:02 pm
Hi
I also don't see alias "p".
Br.
Mike
September 18, 2013 at 12:04 pm
Get in the habit of formatting your code as it can help spot the problems. you are missing 2 on cluases.
SELECT
a.GRGR_CK,
a.GRGR_ID,
a.GRGR_NAME,
s.SBSB_ID,
s.SBSB_LAST_NAME,
s.SBSB_FIRST_NAME,
e.CLCL_ID,
e.CLCL_CUR_STS,
e.CLST_MCTR_REAS,
e.CLCL_CL_TYPE,
e.CLCL_RECD_DT,
GETDATE() AS today,
e.Age,
CASE
WHEN e.Age <= 15 THEN 15
WHEN (e.Age > 15 AND e.Age <= 30) THEN 30
WHEN (e.Age > 30 AND e.Age <= 45) THEN 45
WHEN (e.Age > 45 AND e.Age <= 60) THEN 60
WHEN (e.Age > 60 AND e.Age <= 90) THEN 90
WHEN (e.Age > 90 AND e.Age <= 120) THEN 120
WHEN (e.Age > 120 AND e.Age <= 180) THEN 180
ELSE 181
END AS AgeRange,
p.PRPR_ID,
p.PRPR_NAME,
CASE
WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD
ELSE i.CLST_MCTR_REAS
END
FROM #temp1112b e
INNER JOIN [rpt_ALL].[dbo].[CER_SYMD_MSG_DEF] f
INNER JOIN [rpt_ALL].[dbo].[CER_SYML_MSG_LOG] g
ON g.SYMD_ID = f.SYMD_ID AND g.SYMD_MSG_CD = f.SYMD_MSG_CD
INNER JOIN [rpt_ALL].[dbo].[CMC_GRGR_GROUP] a
INNER JOIN [rpt_ALL].[dbo].[CMC_CLCL_CLAIM] i
ON a.GRGR_CK = i.GRGR_CK
LEFT JOIN [rpt_ALL].[dbo].[CMC_MCTR_CD_TRANS] j
ON i.CLST_MCTR_REAS = j.MCTR_ENTITY
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 12:05 pm
Any clue as why SSMS Intellisence did not catch that and underline them?
If there is an error up there, it will throw out an odd message like the one that you received. Make you chase down the wrong rabbit hole.
Andrew
September 18, 2013 at 12:20 pm
My apologies, I only included the part of the script I was experiencing the error with. Here's the entire script:
SELECT c.GRGR_CK, c.CLCL_ID, c.CLCL_RECD_DT, c.SBSB_CK, c.CLCL_CUR_STS, c.CLST_MCTR_REAS, c.CLCL_CL_TYPE, c.PRPR_ID, datediff(dd,
c.CLCL_RECD_DT, GETDATE()) AS Age
INTO #temp1112d
FROM [rpt_ALL].[dbo].[CMC_GRGR_GROUP] b INNER JOIN
[rpt_ALL].[dbo].[CMC_CLCL_CLAIM] c ON c.GRGR_CK = b.GRGR_CK
WHERE b.GRGR_MCTR_TYPE = 'BBS' AND (c.CLCL_CUR_STS = 11 OR
c.CLCL_CUR_STS = 15)
select GRGR_CK, CLCL_ID, CLCL_RECD_DT, SBSB_CK, CLCL_CUR_STS, CLST_MCTR_REAS, CLCL_CL_TYPE, PRPR_ID, Age
into #temp1118
from (
SELECT c.GRGR_CK, c.CLCL_ID, c.CLCL_RECD_DT, c.SBSB_CK, c.CLCL_CUR_STS, c.CLST_MCTR_REAS, c.CLCL_CL_TYPE, c.PRPR_ID, datediff(dd,
c.CLCL_RECD_DT,GETDATE()) AS Age
, ROW_NUMBER() OVER(PARTITION BY d.CLCL_ID order by d.CLST_SEQ_NO desc) AS rn,d.CLST_USID_ROUTE
FROM [rpt_ALL].[dbo].[CMC_GRGR_GROUP] a INNER JOIN
[rpt_ALL].[dbo].[CMC_CLCL_CLAIM] c ON c.GRGR_CK = a.GRGR_CK left JOIN
[rpt_ALL].[dbo].[CMC_CLST_STATUS] d ON d .CLCL_ID = c.CLCL_ID
WHERE a.GRGR_MCTR_TYPE = 'BBS' AND c.CLCL_CUR_STS = 01 AND d .CLST_MCTR_REAS = ''
)q
where rn=1 and (CLST_USID_ROUTE = '20' OR CLST_USID_ROUTE = '70')
select *
into #temp1112b
from #temp1112d
union all
select * from #temp1118
SELECT a.GRGR_CK, a.GRGR_ID, a.GRGR_NAME, s.SBSB_ID, s.SBSB_LAST_NAME, s.SBSB_FIRST_NAME, e.CLCL_ID, e.CLCL_CUR_STS,
e.CLST_MCTR_REAS, e.CLCL_CL_TYPE, e.CLCL_RECD_DT, GETDATE() AS today, e.Age,
CASE
WHEN e.Age <= 15 THEN 15
WHEN (e.Age > 15 AND e.Age <= 30) THEN 30
WHEN (e.Age > 30 AND e.Age <= 45) THEN 45
WHEN (e.Age > 45 AND e.Age <= 60) THEN 60
WHEN (e.Age > 60 AND e.Age <= 90) THEN 90
WHEN (e.Age > 90 AND e.Age <= 120) THEN 120
WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME,
CASE
WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END
FROM #temp1112b e INNER JOIN
[rpt_ALL].[dbo].[CER_SYMD_MSG_DEF] f INNER JOIN
[rpt_ALL].[dbo].[CER_SYML_MSG_LOG] g
ON g.SYMD_ID = f.SYMD_ID AND g.SYMD_MSG_CD = f.SYMD_MSG_CD INNER JOIN
[rpt_ALL].[dbo].[CMC_GRGR_GROUP] a INNER JOIN
[rpt_ALL].[dbo].[CMC_CLCL_CLAIM] i
ON i.GRGR_CK = a.GRGR_CK LEFT JOIN
September 18, 2013 at 12:22 pm
Ok, I cleaned it up and am still receiving "An expression of non boolean type specified in a context where a condidition is expected" error at the very end of the script on the dbo.CMC_MCTR_CD_TRANS.MCTR_VALUE. I do not understand why it is looking for a condition. Please say you know????:crazy:
SELECT
dbo.CMC_CLCL_CLAIM.GRGR_CK,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
dbo.CMC_CLCL_CLAIM.SBSB_CK,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.PRPR_ID, datediff(dd,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE()) AS Age
INTO #temp1112d
FROM dbo.CMC_GRGR_GROUP
INNER JOIN [rpt_ALL].[dbo].[CMC_CLCL_CLAIM] ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
WHERE dbo.CMC_GRGR_GROUP.GRGR_MCTR_TYPE = 'BBS' AND (dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 11 OR dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 15)
SELECT
GRGR_CK,
CLCL_ID,
CLCL_RECD_DT,
SBSB_CK,
CLCL_CUR_STS,
CLST_MCTR_REAS,
CLCL_CL_TYPE,
PRPR_ID,
Age
INTO #temp1118
FROM
(SELECT
dbo.CMC_CLCL_CLAIM.GRGR_CK,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
dbo.CMC_CLCL_CLAIM.SBSB_CK,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.PRPR_ID, datediff(dd,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE()) AS Age
, ROW_NUMBER() OVER(PARTITION BY dbo.CMC_CLST_STATUS.CLCL_ID order by dbo.CMC_CLST_STATUS.CLST_SEQ_NO desc) AS rn,dbo.CMC_CLST_STATUS.CLST_USID_ROUTE
FROM dbo.CMC_GRGR_GROUP
INNER JOIN dbo.CMC_CLCL_CLAIM
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
LEFT JOIN dbo.CMC_CLST_STATUS
ON dbo.CMC_CLST_STATUS.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
WHERE dbo.CMC_GRGR_GROUP.GRGR_MCTR_TYPE = 'BBS' AND dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 01 AND dbo.CMC_CLST_STATUS.CLST_MCTR_REAS = '')q
WHERE rn=1 and (CLST_USID_ROUTE = '20' OR CLST_USID_ROUTE = '70')
SELECT *
INTO #temp1112b
FROM #temp1112d
UNION all
SELECT *
FROM #temp1118
SELECT
dbo.CMC_GRGR_GROUP.GRGR_CK,
dbo.CMC_GRGR_GROUP.GRGR_ID,
dbo.CMC_GRGR_GROUP.GRGR_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_ID,
dbo.CMC_SBSB_SUBSC.SBSB_LAST_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_FIRST_NAME,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE() AS today,
#temp1112b.Age,
CASE
WHEN #temp1112b.Age <= 15 THEN 15
WHEN (#temp1112b.Age > 15 AND #temp1112b.Age <= 30) THEN 30
WHEN (#temp1112b.Age > 30 AND #temp1112b.Age <= 45) THEN 45
WHEN (#temp1112b.Age > 45 AND #temp1112b.Age <= 60) THEN 60
WHEN (#temp1112b.Age > 60 AND #temp1112b.Age <= 90) THEN 90
WHEN (#temp1112b.Age > 90 AND #temp1112b.Age <= 120) THEN 120
WHEN (#temp1112b.Age > 120 AND #temp1112b.Age <= 180) THEN 180
ELSE 181
END AS AgeRange,
dbo.CMC_PRPR_PROV.PRPR_ID,
dbo.CMC_PRPR_PROV.PRPR_NAME,
CASE
WHEN (dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS = '') THEN dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD
ELSE dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS
END
FROM #temp1112b
INNER JOIN dbo.CER_SYMD_MSG_DEF
INNER JOIN dbo.CER_SYML_MSG_LOG
ON dbo.CER_SYMD_MSG_DEF.SYMD_ID = dbo.CER_SYML_MSG_LOG.SYMD_ID AND dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD = dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD
INNER JOIN dbo.CMC_CLCL_CLAIM
ON dbo.CER_SYML_MSG_LOG.SYML_CONTEXT_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CMC_GRGR_GROUP
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
INNER JOIN dbo.CMC_PRPR_PROV
ON dbo.CMC_CLCL_CLAIM.PRPR_ID = dbo.CMC_PRPR_PROV.PRPR_ID
INNER JOIN dbo.CMC_SBSB_SUBSC
ON dbo.CMC_CLCL_CLAIM.SBSB_CK = dbo.CMC_SBSB_SUBSC.SBSB_CK AND dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_SBSB_SUBSC.GRGR_CK
LEFT OUTER JOIN dbo.CMC_MCTR_CD_TRANS
ON dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS = dbo.CMC_MCTR_CD_TRANS.MCTR_VALUE
September 18, 2013 at 12:38 pm
still missing the ON part of a few joins
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply