April 16, 2009 at 3:52 pm
Can someone please tell me why this code works:
SELECT FINANCE_SVC_TYPE = LEFT(DFSC.FINANCE_SVC_TYPE_CD, 2),
CD.CLAIM_ID,
GRP = (CASE
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPERMH', 'OPERSA') THEN 'OPERMH-OPERSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA') THEN 'OPOTMH-OPOTSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('IPMH', 'IPSA') THEN 'IPMH-IPSA'
ELSE ''
END),
FINANCE_SVC_CATEGORY_ID = MIN(DFSC.FINANCE_SVC_CATEGORY_ID)
FROM dbo.DIM_FINANCE_SVC_CATEGORY DFSC (NOLOCK)
INNER JOIN dbo.FINANCE_SVC_CATEGORY_DIAGNOSIS FSCD (NOLOCK) ON
DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA', 'OPERMH', 'OPERSA', 'IPMH', 'IPSA')
AND FSCD.FINANCE_SVC_CATEGORY_ID = DFSC.FINANCE_SVC_CATEGORY_ID
INNER JOIN dbo.CLAIM_DIAGNOSIS CD (NOLOCK) ON
CD.CLM_DIAG_SEQ_NBR = 1
AND CD.CLM_DIAG_TYPE_CD = 'D'
AND CD.CLM_DIAG_CD = FSCD.DIAG_CD
GROUP BY CD.CLAIM_ID,
LEFT(DFSC.FINANCE_SVC_TYPE_CD, 2),
(CASE
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPERMH', 'OPERSA') THEN 'OPERMH-OPERSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA') THEN 'OPOTMH-OPOTSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('IPMH', 'IPSA') THEN 'IPMH-IPSA'
ELSE ''
END)
ORDER BY FINANCE_SVC_TYPE, CD.CLAIM_ID
And this code doesn't:
SELECT FINANCE_SVC_TYPE = LEFT(DFSC.FINANCE_SVC_TYPE_CD, 2),
CD.CLAIM_ID,
GRP = (CASE
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPERMH', 'OPERSA') AND EXISTS (SELECT MHSA_SVC_CD
FROM MF_MHSA_CATEGORY WHERE MHSA_FLAG = 'Y' AND MHSA_CAT_TYPE = 'P' AND MHSA_SVC_CD =
CLP.CLM_LINE_PROC_CD)
THEN 'OPERMH-OPERSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA') AND EXISTS (SELECT MHSA_SVC_CD
FROM MF_MHSA_CATEGORY WHERE MHSA_FLAG = 'Y' AND MHSA_CAT_TYPE = 'P' AND MHSA_SVC_CD =
CLP.CLM_LINE_PROC_CD)
THEN 'OPOTMH-OPOTSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('IPMH', 'IPSA') THEN 'IPMH-IPSA'
ELSE ''
END),
FINANCE_SVC_CATEGORY_ID = MIN(DFSC.FINANCE_SVC_CATEGORY_ID)
FROM dbo.DIM_FINANCE_SVC_CATEGORY DFSC (NOLOCK)
INNER JOIN dbo.FINANCE_SVC_CATEGORY_DIAGNOSIS FSCD (NOLOCK) ON
DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA', 'OPERMH', 'OPERSA', 'IPMH', 'IPSA')
AND FSCD.FINANCE_SVC_CATEGORY_ID = DFSC.FINANCE_SVC_CATEGORY_ID
INNER JOIN dbo.CLAIM_DIAGNOSIS CD (NOLOCK) ON
CD.CLM_DIAG_SEQ_NBR = 1
AND CD.CLM_DIAG_TYPE_CD = 'D'
AND CD.CLM_DIAG_CD = FSCD.DIAG_CD
INNER JOIN dbo.CLAIM_LINE_PROCEDURE CLP (NOLOCK) ON
CLP.CLAIM_ID = CD.CLAIM_ID
GROUP BY CD.CLAIM_ID,
CLP.CLM_LINE_PROC_CD,
LEFT(DFSC.FINANCE_SVC_TYPE_CD, 2),
(CASE
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPERMH', 'OPERSA') AND EXISTS (SELECT MHSA_SVC_CD
FROM MF_MHSA_CATEGORY WHERE MHSA_FLAG = 'Y' AND MHSA_CAT_TYPE = 'P' AND MHSA_SVC_CD =
CLP.CLM_LINE_PROC_CD)
THEN 'OPERMH-OPERSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('OPOTMH', 'OPOTSA') AND EXISTS (SELECT MHSA_SVC_CD FROM
MF_MHSA_CATEGORY WHERE MHSA_FLAG = 'Y' AND MHSA_CAT_TYPE = 'P' AND MHSA_SVC_CD =
CLP.CLM_LINE_PROC_CD)
THEN 'OPOTMH-OPOTSA'
WHEN DFSC.FINANCE_SVC_TYPE_CD IN ('IPMH', 'IPSA') THEN 'IPMH-IPSA'
ELSE ''
END)
ORDER BY FINANCE_SVC_TYPE, CD.CLAIM_ID
The only difference is that I added an EXISTS () clause inside the CASE statement. After that, it throws an error message saying that the DFSC.FINANCE_SVC_TYPE_CD field is invalid in the SELECT because it isn't in the GROUP BY, but it is. If I remove the LEFT() from the FINANCE_SVC_TYPE_CD field in the GROUP BY, I no longer get the error message, but it doesn't group the way I need it to. And, it works fine with the LEFT() in the GROUP BY as long as the EXISTS() isn't in the CASE statement. Any suggestions would be greatly appreciated!
Thanks,
Hari
April 16, 2009 at 4:11 pm
While we're all scratching our heads, may I suggest that you simplify this a little by using a CTE up front to develop values? Use your existence test in the CTE to produce a result column called existenceTest. Maybe even do all of your case testing in the CTE. You might get unstuck before anyone figures it all out.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 4:15 pm
And the [ code ] tags would make life easier for all of us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 4:51 pm
Barry!! You're AWAKE !!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 5:02 pm
Heh, yeah, for now. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 5:23 pm
I'm part of the 1% of SQL humanity that hasn't read your article yet. Is it on your blog?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 5:55 pm
Thanks for the tip. CTEs are new with SQL Server 2005, aren't they? Most of my work has been with 2000, so I've never used them. I'll look them up and give it a try. And, sorry about the lack of formatting in the code. I don't use forums much - I should have read up on the protocols.
Thanks,
Hari
April 16, 2009 at 6:02 pm
Yes, new in 2005, CTEs are (for me) much easier to follow alternative to subqueries. They are like views that only exist for a single query. I find them really helpful in breaking down complicated logic to step-by-step, although the optimizer resolves them to a single execution plan.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 8:40 pm
Bob Hovious (4/16/2009)
I'm part of the 1% of SQL humanity that hasn't read your article yet. Is it on your blog?
No it's at SQLServerCentral.com, here[/url].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply